Stop writing percentage formulas in Excel—use this instead

Calculating percentages in Excel often means wrestling with absolute cell references and division symbols. But why do the work when a built-in tool can do it for you? By using the PivotTable Show Values As option, you can calculate complex ratios in seconds without typing a single formula.

The problem with manual percentage formulas

And why the Show Values As menu is the way forward

Formulas are the backbone of Excel, but they’re also the easiest way to clutter a clean spreadsheet.

Adding separate percentage columns creates visual noise that distracts from your data. Even with Excel tables and structured references, you still have to write and maintain the logic—often managing a Grand Total value that can return incorrect results, especially when filters are applied. Manual cell references are also fragile. If your structure changes, a formula like =A2/$B$20 may point to the wrong cell or return a #DIV/0! error.

PivotTables largely bypass these problems using Show Values As, a built-in feature that transforms raw numbers into ratios without adding a single extra column to your source data. Because this analysis lives in a separate worksheet, your original data remains pristine. Best of all, you aren’t relying on fragile cell-to-cell formulas that can break if a row is deleted or a range is sorted—the logic is built into the PivotTable itself.

Related

My Go-To Excel Trick for Analyzing Big Data Sets Fast

PivotTables are an underrated tool in Microsoft Excel.

The sample dataset

A consistent foundation for our examples

To demonstrate the Show Values As tool, I’ll use the following Excel table (T_SalesData) with dates, regions, sales reps, product categories, and revenues. Each of the three examples below begins with a fresh PivotTable, so you can see how different Show Values As tools can transform the same raw information into unique insights.

Using an Excel table (Ctrl+T) as the data source is essential for a dynamic workflow. Unlike a standard range, it automatically expands when you add new rows. Because your PivotTable is linked to the table’s name rather than fixed cell coordinates, it always includes new rows in its source range once refreshed.

Example 1: Calculating contribution and distribution

Visualizing how individual parts impact the whole

Show Values As lets you see how each sub-category contributes to the total without building a single formula.

The scenario: You want to know the dollar amount for each area, along with its percentage of total company revenue.

First, you need to create a PivotTable from your table:

1. Click anywhere inside the Excel table, and in the Insert tab, click PivotTable.
2. Ensure your table name (T_SalesData) is in the Table/Range field, and New Worksheet is checked, then click OK.
3. In the PivotTable Fields pane, drag Region into the Rows area to create a Row Labels column in your PivotTable.
4. Drag Revenue into the Values area twice to create two identical columns.

You’re now ready to calculate the proportions:

1. Right-click any number in the Sum of Revenue2 column, and select Show Values As > % of Grand Total.
2. Double-click the header of this column to rename it Percent of Total.

Now you can see the dollar amounts side by side with their percentage counterparts. This shows which regions drive your business without a single division symbol cluttering your source sheet.

If you later add a new sales record to your source table, simply right-click your PivotTable and select Refresh. Excel will automatically scan the expanded table and update your totals and proportions.

Example 2: Comparing trends and performance over time

Tracking growth and variance without subtraction formulas

Instead of writing a “new minus old divided by old” formula in Excel, you can use built-in logic to calculate variance automatically.

The scenario: You want to see month-over-month sales growth to determine whether your revenue is trending upward or downward.

Assuming you already have your PivotTable set up from Example 1 above, you first need to clear the existing fields by dragging them away from the PivotTable Fields pane.

Next, add your new fields:

1. Place Date into the Rows area. Excel 2016 and later (including Microsoft 365) automatically group dates into months in the PivotTable, creating fields like Months, Days, and Years in the field list. If you’re using an older version, right-click the date and select Group > Months.
2. Drag Revenue into the Values area twice.

Now, you’re ready to apply the variance calculation:

1. Right-click a value in the Sum of Revenue2 column.
2. Head to Show Values As > % Difference From….
3. In the pop-up menu, set the Base Field to the grouped month field (such as Months (Date)) and the Base Item to (previous). Then, click OK.
4. Double-click the header to rename it MoM Growth %.

Your PivotTable now clearly shows your growth trends alongside the dollar amounts.

You’ll notice that the first row of your MoM Growth % column is blank. Don’t worry—your PivotTable isn’t broken. Since you selected (previous) as your Base Item, Excel is looking for data from the month prior to your first entry. However, because there is no prior month, Excel leaves the first cell empty.

Related

Formatting Numbers as Percentages in Excel Is Frustrating—But Here’s an Easy Fix

Turn Excel numbers into percentages in just a few seconds.

Example 3: Analyzing nested distribution with parent totals

Seeing internal shares within a specific category

Excel’s Show Values As is ideal for nested data, as it calculates percentages based on parent groups instead of the entire dataset.

The scenario: You want to see each sales rep’s revenue and what percentage of their parent category total they account for within each category group.

Here’s how to do this after resetting your PivotTable by dragging the fields away from the Rows and Values areas:

1. Drag Category into the Rows area, then place Sales Rep beneath it. Then, move Revenue into the Values area twice.
2. Right-click any number in the Sum of Revenue2 column and select Show Values As > % of Parent Row Total.
3. Rename your percentage column header % of Category.

You can now see exactly how performance is distributed within each product line. For example, Miller accounts for 46.15% of the Hardware category’s revenue, even though his total revenue in this category is just a fraction of the overall company sales. This provides a much more nuanced view of performance than a simple flat list ever could.

Calculating percentage proportions without manual formulas keeps your workbooks clean and your results reliable. This automated approach ensures that as your data grows, your analysis remains accurate and easy to manage. The right-click menu includes other Show Values As options, but the three demonstrated here are the most useful for analysis. To complement this formula-free setup, make your reports even more interactive by adding timeline filters and slicers to your PivotTable, resulting in a truly dynamic dashboard.

OS

Windows, macOS, iPhone, iPad, Android

Free trial

1 month

Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.