Filters

Introduction
Sometimes you have a larger set of data in your Excel sheet than you need to examine or work with.
Excel has filtering options to be able to reduce your data to a subset. It is also possible to filter on a
filter to even further refine your subset.

Simple Filters
1. Open the CIS308SalesData.xlsx file found on Blackboard.
2. Click on the File tab, then choose Save As. Save the file to your computer as “Assignment 5.[Your
Last Name].xlsx”.
3. Click on cell A2.
4. In the Editing area of the Home tab, use the pull down on Sort & Filter and choose [Filter].
5. Notice that each of the column headers now has a pull down arrow.

Filtering Text and Numbers

6. Use the pull down on the Item option in column D and unselect the ‘Desk’ option.
7. Click [OK].
8. You now see only those options with an Item type equal to Binder, Pen, Pen Set or Pencil.
9. Note that the pull down on the Item header also has a symbol added that looks like a funnel. This
signifies that there is an active filter on the column. The bottom border of the worksheet on the left
side also shows the number of filtered records as well as the total records.
You can use the pull down on the Item option to reshow all the data by re-checking the box next to the
‘Desk’ option. For now, leave it unchecked.
10. While the data is filtered for Item, you can also add an additional filter.
11. Use the pull down on the Region option in column C and unselect the ‘East’ and ‘West’ options.
12. Click [OK].
You have now filtered down your data subset further.

You can also filter using number criteria:

13. Use the pull down on the Unit option in column E and, from the Number Filters expansion area
choose Less than…
14. The Custom AutoFilter box opens.
15. On the left you’ll see an option box (with a pull down) that contains the text ‘is less than’.
16. The cursor defaults to an option box (also with a pull down) to the right of the one described above.
In this box, type ‘50’.
17. Click [OK].
18. You are now filtering for units less than 50, while maintaining the filter on the Item and Region
fields.
Multiple Filters
You can also have filters on multiple columns:
19. Use the pull down on the Sales Rep option in column J and from the Text Filters expansion area
choose Begins With…
20. The Custom AutoFilter box opens.
21. On the left you’ll see an option box (with a pull down) that contains the text ‘begins with.
22. The cursor defaults to an option box (also with a pull down) to the right of the one described above.
In this box, type ‘G’.
23. Click [OK].
The original filter on this column stays and the new filter is also added. Your data has been filtered down to
three records.

You can also use wildcards with your filters. An asterisk (*) stands for any number of characters (so *G*
would show any that contain the letter G), while a question mark (?) stands for one character (G?).

With numbers, you can also filter for Top 10, Above Average or Below Average. The Top 10 option also
allows you to change to include more values (i.e. 20 instead of 10) or select the bottom values instead of the
top.
Note** When working with filtered data, the SUM function does not work – if you try to AutoSum a group
of cells, Excel will use the Subtotal function instead.

Filtering Dates

We’ve looked at filtering text and numbers, but Excel uses different criteria for dates.

24. Use the pull down on the Order Date option in column B and from the Date Filters expansion area
choose All Dates in the Period and from that expansion area choose Quarter 3.
25. Note the expansive number of ways you can filter dates with Excel.

For more tips on filtering with dates and numbers, watch the Lecture 5 video.

Filtering by Color and Icons

You can also filter in Excel based on the background color in each cell.

26. Click in cell A1. Clear all the filtering. The easiest way to do this is to go to the Data tab in the
Ribbon. In the Sort & Filter area, the Filter option is highlighted. Click this twice and it will remove all
filtering and then add the filtering option back, with no data selected.
27. In the Region fields in column C, use the [Fill Color] option in the Font area of the Home tab to fill
the cells – use gold for East, and maroon for the West Region.
28. Use the pull down on the Region option in column C and from the Filter By Color expansion area
choose the Gold color box.

You can also filter based on Conditional Formatting setup (by Icons)

29. Highlight the Order # column by clicking on the letter A at the top of the column.
30. Click on the [Conditional Formatting] option in the Styles area of the Home tab.
 31. From the Icon Sets expansion area, choose the 5 Boxes option from the Ratings area (it’s the last
choice right above ‘More Rules’).
32. Use the pull down on the Order # option in column A and from the Filter By Color expansion area
choose the Icon with 3 solid blue boxes and 1 white.
33. Save the file.

Advanced Filtering
Excel also functions to let you create more intricate filters. Two separate cell range areas are organized; one
to define the data and the other to define the filter criteria. The filter criteria range area is typically situated
above the data cell range so as to match column heading filters, but it can actually be situated anywhere in
the spreadsheet. We’ll look at it in the typical fashion.

34. Make a copy of the Sales sheet by right-clicking on the Sales tab and choosing ‘Move or Copy’. Leave
all other options, but check the Create a copy box just above and to the left of the OK button.
35. Click [OK]
36. On the new tab, called Sales (2), clear the filters. Do not click Filter twice, just once.
37. Highlight rows 2-6, right click and choose Insert.
38. Highlight row 1, right click and choose Copy.
39. Highlight row 6 and press [Enter].
You should now have a header row, then four empty blue rows followed by a second header row and then
the data.

40. Click in cell J2.

41. Type ‘Jones’ and press [Enter].
42. In the Sort & Filter area of the Data tab, click [Advanced].
43. The Advanced Filter box opens.
44. Type ‘A1:J51’ in the List Range area – if it defaults to this option, but with dollar signs, you can leave
it.
45. Type ‘A1:J2’ in the Criteria Range area.
46. Click [OK] to run the filter.

You can also use Advanced Filtering on multiple filter options.

47. In cell E2, type ‘>30’.
48. Click [Advanced] again and then [OK].

You can also use it on multiple filters with multiple criteria.

49. Click on [Advanced] and update the Criteria Range to A1:J4 to expand the selection and show the
existing criteria.
50. In cell E3 type ‘<10’ and in cell J3 type ‘Andrews’.
51. Click on [Advanced] and update the Criteria Range to A1:J3 and click [OK] to run the filter.
Note that each line sets up its own criteria, like using an Or option – and the resultant data shows the results
for both sets of criterion together.
Note** the Unique records only option can be used to remove duplicate records from a dataset.
Using Multiple Sheets

With advanced filtering you can also set up your criteria on a different worksheet from the data.

52. Click on [Advanced] and update the Criteria Range to A1:J5 to expand the selection and show the
existing criteria and click [OK].
53. On the Sales (2) tab, highlight Rows 1 and 2, right click and choose Copy.
54. Go to the Sales tab, click in cell A60 and press [Enter].
55. Go back to the Sales (2) tab, highlight rows 1-5, right click and choose Delete.
56. Go back to the Sales tab and click in cell A60.
57. Click on [Advanced].
If you get the following error, click [OK].

58. Click the Copy to another location option.

59. Click in the List Range area, highlighting any data that is already there.
60. Click on the Sales (2) tab – this will put ‘’Sales (2)’!’ into the List Range location. Type ‘A1:J46’ so
that the List Range contains ‘Sales (2)’!A1:J46
61. In the Criteria Range area, type ‘A60:J61’.
62. In the Copy To area, type ‘A65’ and click [OK] to run the filter.
The filtered data appears on the Sales tab, with the data remaining on the Sales (2) tab.
63. On the Sales (2) tab, Filter for only maroon Region items.
64. You’ll see that the filter on the Sales tab does not change, though the appearance of the Sales (2)
tab has. That is because the data is still there, just filtered.

Save the file, and in Canvas, upload your assignment document as the response to Question 1 in the
Lecture 5 assessment, then answer the rest of the assessment based on your file and/or comprehension of
the lecture.