OmniDocHow to Apply Filters in a Pivot Table
Learn how to apply report and field filters in a pivot table, filter just one column, and keep those filters after you refresh the data.
If you have built a pivot table and only want to see some of its rows, columns, or totals, you need a filter. People often search for "apply filter in pivot table" because the filter controls are not where they expect them to be. The right method depends on whether you want to limit the whole report, one column, or rows that meet a number rule.
This guide covers the three filter types most people use: report filters, field filters on rows or columns, and value filters. It also explains why filters can change after you update the source data, and how to keep them in place. Formatting problems that appear while you filter, such as shifted fonts or lost cell styles, are a separate issue and are not covered here.
How do you apply a filter in a pivot table? The short answer
Drag the field into the Filters area to filter the entire table, or open the dropdown on a row or column field to choose which of its items appear.
| Your situation | What to do | Why |
|---|---|---|
| You want to limit the whole report to one region or year | Place the field in the Filters area and pick an item | One selection changes every part of the table |
| You want to hide some rows or columns only | Open the dropdown on that row or column field and tick the items to keep | Other fields keep their full view |
| You want only one column to show a subset | Place the field in the Columns area and filter it there | Filtering a row field would hide rows instead of columns |
| You want to keep rows above or below a number | Open the row field dropdown and use a value filter | The filter checks the totals, not the labels |
Before you start
- Clean up the source data. Use one header row, no blank rows inside the data, and no merged cells in the header. Blank rows create a "(blank)" item in the dropdowns, and merged headers can give fields confusing names.
- Check that the data is in a spreadsheet. A pivot table needs a range of cells. If your table is in a PDF, run OCR first if the pages are scanned, then export the table to Excel format with the PDF editor in the OmniDoc desktop client at download.omnidoc.top. Open the exported .xlsx file and confirm that the rows and columns landed in the right cells.
- Decide which scope you need. Filtering the whole table, one column, or rows by value calls for different controls. Writing down the goal first saves you from undoing filters later.
- Confirm the source range. A pivot table only reads the rows inside the range it was built from. If you add rows below the data, the refresh may miss them until you widen the range.
- Save a copy of the file. Filters are easy to clear, but a copy lets you compare before and after if a layout change causes confusion.
Adding report and field filters to a pivot table, step by step
- Select any cell inside the pivot table. Filter controls and the field list only appear when the pivot table is active.
- Find the field you want to filter by in the field list. Field names come from the header row of your source data, so a clear header makes this step easier.
- Drag the field into the Filters area to create a report filter. A single dropdown appears above the table, and choosing an item limits the entire table to it.
- Choose several items when you need more than one. Open the report filter dropdown and tick the option that lets you select multiple items. The table redraws with only the items you ticked.
- For one column only, place the field in the Columns area and use its dropdown. The dropdown changes only that axis, so the row labels and other columns stay as they were.
- Use label or value filters to set rules. Label filters match text, such as items that begin with a certain word. Value filters match numbers, such as rows whose total is above a threshold you set.
- Reset a filter by reopening its dropdown and showing all items again. Each dropdown should then include every item in the source, and the table returns to its full view.
- After you change the source data, refresh the pivot table and reopen each dropdown. A refresh keeps your selections for items that still exist. New items may appear unfiltered, so tick or clear them as needed.
Check the result
Once the filters are set, compare the table against your source data before you share or export it.
| Check | How to see it | If it's wrong |
|---|---|---|
| Filter scope | Read the filter labels above the table and the dropdown on each row or column field | If rows disappeared when you wanted columns, the field may sit in the Rows area. Move it to Columns. |
| Totals | Add up the visible items in the source and compare with the grand total | If the numbers do not match, check value filters and any items you hid. |
| Blank items | Open each dropdown and look for a "(blank)" item | Fill or delete the empty cells in the source, then refresh. |
| Items after a refresh | Reopen each dropdown after updating the data | Tick new items that should appear, or clear and reapply your filter. |
| Source range | Compare the last row in the pivot with the last row of your data | Widen the source range so the new rows are included, then refresh again. |
Filters change only what the pivot table displays. They do not delete or move rows in the source data, so you can always reset them and start over.
📊 At a glance
| Method | What it limits | Good fit when |
|---|---|---|
| Report filter | The entire pivot table | You want one selection, such as a single year, to apply everywhere |
| Row or column field dropdown | Only that axis | You want to hide specific categories without touching the rest |
| Label filter | Rows or columns whose text matches a rule | You need items that share a prefix or a pattern |
| Value filter | Rows or columns whose totals meet a rule | You want to see only high or low performers |
❓ FAQ
1. Can I filter a pivot table by more than one field at once?
Yes. Place several fields in the Filters area, and each one narrows the table. You can also combine a report filter with dropdown filters on rows or columns.
2. Why does my filtered total not match the source total?
The grand total usually reflects only the visible items. Add up the items you can see in the source and compare them with the table, and check for value filters or hidden items that might explain the gap.
3. Do filters change my source data?
No. Filters only control what the pivot table shows. Your rows and cells stay exactly where they were.
4. Why does my spreadsheet show a "(blank)" item in the dropdown?
Your source contains empty cells in the field you filtered. Fill those cells with a label, or delete the empty rows, and then refresh the pivot table.
📝 Summary
To apply a filter in a pivot table, use the report filter for the whole table, a row or column dropdown for one axis, and value filters for number-based rules. Keep your source data clean, and reopen the dropdowns after each refresh to confirm that the filters still show what you intended.
