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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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: