Pivot Table Magic ... Again
This page might open your eyes to even more magic that Pivot Tables can perform. What you will see is the creation of what accountants call a Common Size Statement. The page also shows a couple of formatting techniques that will also be useful for anyone who uses PTs for presenting tables and data.
Common Size Statements
Just in case you don't know, a common size statement is one that shows all elements of a column as a percentage of one cell in that column. For example, it might show sales as 100% of itself and then all other rows as percentages of sales. In the example on this page, we are not dealing with a profit statement but with a cost statement: in this case, it is the total cost row that is 100% ... Here is a screenshot of the PT and we are referring to the CS Qx(%) columns: CS = common size.
We don't show the column totals here but see the link at the bottom of this page to download the Excel file for this page.
The example shown here shows four columns: two total cost columns and two common size columns. The total cost columns are ordinary value columns and anyone who is familiar with PTs will already know what to do with them.
To prepare the common size statement, create a new column, along the lines of the Total Q1 and Total Q2 columns: let's discuss the column, CS Q1(%). Start by creating another ordinary Q1 column in the PT and then click once on the Q1 element in the Values section of the Field List and click on Value Field Settings element in the menu that opens. At this point, the appropriate dialogue box is open and you must click on Show Values As tab in the middle of the dialogue box. A little lower down is says Show Values As again: click on the down arrow and select % of Grand Total. You can now change the column heading name, Custom Name to CS Q1(%) and then set the number format: see the next paragraph for number formatting information.
Number Formatting
This instruction applies to the Total Qx columns and to the CS Qx columns although the formatting is a little different between them.
Total Qx Column Formatting: Click on the Values element that you want to custom format and select Value Field Settings: make any other changes you want to make and then click on Number format then click on Custom in the Category section: for Total Qx enter the following under the Type heading:
#,##0;[Red]#,##0;""
CS Qx(%) Column Formatting: enter the following under the Type heading:
#,##0.00%;[Red]#,##0.00%;""
- What happens herefor Total Qx columns is that we tell Excel to present positive numbers as numbers with no decimal points, in red for negative numbers showing no decimal places and any zero value is shown as an empty field.
- What happens herefor CS Qx(%) columns is that we tell Excel to present positive numbers as numbers with no decimal points but in the % format, in red for negative numbers showing no decimal places but in the % format and any zero value is shown as an empty field.
This custom formatting suppresses any zero values and makes the data in every cell and column fully consistent with all other data points as appropriate! Take a look at diagram one, above to see what happens.
Notice: you are using a field more than once
One key thing that you might appreciate is that in the PT we are talking about here is that we are using a field more than once. For example, Total Q1 and CS Q1(%) are both based on the Total Q1 Calculated Field that we prepared for this PT ... we have not discussed calculated fields here! Take a look at a page from my sister site for a discussion on calculated fields!
Well, there you are, another bit of magic from Pivot Tables and from me. Let me have yourfeedback, ideas and suggestions!
Duncan Williamson
25th November 2011
Download the Excel file discussed in this article
