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.


6 key areas of change for accountants and auditors

New accounting standards on revenue recognition, leases, and credit losses present implementation challenges. This independently-written report identifies the hurdles that accounting professionals face and provides tips for overcoming the challenges.


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.