21. Filtering and Consolidating Data
This chapter shows you how to use Excel’s filtering functionality to look at just the desired records. It also shows you how to create a list of unique items, delete duplicates, and consolidate data.
Filtering and consolidating data are important tools in Excel, especially when you are dealing with large amounts of data. The filtering tools can quickly reduce the data to the specific records you need to concentrate on. The consolidation tool can bring together information spread between multiple sheets or workbooks.
Filtering Data
To make the most out of Excel’s filtering capabilities, your data should adhere to a few basic formatting guidelines:
• There should be no blank rows or columns. The occasional blank cell is acceptable.
• There should be a header above every column.
• Headers should be in only one row.
If you don’t follow these guidelines, Excel can get confused and is unable to find the entire table or header row on its own. Also, Excel can only work with one header row—any rows after the first header row get treated like data.
Applying a Filter to a Data Set
Filtering enables you to view only the data you want to see by hiding the other data. You can apply a filter to multiple columns, narrowing the data. As you filter the data and rows are hidden, the row headers (1, 2, 3, and so on) become blue. Anytime you see blue row headers, you know the data has been filtered. An icon that looks like a funnel replaces the arrow on the column headings that have a filter applied, as shown in the Customer heading in Figure 21.1.
FIGURE 21.1 You can tell which column(s) are filtered by the filter icon where the drop-down arrow used to be.
The Filter button is a toggle button. Click it once to turn filtering on and click it again to turn filtering off. To activate the filtering option, select a single cell in the data set and use one of the following:
• On the Home tab, click the Sort & Filter button and select Filter.
• On the Data tab, click the Filter button.
Caution
It is very important to select only a single cell because it is possible to turn on filtering in the middle of a data set if you have more than one cell selected.
When a filter is applied to a data set, drop-down arrows appear in the column headers. Click an arrow to open up the Filter dialog box, which remains open until you click OK, Cancel, or outside the dialog box. One or more selections can be made from each drop-down, filtering the data below the headers. Filters are additive, which means that each time a filter selection is made, it works with the previous selection to further filter the data.
Tip
If you have a long list of items or need to widen the dialog box to see the full text of a line, place your cursor over the three dots in the lower-right corner of the dialog box, and click and drag to resize. The change in size will not be saved—you need to do it again next time you open the dialog box.
Managing Filters
After applying a filter, you can modify, reapply, or clear it in a variety of ways.
Turning Filtering On for One Column
You can turn on filtering for a single column or for two or more adjacent columns. Even though you can only select a filter item in select columns, the filter is applied to the entire table. This can be useful if you want to limit the filtering users can apply. If the sheet is then protected, users cannot turn on filtering for the other columns. (See “Allowing Filtering on a Protected Sheet” for more information.)
To control what column has filtering, select the header and the first cell directly beneath the header. Then do one of the following:
• On the Home tab, click the Sort & Filter button and select Filter.
• On the Data tab, click the Filter button.
Filtering for Listed Items
The filter listing, shown in Figure 21.2, is probably the most obvious filter tool when you open the drop-down. For text, numbers, and ungrouped dates, a listing of all unique items in the column appears. (See the “Filtering the Grouped Dates Listing” section if dates appear grouped by year and month.) All items will be checked because they are all visible the first time you open the drop-down, but you can select just the items that should appear in the data. Any item that no longer bears a check mark will be hidden.
We have a table of the sales of various printers in 2011 and 2012. We want to narrow the table down to Laser Printers sold to Bright Hairpin Company. To filter columns for specific items, follow these steps (if filtering a Table [Insert, Tables, Table], skip to step 3):
1. Select a single cell in the data set to apply filtering to.
2. On the Data tab, click the Filter button.
3. Open the drop-down of the Product column.
4. Unselect the Select All item to clear all the check marks in the list.
5. Select Laser Printers and click OK. Now only Laser Printers are shown in the table.
6. Repeat steps 3–5 for the Customer column and Bright Hairpin Company.
7. Click OK. The sheet updates, showing only the items selected.
Filtering the Grouped Dates Listing
Note
This section applies to dates that are grouped, as shown in Figure 21.3. The grouping is controlled by the Group Dates in the AutoFilter Menu check box on the Advanced tab of the Excel Options dialog box. (Select File, Options to open this dialog box.) By default, this option is selected. If unselected, the dates appear in a list like the filter for number and text items. You can refer to the section “Filtering for Listed Items” for more information on that type of filter listing.
Dates in the filter listing are grouped by year, month, and day. All items are checked because they are all visible the first time you open the drop-down, but you can select just the items that should appear in the data. Any item that no longer bears a check mark will be hidden.
If you click the + icon by a year, it opens up, showing the months. Click the + icon by a month, and that month opens to show the days of the month. An entire year or month can be selected or unselected by clicking the desired year or month. For example, to deselect 2011 and January 2012 in Figure 21.3, deselect the 2011 group, and then deselect the January group under 2012. The data filters to show only February through December 2012.
To filter for only June 12–14, 2012, follow these steps (if filtering an existing Table, skip to step 3):
1. Select a single cell in the data set to apply filtering to.
2. On the Data tab, click the Filter button. Click OK to continue.
3. Open the drop-down of the Date column to filter.
4. Deselect the Select All item to clear all the check marks in the list.
5. Click the + icon to the left of 2012.
6. Click the + icon to the left of June.
7. Select 12, 13, and 14.
8. Click OK. The sheet updates, showing only the dates selected.
Using Text, Number, and Date Special Filters
With careful planning or additional columns, you can filter for all types of reports such as top 10, quarterly reports, and ranges of values. But not all filtering is easy or quick. The filter listing has options to make it easier. Special filters are available in the filter drop-down depending on which data type (text, numbers, or dates) appears most often in a column. All the special filters, except for ones that take action immediately, open the Custom AutoFilter dialog box, allowing two conditions to be combined using AND or OR.
If the column contains mostly text, Text Filters is available from the filtering listing. Choosing Text Filters opens a list with the following options: Equals, Does Not Equal, Begins With, Ends With, Contains, and Does Not Contain. Selecting one of these options opens a Custom AutoFilter dialog box, in which you can enter text to filter by.
Tip
When entering the text you want to filter by, you can use wildcards. Use an asterisk (*) to replace multiple characters or a question mark (?) to replace a single character.
If the column contains mostly numbers, Number Filters is available with the following options: Equals, Does Not Equal, Greater Than, Greater Than or Equal To, Less Than, Less Than or Equal To, Between Top 10, Above Average, and Below Average. Selecting Top 10, Above Average, or Below Average automatically updates the filter to reflect the selection.
If Top 10 is selected, you can specify the top or bottom items or percent to view. For example, you could choose to view the bottom 15% or the top seven items.
For columns with dates, the special filter, Date Filters, offers a wide selection of options, including additional options under All Dates in the Period. The options dealing with quarters refer to the traditional quarter of a year, January through March being the first quarter, April through June being the second quarter, and so on. When using the Custom AutoFilter option, the dialog box for dates includes calendars to aid in data entry.
To create the bottom 25% report for the previous quarter shown in Figure 21.4, follow these steps:
1. Open the drop-down of the Date column.
2. Select Date Filters, All Dates in the Period, Quarter 2. The data automatically filters to the 2nd quarter, April–June.
3. Open the drop-down of the numeric column, Quantity, to filter.
4. Select Number Filters, Top 10. The Top 10 AutoFilter appears.
5. Select Bottom from the leftmost drop-down.
6. Enter 25 in the middle field.
7. Select Percent from the rightmost drop-down.
8. Click OK; the table filters show the bottom 25%.
FIGURE 21.4 The special filters make it easier to create quarterly reports reflecting records that fall within specific parameters.
Filtering by Color or Icon
Data can be filtered by font color, color (set by cell fill or conditional formatting), or icon by going to the Filter by Color option in the filter listing. There, colors and/or icons used in the column are listed, as shown in Figure 21.5.
The data table in Figure 21.5 uses conditional formatting icons to denote which record quantities are below average (arrow), average (exclamation mark), or above average (star). Also, quantities greater than or equal to 1,000 have a green cell fill. To filter to show only the above average items, open the drop-down filter for the Quantity column. Select Filter by Color and then from the icons listed, select the star. The list automatically updates to show only the star records.
Note
Refer to the section “Applying Conditional Formatting” in Chapter 17, “Formatting Sheets and Cells,” for steps on how to apply your own custom icon formatting.
Filtering by Selection
Even without the filter turned on, you can right-click any cell in a column, go to Filter, and choose to filter by the cell’s value, color, font color, or icon. Doing so turns on the AutoFilter and configures the filter for the selected cell’s property.
This filtering is additive. If you filter a cell by one value and then go to a cell in another column and filter by its value, the result is those rows that satisfy both filter criteria. You cannot filter by a property more than once within the same column, but you can filter a column by multiple properties—for example, filter by icon and then value.
To use filter selection to filter records meeting a specific value and icon, such as the below average East records in Figure 21.6, follow these steps:
1. Right-click a cell in the Region column containing the value (East) to filter by.
2. Select Filter, Filter by Selected Cell’s Value. The AutoFilter drop-downs appear in the column header and the table updates to show only East records.
3. Right-click a cell in the Quantity column containing the icon (a down arrow) to filter by.
4. Select Filter, Filter by Selected Cell’s Icon. The table now shows all below average records from the East region.
FIGURE 21.6 Right-clicking a specific cell enables you to quickly filter by that cell’s value, cell color, font color, or icon.
Allowing Filtering on a Protected Sheet
Normally, if you set up filters on a sheet, protect the sheet, and then send it out to other users, the recipients won’t be able to filter the data. If you want others to be able to filter your protected sheet, follow these steps:
1. Select a single cell in the data set to apply filtering to.
2. On the Home tab, click the Sort & Filter button and select Filter from the menu. The filter is turned on for the data set.
3. On the Review tab, click the Protect Sheet button.
4. In the listing for Allow All Users of This Worksheet To, scroll down and select Use AutoFilter.
5. Optionally, enter a password in the Password to Unprotect Sheet field if you want to apply a password to the sheet.
6. Click OK.
7. If you entered a password in step 5, Excel prompts you to reenter the password. Do so and click OK.
Users won’t be able to modify the data you’ve protected, but they can use Excel’s various AutoFilter tools to filter the data as needed.
Note
For more information on protecting your data, see “Protecting the Data on a Sheet” in Chapter 22, “Preparing Workbooks for Distribution and Printing.”
Clearing a Filter
You can clear a filter from a specific column or for the entire data set. To clear all the filters applied to a data set, use one of the following methods:
• On the Home tab, click the Sort & Filter button and select Clear.
• On the Data tab, click the Clear button.
• Turn off the filter entirely using one of the following methods:
• On the Home tab, click the Sort & Filter button and select Filter.
• On the Data tab, click the Filter button.
To clear all the filters applied to a specific column, use one of the following methods:
• Click the filter drop-down arrow and select Clear Filter from Column Header.
• Click the filter drop-down arrow and select Select All from the filtering list.
• Right-click a cell in the column to clear and select Filter, Clear Filter from Column Header.
Reapplying a Filter
If data is added to a filtered range, Excel does not automatically update the view to hide any new rows that don’t fit the filter settings. You can refresh the filter’s settings so they include the new rows through one of the following methods:
• On the Home tab, click the Sort & Filter button and select Reapply.
• On the Data tab, click the Reapply button.
• Right-click a cell in the filtered data set and select Filter, Reapply.
Removing Duplicates from a Data Set
You’ve received a report where a user duplicated data by importing twice. You could try sorting the data and creating a formula in another column that compares rows, but with more than 700,000 rows, it could take a while for the formula to calculate, and you aren’t even sure if the process is foolproof. Instead, use the Remove Duplicates option to ensure the process. You can remove duplicates by clicking the Remove Duplicates button on the Data tab. If the data set is a Table, click the Remove Duplicates button on the Table Tools – Design tab.
Caution
The tool permanently deletes data from a table based on the selected columns in the Remove Duplicates dialog box. Unlike other filters, it does not just hide the rows. Because of this, you might want to copy the data before deleting the duplicates.
To remove duplicates based on the Region, Product, Date, and Customer, follow these steps:
1. Select a cell in the data set.
2. On the Data tab, click the Remove Duplicates button.
3. Excel highlights the data set. If columns are missing in the selection, go back and make sure there are no blank separating columns.
4. From the Remove Duplicates dialog box, make sure My Data Has Headers is selected if the data set has headers.
5. By default, all the columns are selected. A selected column means the tool uses the columns when looking for duplicates. Duplicates in an unselected column are ignored. In the Columns list box, select the columns to use in the search for duplicates, as shown in Figure 21.7.
FIGURE 21.7 Remove Duplicates enables you to specify which columns you want to use to verify duplicate records.
6. Click OK. The data set updates, deleting any duplicate rows. A message box appears informing you of the number of rows deleted and the number remaining in the data set.
Consolidating Data
The Remove Duplicates tool is great for completely removing duplicates, but what if you wanted to remove duplicates based on some fields and, at the same time, combine the data of other fields? For example, you have a sheet with 2011 data and a sheet with 2012 data. You need to create a quantity sold report combining the data based on the company name but separating the different years. You could create a pivot table or use the Consolidate tool. The Consolidate tool, found under Data Tools on the Data tab, helps you create a report of unique records with combined data. It even combines data from different sheets and workbooks. You can do this in one of three ways:
• By Position—Sum data found on different sheets or in different workbooks based on their positions in the data sets. For example, if the ranges are A1:A10 and C220:C230, the results are A1+C220, A2+C221, A3+C222, and so on. Do not select either of the options under Use Labels In.
• By Category—Sum data found on different sheets or in different workbooks based on matching row and column labels, similar to a pivot table report. The references must include the labels in the leftmost column of the ranges. Select either or both of the options under Use Labels In to have the labels appear in the final data.
• By Column—Combine the data to a new sheet, with each data set in its own column. Select the Top Row option under Use Labels In.
The Reference field is where the data sets are entered. Click Add to add the selection to the All References list. If the data set is in a closed workbook, you can reference it only by using a range name. Click the Browse button to find and select the workbook. After the exclamation point (!) at the end of the path, enter the range name assigned to the data set.
Note
See “Using Names to Simplify References” in Chapter 18, “Using Formulas,” for details on how to create a range name.
If Create Links to Source Data is selected, the consolidated data updates automatically when the source is changed. Also, the consolidated data is grouped, as shown in Figure 21.8. Click the + icon to the left of the data to open the group and see the data used in the summary. Column B of the report shows the name of the workbook in the first instance of its data.
FIGURE 21.8 When Create Links to Source Data is selected, the final report includes the individual values of the selected references.
A few things to keep in mind when using this tool:
• The range selected must be adjacent columns.
• If your reference field consists of multiple sheets, as you go from sheet to sheet, Excel automatically selects the same range as the previous sheet.
• If you select Left column, data is combined based on the leftmost column of the selection and the selected function is used on all other columns in the range.
• If doing a consolidation by position or by column, you have to select a specific range, versus clicking the column headers to select the entire column.
• The range on which the function is applied must be numerical.
You have two reports, sales from 2011 and sales from 2012, on separate sheets. To combine the customers and the quantity sold onto a single report, follow these steps:
1. Select the top leftmost cell where the consolidated report should be placed, such as cell A1. If other data is on the sheet, make sure there is enough room for the new data.
2. On the Data tab, click the Consolidate button.
3. Select the desired function, SUM, from the Function drop-down.
4. Place the cursor in the Reference field.
5. Go to the sheet with the desired data set.
6. Select the data set, making sure the labels to be combined are in the leftmost column and that the column headers are included in the selection.
7. Click the Add button.
8. Repeat steps 4 to 7 for each additional data set.
9. To include the top and/or left column labels, select the corresponding option. If combining text fields, as we are here, Left Column must be selected.
10. Click OK.