Excel users often struggle to convert raw sales figures into meaningful percentage shares within a PivotTable, frequently resorting to manual formulas outside the table that break the moment the data refreshes. The built-in Show Values As feature solves this instantly. It allows you to display data as a percentage of the grand total, row, or parent category with just a few clicks, keeping the underlying sum intact while changing what the cell displays.
This feature is available across Excel for Windows, Excel for Mac, and Excel for the web. Because each option divides the base number by a different total, the exact same dataset can yield entirely different, yet mathematically correct, insights depending on the question you are trying to answer.
Prerequisites and Sample Data
- Excel for Windows or Mac (Microsoft 365, Excel 2021, or Excel 2024), or Excel for the web.
- A dataset with at least one number column and one or more category columns.
- A basic understanding of how to create your first PivotTable in Excel.
To follow along with the steps below, you can use this sample dataset where the East region sells $20,000 and the West region sells $30,000, creating a grand total of $50,000:
| Region | Salesperson | Quarter | Sales |
|---|---|---|---|
| East | Ana | Q1 | 6000 |
| East | Ana | Q2 | 4000 |
| East | Ben | Q1 | 4000 |
| East | Ben | Q2 | 6000 |
| West | Cara | Q1 | 10000 |
| West | Cara | Q2 | 10000 |
| West | Dev | Q1 | 5000 |
| West | Dev | Q2 | 5000 |
How to Configure Percentage Views
- Build the initial PivotTable by clicking inside your data, navigating to Insert, and selecting PivotTable. This initializes the table so you can drag Region and Salesperson to the Rows area, and Sales to the Values area.
- Right-click any number in the Sum of Sales column and hover over Show Values As. This opens the calculation submenu directly, bypassing the need to navigate through the ribbon menus.
- Select % of Grand Total to see each item's share of the entire company. This divides every individual row by the $50,000 grand total, revealing that the West region accounts for 60% of all sales.
- Choose % of Parent Row Total when analyzing nested fields like Salesperson under Region. This divides the salesperson's revenue by their specific region's subtotal, showing that Cara brought in 66.67% of the West region's sales.
- Click % Of... to compare performance against a specific benchmark. This allows you to set the Base field to Region and the Base item to East, revealing that the West region sold 150% of what the East region sold.
- Select % Running Total In... after dragging Quarter into the Columns area. This tracks cumulative progress over time, showing 50% completion in Q1 and 100% by the end of Q2.
- Drag the Sales field into the Values area a second time to keep both formats. This creates a duplicate column, allowing you to display the raw dollar amounts in one column while applying the percentage view to the other.
- Open Value Field Settings, click Number Format, and choose Percentage. This ensures the data displays cleanly as 40% rather than a raw decimal like 0.4, and allows you to control decimal places.
Troubleshooting Common Calculation Errors
- Refresh the Data: If the percentages look stale after updating the source data, right-click the table and choose Refresh, or press Alt + F5 on Windows.
- Target the Right Cell: The menu will not appear if you right-click a row label (like "East"). You must right-click a numerical value inside the Values area.
- Clear Hidden Filters: If your percentages do not add up to 100%, check for unchecked items in the label filters. Hidden items change the total calculation base.
- Fix #N/A Errors: This occurs when using the "% Of" feature if the base item does not exist for a specific row. Switch to "% of Grand Total" to resolve it.
- Correct Count vs. Sum: If Excel reads any sales values as text, it will count them instead of summing them. Fix the source data formatting, then change the summary function back to Sum.
The End of Fragile Spreadsheet Formulas
Relying on manual formulas placed adjacent to a PivotTable is one of the most common structural mistakes in corporate reporting. When new data is added and the PivotTable expands, those hardcoded formulas either break, point to the wrong cells, or get overwritten entirely. By utilizing the native calculation engine, the percentages become dynamically bound to the data model.
The real analytical power here lies in the contextual awareness of the parent-child relationships. The ability to instantly pivot Cara's performance from representing 40% of the total company to 66.67% of her specific regional team - without writing a single VLOOKUP or SUMIFS - drastically reduces reporting time. For financial analysts and managers, mastering these nested calculations is the difference between building a static spreadsheet and creating a resilient, interactive dashboard.
