![excel pivot table add subtotal to grandtotal excel pivot table add subtotal to grandtotal](https://www.myexcelonline.com/wp-content/uploads/2017/02/Sort-Largest-to-Smallest-Grand-Totals-01.jpg)
Rename both fields using the Current Field box on the Analyze tab. Because both fields are numeric, they move to the Values area and appear in the pivot table as new columns. Drop this field between Sector and Customer in the Rows area.Ĭheck Profit and Cost in the top of the PivotTable Fields list. Drag the Region tile from the Columns area in the PivotTable Fields list.Select Design, Subtotals, Do Not Show Subtotals.From the Design tab, select Grand Totals, Off for Rows and Columns.To make these changes, follow these steps: This would allow you to add Cost and Profit as new columns in the final report.įinally, you want to convert from a live pivot table to static values. And, depending on your future needs, you might want to move the Region field from the Columns area to the Rows area. You don’t need the Grand Total at the bottom. You don’t need the Sectors totals in rows 7, 14, 18, and so on. Say that you want to convert the pivot table shown in Figure 3.13 to be a regular data set that you can sort, filter, chart, or export to another system. Blank Rows-Inserts or removes blank lines after each group.Ĭase Study: Converting a Pivot Table to Values.Report Layout-Uses the Compact, Outline, or Tabular forms.Grand Totals-Turns the grand totals on or off for rows and columns.Subtotals-Moves subtotals to the top or bottom of each group or turns them off.Layout changes are controlled in the Layout group of the Design tab, as shown in Figure 3.8.
EXCEL PIVOT TABLE ADD SUBTOTAL TO GRANDTOTAL PLUS
If you consider three report layouts, and the ability to show subtotals at the top or bottom, plus choices for blank rows and Repeat All Item Labels, you have 16 different layout possibilities available. The Excel team continues to offer the newer Compact layout as the default report layout, even though I continually hound them about the fact that people who work in the real world would rather use the Tabular report layout, or at least would like to have a choice about which one to use as a default. Learn More Buy Making Report Layout ChangesĮxcel 2016 offers three report layout styles.