Excel 2024: Find the True Top Five in a Pivot Table

May 10, 2024 - by Bill Jelen

Pivot tables offer a Top 10 filter. It is cool. It is flexible. But I hate it, and I will tell you why.

Here is a pivot table that shows revenue by customer. The revenue total is $6.7 million. Notice that the largest customer, More4Apps, is 11.46% of the total revenue.

What if my manager has the attention span of a goldfish and wants to see only the top five customers? To start, open the dropdown in A3 and select Value Filters, Top 10.

The super-flexible Top 10 Filter dialog allows Top/Bottom. It can do 10, 5, or any other number. You can ask for the top five items, top 80%, or enough customers to get to $5 million.

But here is the problem: The resulting report shows five customers and the total from those customers instead of the totals from everyone. More4Apps, who was previously 11% of the total is 23% of the new total. I have two different solutions to this problem.

More Reading

Excel 2024: Change the Calculation in a Pivot Table »

May 8, 2024 - by Bill Jelen

Pivot tables offer a myriad of calculations in the Field Settings dialog box. Here is a faster way to change a calculation.

Excel 2024: Create a Year-over-Year Report in a Pivot Table »

May 6, 2024 - by Bill Jelen

Let's say you have two years worth of detail records. Each record has a daily date. When you build a pivot table from this report, you will have hundreds of rows of daily dates in the pivot table.

Excel 2024: Create Your First Pivot Table »

May 2, 2024 - by Bill Jelen

Pivot tables let you summarize tabular data to a one-page summary in a few clicks. Start with a data set that has headings in row 1. It should have no blank rows, blank columns, blank headings or merged cells.

Guerrilla Data Analysis Using Microsoft Excel - 3rd Edition

May 2022

Two of the leading Excel channels on YouTube join forces to combat bad data. This book includes step-by-step examples and case studies that teach users the many power tricks for analyzing data in Excel. These are tips honed by Bill Jelen, "MrExcel," and Oz do Soleil during their careers run as financial analysts.

Master Your Data with Power Query in Excel and Power BI

August 2021

Despite the moniker "data monkey," we information workers are often more like data magicians. Our data seldom enters our world in a ready-to-consume format; it can take hours of cleaning, filtering, and reshaping to get things ready to go. Power Query will make this process faster the first time and reduce it to a single button click every subsequent time.

MrExcel 2021 - Unmasking Excel

February 2021

This is a 5th edition of MrExcel XL. Updates for 2021 include: LAMBDA, LET, Power Query Fuzzy Match, Sort & Filter in Sheet View, Cut-out people, Save object as image, STOCKHISTORY, Wolfram Alpha Data Types, Custom Data Types from Power Query, Weather data types, bilingual spreadsheets, Performance improvements, Unhide multiple worksheets, Action pen, Collapsible task panes, LET function to re-use calculations, store formulas using LAMBDA, Recursive LAMBDA, Branching LAMBDA, Lambda to return a picture, Excel function quick reference.

MrExcel 2022 Boosting Excel

April 2022

The 6th Edition of MrExcel XL, updated with new functions released for Microsoft 365.

see more products »

Excel 2024: Find the True Top Five in a Pivot Table

More Reading

Featured Products