OmniDocShow Top 10 or Top 5 Values in a Pivot Table
Learn how to limit a pivot table to its top 5, 10 or 20 items using a value filter, with steps, checks and fixes for common problems.
If you need only the biggest sales, the busiest regions or the highest-scoring students, a pivot table with every row is hard to read. You want a short list that ranks the top 10 pivot table results and leaves out the rest. Sorting alone does not do this cleanly, because the pivot table still counts every item behind the scenes and you have to scroll past the ones you do not need.
The fix is a value filter. You tell the pivot table to keep only the top N items, ranked by a number field you choose, and it recalculates the list for you. The same method gives you top 5, top 10 or top 20 by changing one number. This article walks through the setup, the checks to run afterward and the common problems that make the count look wrong.
How do I show only the top 10 in a pivot table? The short answer
Add the item name as a row field, add a number field as the value, and apply a Top 10 value filter on the row field that ranks items by the sum of that number field.
| Your situation | What to do | Why |
|---|---|---|
| You want the largest items by total | Apply a Top N value filter ranked by the sum of your number field, with N set to 10 | The filter runs after totals are calculated, so the ranking uses real totals |
| You want the smallest items | Set the filter direction to bottom and N to 5 | It works the same way as the top filter, in reverse |
| You need the top items but also want the full list | Keep a copy of the full pivot table on another sheet, or filter a copy | The filter hides rows from view, and you may need the complete data later |
| Your table lives in a PDF | Export it to a spreadsheet file first, then build the pivot table | Pivot tables need a spreadsheet source, not a fixed page layout |
Before you start
- Clean up your source table. Use one header row, one record per row, and no blank rows or merged cells inside the range. Blank rows can split the data or leave gaps in the list.
- Confirm the number column holds real numbers. Values stored as text may not add up or rank correctly. Check a few cells for left-aligned numbers or stray spaces.
- Decide what "top" means. The filter ranks by one value field, such as total revenue or total units. Pick the measure first, because the top 10 by units can differ from the top 10 by revenue.
- Choose the item field. This is the field whose names you want to see, such as product, region or student. Each name should appear in a consistent spelling so the totals group correctly.
- Get the data out of a PDF if needed. If the table is in a PDF, check whether the file is digital or scanned by trying to select a line of text. For a scanned file, run OCR first with the PDF editor in the OmniDoc desktop client (download.omnidoc.top), then export the table to a spreadsheet file (.xlsx). Check the exported numbers against the PDF before you build the pivot table, because OCR can misread digits.
Building a top 10 pivot table, step by step
- Select any cell inside your data. Selecting one cell lets the spreadsheet program detect the full range. Make sure the range includes the header row.
- Create a pivot table on a new sheet. Use the pivot table tool in your spreadsheet program and place the output on a separate sheet. This keeps the summary apart from the source data.
- Drag the item field into the row area. Each unique item name becomes one row in the pivot table. Check that every expected name appears once.
- Drag the number field into the values area. Confirm that it is set to sum, not count. A count ranks items by how many records they have, which is a different answer.
- Open the filter on the row field and choose a value filter. Find the option that filters by value, select the top setting, enter 10 as the number, and pick the number field's sum as the ranking measure.
- Review the result. The row list should now show only the ten items with the largest totals, in ranked order. If the list looks unsorted, sort the row field by the sum in descending order.
- Change N for other cuts. To get the top 5 or top 20, change the number in the same filter. You do not need to rebuild the pivot table.
- Refresh after the data changes. When you add or edit rows in the source table, refresh the pivot table so totals and rankings update. If new rows fall outside the original range, extend the source range before refreshing.
Check the result
| Check | How to see it | If it's wrong |
|---|---|---|
| Row count | Count the visible item rows | If the count is not N, look for ties at the cutoff or duplicate item names |
| Ranking | Compare the first rows with the same items sorted by total in your source | If the order differs, the filter may be ranking by the wrong field |
| Spot-check values | Pick one item and add its rows in the source by hand | If the sum differs, a number may be stored as text or a row may be duplicated |
| Grand total | See whether the total row covers all items or only the items shown | If you need the full total, place it in a separate cell calculated from the source |
| After a refresh | Change one source number, then refresh | If the pivot table does not update, refresh again or extend the source range |
📊 At a glance
| Approach | Keeps the full pivot table | Good fit for |
|---|---|---|
| Top N value filter | Yes, the filter can be removed or changed at any time | Ranked lists that update as data changes |
| Sort descending and read the first rows | Yes, all rows stay visible | Quick checks when you do not need a fixed count |
| Helper rank column in the source data | Yes, the rank is stored as a column | Cases where you need the rank for other calculations |
❓ FAQ
1. What is the difference between a top 5 and a top 10 pivot table?
Both use the same value filter. The only change is the number: 5 keeps five items and 10 keeps ten, so the ranking logic does not change.
2. How do I show the top 20 in a pivot table?
Apply the same value filter and enter 20 as the number. If the list has fewer than 20 items, the pivot table will show all of them.
3. Can I show the bottom 5 instead of the top 5?
Yes. Set the filter direction to bottom and keep the same ranking field. This shows the five items with the smallest totals.
4. Why does my top 10 show a different number of rows?
Check for ties at the cutoff, where two items have the same total, and for blank or duplicated item names in the row field. Fix the source data first, then refresh the pivot table.
📝 Summary
A value filter lets you show a top 10 pivot table without deleting or hiding rows by hand. Set the number, choose the sum field as the ranking measure and check the row count and totals. Change N to 5 or 20 whenever you need a different cut.
