Excelling at Excel - Use Filters to home in on the data you need

If you have a lot of data in an Excel worksheet, you should generally activate filters to make it more manageable to use. To do so, simply select any cell in the worksheet that contains data, click over to the Data tab, and click the Filter button.

Add filters to your worksheet

Your top row (usually a header row) will now have some new buttons added to it:

Filter buttons

Click these buttons to filter your data any way you want:


Either subtract any values you want to exclude, or click the Select All checkbox to quickly uncheck all choices and then add back the ones you want.

After you've made your selection(s), you can view the total number of records that meet your filter criteria at bottom left in your status bar, so you don't need to count them.

Records found in status bar

One caveat: Filtering only works with up to 1000 record, so if you have larger databases in Excel some results may be omitted. Although if your database is that large, it may be time to consider moving to Microsoft Access.