5.2 Filtering & Slicers

Learning Objectives

  • Filter table data.
  • Add a total row to a table.
  • Use slicers to filter data

FILTERING DATA

When you first create an Excel table, filter arrows appear in all the column headings. We have seen that you can use those arrows to sort your data by a single column. You can also use these same arrows to filter or limit the data you see by narrowing the displayed data within a column. There are many ways to filter data within a column depending on whether the data in the column is text or numeric. Table 5.5 gives you some filter examples:

Table 5.5 Filter Examples

Text Filters
Desired Results Filter Column Text Filter Checkbox Selected
Data for the State of New Jersey (NJ) State Equals NJ NJ
Data for Books that Have Gardening in Their Title Title Contains Gardening
Data for Weather on the Weekend Day Equals Saturday OR equals Sunday Saturday and Sunday

 

Numeric Filters
Desired Results Filter Column Number Filter Checkbox Selected
Data for Income Greater Than $1,000 Income Greater than 1,000
Data for Amount Paid Equal to Zero Amount Paid Equals 0.00 0.00
Data for Mortgage and Auto Loans Loan Type Equals Mortgage OR equals Auto Mortgage and Auto

Notice there are sometimes more than one way to filter data (i.e. – with a filter choice or a checked box). There are also single criteria filters, as well as, multi-criteria filters. We will explore all of these next.

To start filtering, let’s look at just the first week of data in the Weekly OR sheet:

  1. Click on the Weekly OR sheet and click on a cell in the table.
  2. Click the filter arrow to the right of the Week heading.
  3. Click the Select All checkbox to deselect all of the checkbox choices.
  4. Click on 1 to select Week 1.
  5. Click OK.

Your table should look like Figure 5.16. You should see only 7 rows of Week 1 data in your table. Notice in your Status Bar at the bottom of your screen the message “7 of 31 records found”. Also notice that the filter arrow in the Week heading has changed to a funnel which indicates that this column is currently filtered.

Portland, Oregon Worksheet B5 "Week" filter has funnel and circled in black.
Figure 5.16 Filter
 To remove your filter:
  1. Click the funnel next to the Week heading.
  2. Select “Clear filter from Week”.

Skill Refresher

Filter a Column

  1. Click the filter arrow to the right of the heading in the column you want to filter.
  2. Click the Select All checkbox to deselect all of the checkbox choices.
  3. Click on the checkboxes you want to filter by.
  4. Click OK.

Un-Filter a Column

  1. Click the funnel to the right of the heading in the column you filtered.
  2. Select Clear filter.
 Now let’s try a numeric filter. We want to find days in Portland ME when it’s warmer than 32 degrees in January:
  1. Click in the Portland ME sheet, then click on a cell in the table.
  2. Click on the filter arrow next to the High heading.
  3. Click on Number filters, then select Greater than. The Custom AutoFilter dialog box will appear on your screen.
  4. Enter 32 in the space to the right of “is Greater than”. Your Custom AutoFilter dialog box should now match Figure 5.17.
    Custom AutoFilter dialog box: "Show rows where: High (°F) is greater than 32°" entered.
    Figure 5.17 AutoFilter Dialog Box
  5. Click OK.

You should see that it was only above 32 degrees three days in January in Maine – the first three! Check your table against Figure 5.18.

Figure 5.18 Filter Results

Let’s review sorting and filtering in the following steps:

  1. Click on the Weekly OR sheet and clear the Day column filter.
  2. Sort the table by Week (smallest to largest).
  3. Filter the table to only show Mondays.
  4. Compare your table results to Figure 5.19.
    Portland, Oregon Column A "Day": Monday from cell A7,14,21,28 & 35 and Column B "Week" same row numbers showing 1,2,3, 4 & 5.
    Figure 5.19 Filter Results

Filtering using the Slicer

Beginning in Excel 2013, slicers were added to the software as another way to filter your table data. A slicer is really useful because it clearly indicates what data is shown in your table after you filter your data.

Let’s try using the Slicer to filter our Portland OR data table:

  1. Click on the Portland OR sheet and click in the table.
  2. In the ribbon’s Table Tools Design tab, click Insert Slicer.
  3. Click on Day in the Insert Slicers dialog box, and then click OK.
  4. Drag the slicer so that the upper left-hand corner lines up with the top corner of cell G5.
  5. Notice that when you insert a Slicer, a Slicer Options tab appears on the ribbon. This tab lets you change the style and size of the entire slicer or the individual slicer buttons.
  6. Click on the Slicer options tab, then click on the More button next to Slicer Styles. The choices in Figure 5.20 will show on your screen.
Slicer Styles box with "Light" and "Dark" categories.
Figure 5.20 Slicer Styles
  1. Select the first choice under Dark.
  2. In the Size group on the Slicer Options ribbon (NOT the Buttons group), change the width to 1”.
  3. Click in the table and scroll down to Day 15 and click the 15 button to show only the data for January 15th in the table.
  4. Hold down the CTRL key and click on the Slicer buttons for Days 10 through 14. Your table should now show the data from Days 10-15.
  5. Sort the Day column in Ascending order to show the days in order as in Figure 5.21.
Column A "Day" cell data from 10 down to 15. Day slicer box on right with days 10-15 selected.
Figure 5.21 Slicer Results

Total Rows

By adding a total row to the bottom of your table, you can quickly see summary data for one or more of the columns in your table. Total rows can be added to tables as a whole, or those that are filtered. Total rows can easily be toggled on and off as the need for summary data arises.

  1. Click on the Portland ME sheet and clear the filter from the High column.
  2. Click on the Total Row check box in the Table Style Options group in the Table Tools Design tab in the ribbon.
  3. Scroll to the bottom of your table to the Total Row. Notice the total for the Snow data.
  4. Click on D37 (in the Rain column), and then click the down-arrow that appears to the right of the cell.
  5. Choose Sum to add a sum to the Total Row in the Rain column.
  6. To see the Average rainfall for the month of January, click on the arrow again and choose Average.
  7. Repeat this step in E37 to see the Average snowfall.
  8. Use the Decrease Decimal button in the Home tab of the ribbon to change the decimal places in D37 and E37 to 2. Compare your Total Row to Figure 5.22.
Total Row added with "Total" bold, black in cell A37. Totals for column D & E: Cell D37 has 0.11 and E37 has 0.62.
Figure 5.22 Total Row
  1. Now switch to the Weekly OR sheet and see if you can successfully add a Slicer and Total Row to this table:
  2. Clear the filter from the Day column.
  3. Add a Slicer for the Day column to the sheet.
  4. Move the top left corner of the slicer to H5. Resize it as needed and choose a Slicer Style.
  5. Select Monday through Friday in the Slicer so that Saturday and Sunday data do NOT show in your table.
  6. Add a Total Row that averages the High and Low columns. Your averages should be High: 47.0 and Low: 35.8. Change the label “Total” to “Average” by clicking A37 and typing Average.

Skill Refresher

Add a Total Row

  1. Click on the Total Row check box in the Table Style Options group in the Table Tools Design tab in the ribbon.
  2. Scroll to the bottom of your table to find the Total Row.
  3. Click in one of the columns in the Total Row, and then click the down-arrow that appears to the right of the cell.
  4. Choose Sum to add a sum to the Total Row in the column.
  5. To see the Average for column, click on the arrow again and choose Average.

Some other choices in the Total Row are Count (for words), Count Numbers, Max, and Min.

 

 

Skill Refresher

Add a Slicer

  1. Click on Insert Slicer in the Table Tools Design tab in the ribbon.
  2. Check the box for the column to which you want to add a Slicer.
  3. Click OK.

Key Takeaways

  • Filtering is an easy way to see a subset of your data. Filtering arrows appear to the right of each column heading when you insert a table with a header row.
  • You can filter by text or numerically.
  • A slicer is another way to filter in Excel that provides a set of filtering buttons on your sheet.
  • Adding a total row to a table is a quick, efficient way to see summary statistics for one or more columns in a table.

Attribution

“5.2 Intermediate Table Skills” by Diane Shingledecker, Portland Community College is licensed under CC BY 4.0

Media Attributions

  • figure-5-16-filter
  • FIgure 5.18 Filter Results
  • figure-5-18-custom-autofilter
  • figure-5-22-total-row

License

Icon for the Creative Commons Attribution 4.0 International License

5.2 Filtering & Slicers Copyright © by Emese Felvegi; Noreen Brown; Barbara Lave; Julie Romey; Mary Schatz; Diane Shingledecker; and Robert McCarn is licensed under a Creative Commons Attribution 4.0 International License, except where otherwise noted.