We just told the PivotTable to sort ascending by region, and within each region, by rep. Next, we select any rep cell in the report and then use the Rep drop-down and select Ascending. We select any region cell in the report, and then use the Rep drop-down and select Ascending. Since the default sort of a PivotTable is Manual, and we want the sort to be ascending, even if new regions or reps appear in the data source in future periods, we need to explicitly define the sort order. PivotTable Tools > Analyze > +/- ButtonsĪh, our report is looking a little better.Fortunately, this is easy to do with the following ribbon icon: We need to remove the little show/hide, expand/collapse, +/- buttons. Lastly, we change the Row Labels header to Rep, and our updated report is looking a little better. We right-click any number in the column and select Number Format…number, no decimals, comma. We type the desired label, Average, into the header cell. So, we right-click any value cell in that column and select Summarize Values By…Average. The next column, Sum of Amount3, needs to reflect the average sales per rep. We type our desired column label Count into the column header cell. So, we right-click any value cell in that column and select Summarize Values By…Count. The next column, Sum of Amount2, needs to reflect the count of the number of transactions.
![how to remove subtotals from pivot how to remove subtotals from pivot](https://www.techonthenet.com/excel/pivottbls/images/stotal_row2007_001.png)
To control the formatting, we right-click any value cell in the column and select Number Format (not Format Cells). We simply type our desired column header Sales into the cell. The values are fine but we need to change the report label.
![how to remove subtotals from pivot how to remove subtotals from pivot](https://i.stack.imgur.com/oC7TD.png)
The first amount column, currently named Sum of Amount, needs to reflect the total sales amount. Next, we need to fix the numbers so that they reflect the desired values. We then click-and-drag the Amount field into the Values area three times.Īt this point, our little PivotTable looks like this. We then insert the Region, Rep, and Rep Name fields into the Rows area. We select any cell in the data table, and then select Insert > PivotTable. Let’s begin by inserting the fields into the correct report area. Now, let’s walk through the detailed steps to create it. If there are new rows, the number formats need to be retained as well. If a new region such as East appears in the data next period, when we refresh the PivotTable we need the new region to appear with the custom region formatting (big and bold font) and it needs to appear in the correct alphabetical order. The report is sorted ascending by region, and within each region, by rep initials.Īll of this formatting needs to remain intact each time the PivotTable is refreshed, even if new regions appear in the data source. Region subtotals are presented under the region data. The sales, count, and average columns have numeric formatting. Between region groups is a blank worksheet row. Also notice the region group headers appear on their own rows with a non-standard font size (big and bold). First, the region groups, such as Midwest and Northeast, are in the same column as the reps, but the rep names appear in their own column. There are a few formatting points to note about the report. Our objective is to create the following PivotTable report. STEP 6: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the new data.In this post, we’ll check out a few PivotTable formatting techniques that help get our report looking just right. You can see March, June, and September are now removed. Essentially it means removing the first row of every 3 rows. This will remove starting from the first row, then the pattern is that row will be removed, then skip the next 2 rows, then do the same pattern again. Let us say we want to remove the following months: 3 (March), 6 (June) and 9 (September). STEP 5: Go to Home > Reduce Rows > Remove Rows > Remove Alternate Rows You can see November and December are now removed. Let us try to remove the bottom two rows.
![how to remove subtotals from pivot how to remove subtotals from pivot](https://www.dummies.com/wp-content/uploads/423170.image1.jpg)
STEP 4: Go to Home > Reduce Rows > Remove Rows > Remove Bottom Rows
![how to remove subtotals from pivot how to remove subtotals from pivot](https://4.bp.blogspot.com/-i5M_oX_b5FI/WbfzZ71ZU4I/AAAAAAAABW4/6-H3YkeYEH8yAmKSchxKSUzI1vZNashnwCLcBGAs/s1600/Remove%2Bsub%2Btotal%2Bexcel%2Bpivot.jpg)
You can see January and February are now removed. STEP 3: Go to Home > Reduce Rows > Remove Rows > Remove Top Rows There are a lot of options! Let us see these 3 in action: If you go to Home > Reduce Rows > Remove Rows STEP 2:This will open up the Power Query Editor. Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010) Let us first prepare to use this data in Power Query. STEP 1: Our sample data contains the Sales numbers for each month.