Filtering data – to find just what you need

Filtering is a way of pulling out only the data you need to focus on and hiding all the rest.

What is filtering? I hear you ask.

Filtering is a way of pulling out only the data you need to focus on and hiding all the rest. As opposed to Sorting which is arranging all the data according to one or more criteria.

The data I am using here is from the sales of coffee at Koffee Island which is the coffee shop that features in my Excel book – did I mention I have written a book? It is called Business Barista – Essential Excel Skills to Streamline your Business and can be purchased from Amazon here.

Now how about that filtering

Filter data - the filter icon

Filtering allows you to hide the information that you do not want to see. So if I want to see sales of Cappuccino in London only, I can use the filter option to do just that. Then I can change the filter to suit my needs and refocus on different sets of data.

Filter on the Home Tab

  1. Click anywhere inside your list
  2. On the Home Tab click Sort and Filter
  3. Click Filter

Filter on the Data Tab

  1. Click anywhere inside your list
  2. Click on the Data Icon
  3. Click Filter in the Sort and Filter Group

Filter on Text

Now that you know where the filter lives, let's use it to find sales of Cappuccino.

  1. Click on the drop down arrow by the column heading you want to filter
  2. Un-check Select All
  3. Select the item on which you want to filter
  4. Click OK

I now have only rows where the value in the Item column is Cappuccino. All the other rows are hidden.

Use the Right Click

  1. Right click on the cell with the value you want to filter out - in my example, I have clicked on a cell with Coca Cola
  2. Click Filter
  3. Click on Filter by Selected Cell's Value

You will now see only rows with the value you chose.

Filter on more than one column

I would now like to see the sales of Cappuccino in Leeds only. I will apply the filter to both the Item and the Centre columns.

Apply the filter to another column

  1. Click on the drop down arrow next to the second column to filter on
  2. Un-check Select All
  3. Click to select the item on which you want to filter
  4. Click OK

I now have only rows where Cappuccino was sold in Leeds. You can tell that rows have been hidden by looking at the row numbers - which will also have turned blue.

Remove the filter

Now I want to see all of the data. There are a few ways to clear the filter.

Clear the filter

Click on Clear

All the data comes back with the Auto filter arrows still in place

Turn off the filter

Click on the filter icon

The filter is cleared and the auto filter arrows are removed.

Clear the filter from one column only

  1. Click the drop down arrow of the column whose filter you want to clear
  2. Select Clear Filter From "the field name"
  3. Click OK

Remove with Right Click

  1. Right click in the column you have filtered
  2. Select Filter
  3. Select Clear Filter From "Name of Column" (in our example it is from the Centre Column)

Filter data on colour

If you have applied conditional formatting to your list to show data with certain cell colours, or font colours, you can make Excel show you just those cells. Just like when sorting by colour.

Filter on fill colour

In the Centre column I have made certain of the Centres show up in different colours.

  1. Click the drop down of the column you want to filter
  2. Click on Filter by Selected Cell’s Fill Colour
  3. Click OK

Only cells that have a the fill format selected will be shown.

Filter on font colour

I have changed the font colour for certain values

  1. Click the drop down of the column you want to filter
  2. Click on Filter by Cell Colour
  3. Choose the Colour you want to see
  4. Click OK

Text filters

Say you need to filter out rows that begin with a certain letter or contain a particular value, that contain a particular word or in fact do not contain a particular word. These can all be done from the drop down arrows.

Here we will take a look at Text Filters.

Click the drop down arrow on the heading of the column to filter

  1. Select Text Filters
  2. Choose the filter you want to apply

Number filters

Excel has many more filters for numbers than a simple select all rows with this number option. We can look for rows with a value greater than one we specify, or less than, equal to or between.

Number filters

  1. Click the drop down arrow on the heading of the column you want to filter
  2. Select Number Filters
  3. Choose the filter that matches your criteria

Date filters

What about finding all sales that happened in a particular month or range of dates? Yes with Excel's date filters you can do just that.

Date filters

There is a huge choice of date filters. Have a play around with some test data

To learn more about Microsoft Excel and how you can speed up routine tasks head over to Essential Excel for The Savvy Assistant which is just one of the courses that make up Essential Office for The Savvy Assistant.



Categories: Blog, Microsoft Excel

© 2024 msofficemaestro.com