Add and format tables

Filter data in a table

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

Discover more Excel training at LinkedIn Learning

Filters provide a quick way to find and work with a subset of data in a range or table. When you filter a list, you temporarily hide some of the data, so you can focus on the data you want.

Filter data

  1. Select a cell within the data that you want to filter.

    Note: Before you filter your data, make sure there are no empty rows or columns.

  2. Select Home > Sort & Filter > Filter.
    Or, select Data > Filter.

  3. Select the filter drop arrow of the column you want to filter.

  4. Uncheck Select All, and check the filters you want.

  5. Select OK. The filter drop arrow changes to a filter Filter to show the column is filtered. The Blue row numbers show which rows are included in the filtered data.

  6. To remove the filter, select Clear. All data displays.

    Note: To apply filters on multiple columns, select the first column, apply the filter, and then select the next column and apply a filter on one.

Apply a custom filter

  1. Select the filter drop arrow and then select:

    • Text Filters - available when the column Contains text or a mix of text and numbers: Equals, Does Not Equal, BeginsWith, EndsWith, or Contains.

    • Number Filters - available when the column contains only numbers: Equals, Does Not Equal, Greater Than, LessThan, or Between.

    • Date Filters - available when the column contains only dates: Last Week, Next Month, This Month, and Last Month.

    • Clear Filter from ‘Column’ - available when the column is already filtered. Select this option to clear the filter.

    • Select And if both conditions must be true.

    • Select Or if only one of the conditions needs to be true.

  2. Enter the filtering conditions.

  3. For example, to view the numbers greater than a specific number, select Number Filters > Greater Than Or Equal To and then enter the specific number.

  4. To filter results by two conditions, enter the filter conditions in both boxes.

  5. To change the order of the filter results, select the filter drop-down, and then select either Sort Largest to Smallest or Sort Smallest to Largest.

Want more?

Discover more courses like this at LinkedIn Learning

Filter data in a range or table

Filter data in a PivotTable

Any number of different reasons for not wanting to see all the data and focus on just the data you need to see. The feature's called Filter.

You can, from the Home tab, far right, click Sort & Filter, and then Filter to get to it that way.

Could also jump over to the Data tab and you'll see Filter there as well.

As with other data management tools, make sure your data has no empty rows or columns within it.

If you work with the data frequently, that's not a concern.

Simply click on one of the cells within the data. Here's Filter right here. Click it.

And we see filter arrows at the top of each column.

And it's almost self-explanatory, an easy feature to use.

Let's click the drop arrow for Department.

We get an alphabetical list of all the departments.

We might want to view one or two of these at the same time.

Let's unselect them all and begin by maybe clicking one of these.

How about Human Resources? Click that, click OK. We're only seeing those people.

Lower left-hand corner tells us we're seeing nine of them out of 741.

And the blue row numbers we see to the left accentuate the idea we're only viewing some of the rows.

We want to get back to data. Clear, just to the right of Filter, brings back all the data.

Now sometimes, we might be looking at multiple columns at the same time, in other words, multiple filtering.

Let's say we want to look at one of our larger departments, Manufacturing. Click the drop arrow. Unselect them all.

And let's say we want to look at Manufacturing as well as Manufacturing Admin, related departments. Click OK. 158 records.

Now, it's important because we're gonna be considering some of the payroll changes we're gonna be making.

Let's just focus on our full-time people for a while within this department. Click the drop arrow here, unselect all of them, and check Full-Time.

So instead of 158 records, we're now seeing 85.

Now, Department is a text field and when we click the drop arrow here, we see Text Filters.

Equals, Does Not Equal, Begins With, Ends With, we can use this from time to time also.

The compensation column, column J or column H, either one, is a numeric column, and there we see Number Filters.

And so we've got some different choices out here.

Some are the same, but some are also different here, Greater Than, Less Than, or Between. So we might choose Between and a different dialog pops up.

Maybe we only want to focus on those people whose salaries are in the 60,000 to 70,000 range.

So greater than or equal to 60,000. Less than or equal to, well, maybe.

Or how about just less than, another choice.

Less than 70,000. Another way of saying this is all those salaries that begin with six, between 60,000 and 70,000, but not 70,000 exactly, less than. Click OK.

And we're seeing 119 records here.

They're not in any particular order here, so we also have an option with the arrow here to Sort Largest to Smallest.

Now, this will only use the visible rows to move the data up and down. In other words, the rows will be shuffled up and down, but the row numbers to the left, rows five, 11, 12, 15, will stay the same, but different data will be in them.

Same row numbers, but now we've sorted the data, just the visible data, to be in descending order. So sometimes that's necessary as well too.

Back to the Clear button again. There's also a Date column in here.

As we click the drop arrow for Date, recognize Date Filters, a ton of choices here.

Now, if this is historical data, we might want to see data from last week, next month, this month, last month, and so on, not next month, if it's financial data, but if it's prediction data, of course next month.

We see lots of choices in here, quarters, looking forward, looking backward, depending upon the nature of the data.

And don't overlook, there's an arrow down here. All dates in the period.

We can even narrow them down by month.

Learning doesn't stop here. Discover more expert led tutorials at LinkedIn Learning. Start your free trial today, at

Enjoy one month of free access to LinkedIn Learning

Learn from recognized industry experts, and get the business, tech, and creative skills that are most in demand.


  • Get unlimited access to over 4,000 video courses.

  • Receive personal recommendations based on your LinkedIn profile.

  • Stream courses from your computer or mobile device.

  • Take courses for every level – beginner to advanced.

  • Practice while you learn with quizzes, exercise files, and coding windows.

  • Choose a plan for yourself or your entire team.

Connect with an expert
Contact us
Expand your skills
Explore training

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.