Reading: Analyzing & Managing Data
In Excel, there are many ways to analyze and display data. For this topic, you will focus on Conditional Formatting, Filtering Data, and working with Functions. These are all important tools to know when working with data in Excel. They will help you to better understand your data and make it easier to read and analyze. In this reading, you will go over what each of these tools are and how to use them.
Conditional Formatting
Conditional formatting is a feature in Excel that allows you to format cells based on certain conditions. This can be useful when you want to highlight certain data points or make your data easier to read.
There are many different ways to use conditional formatting in Excel. Some common uses include: - Highlighting cells that contain certain values, text, dates, or numbers - Highlighting cells that are above or below a certain value - Highlighting cells that contain duplicate values - Highlighting cells that are blank or contain errors - Putting icons next to cells that meet certain criteria - Data bars to show the relative size of values in a range - Color scales to show the relative size of values in a range
To set up conditional formatting to your data in Excel, follow these steps:
- Select the range of cells you want to format. This can be a single cell, a row, a column, or a range of cells.
- Make sure you are on the Home tab, then select Conditional Formatting from the ribbon. This will open a drop-down menu with several options for conditional formatting.
- Select the type of conditional formatting you want to apply. There are several options to choose from, including Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, and Icon Sets. You can also create a new rule by selecting "New Rule" from the drop-down menu.
- Once you have selected the type of conditional formatting you want to apply, a dialog box will appear. This dialog box will allow you to set the conditions for the formatting and choose the formatting options. Say for example, you want to highlight any cells that contains a "5" in the data set. You would select "Text that contains" from the drop-down menu.
- This will open a dialog box where you can enter the value you want to format. In this case, you would enter "5" in the box. You can also choose the condition to be based on a formula. For example, if you want to highlight cells that are greater than 5, you would enter the formula ">=5" in the box. The condition could also be based off another cell. For example, if you want to highlight cells that are greater than the value in cell A1, you would enter the formula ">=A1" in the box.
- After you have entered the value or formula, you can choose the formatting options. This includes the font color, fill color, and border style. You can also choose to apply the formatting to the entire row or column. There are some preset options to choose from, or you can create your own custom formatting by selecting "Custom Format" from the drop-down menu.
- Inside the custom format dialog box, you can choose the font, border, and fill options. You can also choose to apply the formatting to the entire row or column. Once you have selected the formatting options, click "OK" to apply the formatting.
- Once you have selected what the condition is and how you want it to look, you can click OK. This will apply the conditional formatting to the selected cells.
You can also add multiple conditional formatting rules to the same range of cells. To do this, simply repeat the steps above for each rule you want to add. The rules will be applied in the order they are listed in the Conditional Formatting Rules Manager. Conditional formatting rules can be edited or deleted at any time by selecting the rule in the Conditional Formatting Rules Manager and clicking "Edit Rule" or "Delete Rule". It is important to note that the order of the rules can affect how they are applied. For example, if you have two rules that apply to the same cells, the first rule will take precedence over the second rule. This means that if the first rule is true, the second rule will not be applied. Conditional formatting rules can also be copied and pasted to other cells. To do this, simply select the cells with the conditional formatting you want to copy, right-click, and select "Copy". Then, right-click on the cells where you want to paste the formatting and select "Paste Special". In the Paste Special dialog box, select "Formats" and click "OK". This will apply the conditional formatting to the selected cells.
To see more examples of conditional formatting and the different options available, check out this link from W3Schools: Conditional Formatting
Filtering Data
Filtering data is a feature in Excel that allows you to show only the data that meets certain criteria. This can be useful when you have a large data set and only want to focus on a specific section of it. This differs from conditional formatting as filtering allows you to change the range of data you see, while conditional formatting changes the visual aspect of the data but does not alter the view range.
To set up filtering in Excel, follow these steps:
- Select the columns of data you want to add filters to. You can select the entire table or just the headers.
- Then go to the Data tab in the ribbon and select the Filter button. This will add a filter icon to each of the headers in the selected columns. Also on the Home tab, select "Sort & Filter" and then the Filter button. You can also use the shortcut Ctrl + Shift + L (Cmd + Shift + L for Mac users) to add filters to the selected columns.
- This will add filters to the top of the table on the header column.
- To filter the data, click on the filter icon in the header of the column you want to filter. This will open a drop-down menu with several options for filtering the data.
- There are many ways to filter data, including: - Filter by values/number: This allows you to filter the data based on specific values or numbers. For example, if you have a column of numbers and only want to see the rows with values greater than 50, you can filter the data to only show those rows. - Filter by color: This allows you to filter the data based on the color of the cells. For example, if you have cells that are highlighted in red, you can filter the data to only show those rows.
- To filter by values, select the "Number Filters" option from the drop-down menu. This will open a sub-menu with several options for filtering the data based on numbers. You can choose to filter by "Equals", "Does Not Equal", "Greater Than", "Less Than", "Between", and more. In this case we will select "Greater Than" and enter the value we want to filter by. This will then open the following dialog box:
- In this dialog box, you can add two conditions to filter by, such as "greater than" and/or "less than". You can also choose to filter by "Top 10" or "Above Average" to show only the top or bottom values in the data set. Once you have selected the conditions you want to filter by, click "OK" to apply the filter.
- After a filter is applied, the rows that do not meet the criteria will be hidden from view. You can tell that a filter is applied by looking at the filter icon in the header of the column. The icon will change to show that a filter is applied. (See the difference between the arrows in the top right of the image below) Also note that the row numbers will also reflect the hidden rows.
- To remove a filter, click on the filter icon in the header of the column and select "Clear Filter From [Column Name]". This will remove the filter and show all the data in the table again. You can also remove all filters by going to the Data tab in the ribbon and selecting "Clear" from the Sort & Filter group.
Filters can also be applied to multiple columns at the same time. To do this, simply repeat the steps above for each column you want to filter. The filters will be applied in the order they are listed in the Filter drop-down menu. You can also use the "Custom Filter" option to create more complex filters that combine multiple criteria. This allows you to filter the data based on multiple conditions, such as showing only rows that meet both criteria.
For more help on creating custom filters, check out: Filter by using advanced criteria
For more general help on filtering data in Excel, check out the following websites: Filter data in a range or table and Excel Filters by W3Schools
Working with Functions
In Excel, functions help users to analyze, manage, and compute data. A function is set up in three parts:
Throughout this unit, you will learn new and useful functions. For this topic, you will focus on the most common functions for analyzing data:
| Function | Syntax | Purpose | Help Link (W3Schools) |
|---|---|---|---|
| Sum | =SUM(arguments) | Adds all of the arguments together | SUM |
| Average | =AVERAGE(arguments) | Averages arguments together | AVERAGE |
| Max | =MAX(arguments) | Returns the highest number out of the arguments | MAX |
| Min | =MIN(arguments) | Returns the lowest number out of the arguments | MIN |
| Standard Deviation | =STDEV.P(arguments) | Returns the standard deviation for the entire population. Use this if the range you pass to the function is the entire dataset.** |
STDEV.P |
| Standard Deviation | =STDEV.S(arguments) | Returns the standard deviation for a sample of the entire population. Use this if the range you pass is a subset of the overall population. |
STDEV.S |
| Median | =Median(arguments) | Returns the median of the arguments | MEDIAN |
** This function replaced the older STDEV function, which is still available for compatibility with earlier versions of Excel.
You can find more functions at W3Schools. You can also find a list of all functions in Excel by going to the Formulas tab in the ribbon and selecting "Insert Function". This will open a dialog box with a list of all the functions available in Excel. You can also search for specific functions by typing in the search box at the top of the dialog box. Within this dialog box, you can also find the function arguments and a description of what the function does. This is a great way to learn about new functions and how to use them. This can also be pulled by clicking on the function name in the formula bar.
Freezing Rows/Columns
When working with large data sets in Excel, it can be helpful to freeze certain rows or columns so that they remain visible while you scroll through the rest of the data. This is especially useful for keeping headers or labels in view while you work with the data. To freeze rows or columns in Excel, follow these steps: 1. Select the row or column Below the row or column that you want to freeze. If you would like to freeze the top two rows, you are going to select the row Below the top two rows or the leftmost cell Below the top two rows. If you want to freeze both rows and columns, select the cell that is below the rows and to the right of the columns you want to freeze. 2. Go to the View tab in the ribbon and select "Freeze Panes" from the Window group. This will open a drop-down menu with several options for freezing rows and columns. 3. Select the option that best fits your needs: - Freeze Panes: This will freeze both the rows above and the columns to the left of the selected cell. - Freeze Top Row: This will freeze only the top row of the worksheet. - Freeze First Column: This will freeze only the first column of the worksheet. 4. Once you have selected the option you want, the rows or columns will be frozen in place. You can tell that they are frozen by looking for a thicker border between the frozen and unfrozen sections of the worksheet. If you scroll down or to the right, the frozen rows or columns will remain visible while the rest of the data scrolls. 5. To unfreeze the rows or columns, go to the View tab in the ribbon and select "Unfreeze Panes" from the Window group. This will unfreeze all frozen rows and columns in the worksheet.
Freezing rows and columns can be a useful tool when working with large data sets in Excel. It allows you to keep important information in view while you work with the rest of the data. This can be useful for keeping headers or labels visible, making it easier to understand and remember the data you are working with. This link provides more information on freezing rows and columns in Excel if you would like to learn more! Microsoft Freeze Panes
Pre-Class Quiz Challenge
- First download the starter sheet: (Starter-Workbook)-Pre-Analyzing-&-Managing-Data.xlsx and save it to the OneDrive folder you created for this class.
Before you start, make sure to make a copy of the file and rename it something like “(Your-Name)-Pre-Analyzing-&-Managing-Data”.
The challenge is a modified version of one from this website Filtering and Sorting Data. - Highlight those in the Type column that checked out Cameras.
Hint: Use conditional formatting - Highlight those in column A that have an ID number between 1000 and 2500.
- Select cells A1:F30 and add a Filter.
- Sort the spreadsheet by the Checked Out date from most recent to the oldest.
- Sort the spreadsheet by Days Checked Out to only show those who have a value of 5 and higher.
- Fill in the Days Checked out Statistics chart using the Days checked out info in the main table.
- When you're finished, your spreadsheet should look something like this:
Turning in/Rubric
REMINDER - For this class, you will only turn in the links to your Excel files. You will get a 0 for this assignment if you turn in an Excel file or a link that is not shareable.
- Make sure your file is saved to your OneDrive folder.
- On the top right, click the share button --> share --> settings
- Click "anyone" at the top, then underneath "More settings", change "can view" to "can edit". Then click apply.
- Copy the link, then turn it into Learning Suite in the feedback box for that assignment.
Rubric:
| Item | Points Possible |
|---|---|
Total |
3 |
The following is not a part of the rubric, but specifies how you can lose points. For example: if you fail to share your link correctly.
| Reasons for Points Lost | Amount |
|---|---|
| Link shared incorrectly | -10% |