A PivotTable column worth repeating


Q: How do I add a percentage-of-total column in a PivotTable in Excel 2010?

A: The October 2011 JofA Technology Q&A column (page 76) included an item titled “Make a Difference With PivotTables,” which explains how to add a calculated column to a PivotTable. That solution could work, but in this particular situation, I’d like to offer an easier option, using Excel’s Show Values As function. To use this feature, in the PivotTable’s Field List, drag the value field name that you want to summarize by amounts and percentages into the Field List’s Values box twice. This action repeats the value columns in the PivotTable, as shown below.

Next, right-click anywhere on the second value column (Sum of Revenue2 in this example) and select Value Field Settings from the popup menu. In the resulting Value Field Settings dialog box, click the Show Value As tab and select % of Column Total from the Show Values As dropdown box, and then click OK.
Your PivotTable will now display the same two columns of data both numerically and as a percentage of total, as pictured below. (In this example, I double-clicked and edited each column’s title to better describe the data.)

Note: This option also can be accessed from the PivotTable Tools tab by selecting the Options tab, Calculations, Show Values As.


Solving the lease accounting challenge

The challenges of the new lease accounting standard have been pervasive to say the least. In this free, independently-written report, you'll learn effective adoption strategies as well as resources for easing the transition to the new standard.


Tackling TCJA changes this tax season

Return preparers must be ready for how the Tax Cuts and Jobs Act has modified many common features of individual and business returns.