How To Use Filtering In Excel 2010/2013/2016

Filtering is a genius tool if you have lots of data on your worksheet. Imagine a spread sheet where you have hundreds or even thousands of records and you want to either delete a certain kind of record or find the total value for them. It is possible to sort the records, but another simple option is to use the filtering functionality. Sorting only really helps if the values you are looking for come to the top of the list. If they don't then you will still need to scroll down which is a waste of time.

To add filtering to a workbook, highlight the first row. This example assumes that the first row contains headers. You don't have to use the first row; instead you could highlight only a few columns to filter on. I prefer to put a filter on all columns so I can perform multi-column filtering if required. Go to the Data tab on the ribbon and click the Filter button, which has a little funnel symbol above it. Notice that this places a drop down list symbol on each column header.

To begin exploring the functionality, choose a column you want to filter on. In this example I am assuming I have a sales spread sheet which contains one column for the Sales Office, another column for the Sales Person and another column for the value. I want to return the total sales value by sales office and within that I want to look at how much certain people in certain offices have sold. Click the filter symbol on the Sales Office column and you will see that all the values within the list are given. This allows you to easily select individual ones by using the check boxes next to the names. Choose a value and notice that the records are now reduced on the page to show only the selected ones. In order to be clear that not all records are shown, the row numbers on the left will turn blue and the symbol on the column you are filtering on will change.

This is the simplest form of filtering, but it can get more advanced, without being complicated. For example, I once sent a spread sheet out to a colleague full of hundreds of contact records. I asked her to indicate which contacts were no longer relevant. What I should have done was ask her to put a cross next to each record, but instead she coloured each record red. The filtering functionality does now allow the user to filter based on colour. This option will only become available if there are colours on the sheet. Choose filter by colour and a list will come up showing all possible colours available to filter on, separated by font colour or background colour as relevant.

Sometimes filtering is not so simple due to the nature of the data. Consider a worksheet that contains a column with the Company Name. Users have been able to manually enter data on the sheet for a long time resulting in the same company being on the sheet, but written in a variety of ways. One company is called 'Foxbury Tiles Ltd.' Due to some human errors it has been misspelled and sometimes the Ltd part is written out in full. This would make simple filtering difficult since filtering on one of the company names would miss out some relevant records.

This is where you can use the Text Filters options. This will bring back records that begin with, end with, contains specified characters, does not contain etc. In our example the user could specify all records that contain the character 'Fox' which would stand a good chance of including all the company records. It is also possible to filter on two things such as contains Fox AND Ltd. The AND/OR functionality can incorporate a broader spectrum of results.

Filtering in this way is not limited to one column at a time. If you have successfully filtered the sheet so that all records for the Hounslow office are showing, you can then filter on the Sales Person column to bring back sales only for specific sales people. Just as text fields have text filters, the number fields allow specific numeric filters such as greater than, less than or equals to. This would be useful if you wished to see all sales over a certain value. Generally different kinds of fields will automatically present their own range of filters. Date fields can be filtered on this month, next month etc.

Along with other Excel functionality such as sorting and charting capabilities, filtering allows the user to manage their data to meet their specific requirements. It is worth experimenting with the filtering to get a better idea of how the functionality can be used to your advantage.

With our Super Excel training courses you will spend only 20% of the time to master enough Excel skills that will contribute to 80% of your job performance. The secret is – do not try to learn everything Excel has to offer, only learn the part that is most useful, and especially the creative ways those features and functions can be combined to process and analyze data

You can reach us on 08062468296 or send an email to [email protected]