Excel Pivot Tables are sometimes a mystery to users of Excel. In this example I am going to show you how to add a percentage (%) column into a pivot table. This column will calculate that rows percentage of the grand total.

pivot table data
Raw Pivot Table Data

We will start with some sample raw data. In the worksheet above we have order information by a few salesman. We are going to start by creating a simple pivot table summarizing total sales by salesman. To create the pivot table click into any cell in the table (A1 for example). Next click the INSERT menu at the top of Excel. Next, click the PIVOT TABLE icon. This will bring up the below popup box. We are going to select to place the pivot table on the existing worksheet to the right of the table data.

create pivot table on existing sheet

From the Pivot Table Fields pane on the right hand side of Excel choose the checkbox next to Salesman and Total Sale. Next, confirm that the Salesman field is in the Rows box and that “Sum of Total Sale” is in the Values box in the bottom right. Your table should look like the below screen shot.

select pivot table fields

Format the pivot table column as currency

Just to keep the numbers looking neat and clean I am going to adjust the field properties to format the Total Sale column as Currency. To format the Total Sale column you can right click the column heading in the pivot table and choose Value Field Settings from the menu.

Value Field Settings
Right click the column heading and choose Value Field Settings

On the Value Field Settings box click the Number Format button in the bottom left of the screen as shown below.

Number Format

This will bring up the Format Cells box. You should select the Currency option to display this column of data using the currency format.

pivot table column format

When formatted your pivot table will now look like the below screen shot. Although this is not necessary, I like to get in the habit of making the data look as easily readable as possible.

formatted pivot table

Add the Percentage Column

To add a column to our pivot table that shows the percentages of total sales for each salesman we need to click and drag a second copy of the Total Sale column from the field list down to the Values section. It will look like the below screen shot.

Add second total field to Values
Drag and drop the Total Sale field to the Values again

Next, we need to adjust the display of this second Sum of Total Sale column to show the percentage of grand total rather than another sum of the sales numbers. To do this, click the small triangle next to the field name in the Values box to show a menu. Choose the Value Field Settings to show the popup box where we will adjust the display of this field. Select the Show Values As tab. Next select the “Show values as” drop down box and choose “% of Grand Total” as shown below. After that you can click the OK button. The field/column will now be shown as percentages.

Show Values As
Select Percentage of Grand Total

If all of the above steps were followed you should now have a Excel Pivot Table that has Salesman, Total Sales and Percentages as shown below.

Pivot Table final result
Final Pivot Table with Percentage Column

Please post any questions or comments below. For more of my Excel Tips and Tricks.