How to add percentage of grand total/subtotal column in an Excel Pivot Table?
When creating a pivot table in Excel, the grand total column/row will be added automatically. But how to add another percentage of a grand total column or subtotal column in the pivot table? Below solution will ease your work.
Please follow below steps to create a pivot table with the percentage of a grand total column or subtotal column in Excel.
1. Select the source data, and click Insert > PivotTable.
2. In the Create PivotTable dialog box, please specify a destination range to place the pivot table, and click the OK button. See screenshot:
3. Now go to the PivotTable Fields pane, drag Shop field and Items field to the Rows section, and then drag the Sales field to the Values section twice. See screenshot:
4. In the Values section, click the second Sales field, and then select Value Field Settings from the drop-down list. See screenshot:
5. In the Value Field Settings dialog box, select % of Grand Total from the Show value as drop-down list on the Show Values As tab, rename the filed as you need in the Custom Name box and then click the OK button. See screenshot:
Note: If you need to add a percent of the subtotal column in the pivot table, please select % of Parent Row Total from the Show values as drop-down list.
Now you return to the pivot table, and you will see the percent of Grand Total column in the pivot table. See screenshot:
Note: If you selected % of Parent Row Total from the Show values as drop-down list in above Step 5, you will get the percent of the Subtotal column. See screenshot:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!