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

Keeping client information safe in an age of scams and security threats

A look at the Dirty Dozen tax scams and ways to protect taxpayer information.

TECHNOLOGY Q&A

How to create maps in Excel 2016

Microsoft Excel 2016 has two new mapping capabilities. J. Carlton Collins, CPA, demonstrates how to make masterful 2D and 3D maps in Excel 2016.

QUIZ

News quiz: IRS enforcement, a hot job, and audit value

The IRS’s 2016 Data Book, a “hot job” of particular interest at this time of year, and insight into how executive and audit committees view the insights from financial statement audits received attention recently. See how much you know with this short quiz.