A PivotTable column worth repeating

BY J. CARLTON COLLINS

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.

SPONSORED REPORT

Taking stock of artificial intelligence

Artificial intelligence is either the greatest thing to ever happen to human work or the dread of our existence. This independently written report explores how AI will reshape the workplace and how analytically minded individuals can stand out.

PODCAST

How tax reform will impact individual taxpayers

Amy Wang, a CPA who is a senior technical manager for tax advocacy at the AICPA, answers to some of the most common questions on how the new tax reform law will impact individual taxpayers.