How to Filter Data in Excel

Learning how to filter data in Excel will help you analyse data faster and become better at your job. Filtering in Excel enables you to display only the data that you want to see on your spreadsheet without deleting anything. It’s a really great way to search through large amounts of information and you know I’m all about Excel tips that help improve your speed and efficiency!

There are three types of filters in Excel: list of values, by format, or criteria and you can sort your spreadsheet by order, color or text. What’s the difference between sorting and filtering? Sorting will rearrange the order of your list while filtering keeps the order but actually hides data based on your filter criteria.

To add a Filter in Excel, you can first select a single cell within your range of data but I recommend you highlight all your data (please note you cannot add filters to empty cells). Next, go to the Home tab then the Editing section. Under Sort & Filter click Filter (or use the filter shortcut Crtl+Shift+L). Once filtering is turned on you will see little arrows along your top row of data. Select one of the arrows to set your filter options. If you hover your mouse over the drop down arrow you will see a pop-up message displaying what the value the filter is currently set to (example: equals “Test”).

 

To remove the filtering from your spreadsheet, simply click the Filter button again. If you want to reset the filter to the original values click on the Sort & Filter button and then click “Clear."

You can also turn auto-Filter on and off with a VBA macro:

Sub TurnAutoFilterOn()

'check for filter, turn on if none exists

If Not ActiveSheet.AutoFilterMode Then

ActiveSheet.Range("A1").AutoFilter

End If

End Sub

Turn off AutoFilter with VBA:

Sub TurnFilterOff()

Worksheets("Sheet1").AutoFilterMode = False

End Sub

Here’s a great video showing you exactly how to filter data in Excel:

Follow us on Google Plus for the latest updates when I will talk about dynamic filters and Kalman filters in Excel.