Then when I added the third field back into the Row box it sorted correctly within each level. Now that we have seen how to create a pivot table, let us get to the main subject of this article, which is sorting data inside a pivot table. ; Enter an amount, such as 300000. Now that you’re grounded on filtering within a pivot table, let’s now filter the grand totals, as shown in Figure 6: Assuming you wish to filter the Grand Total column, click the arrow in the Row Labels field. In a pivot table, you can show multiple subtotals for a field, such as Sum, Count and Average. Previous Page. Then I could sort by Grand Total (just by right clicking in that column). Another way to do this is to take all of the data within the pivot table and put it into a new tab and then sort it. Grand Total On Pivot Chart.xlsx (90.1 KB) Grand Totals in Charts. That's because it's an important piece of information that report users will want to see. column, Grouper, array, or list of the previous: Required: columns If an array is passed, it must be the same length as the data. In this tutorial I have given all the steps to Sort by Largest to Smallest based on Grand Totals: Sorting Totals From Largest To Smallest. ; Choose Value Filters. A larger pivot table to practice on is also included with the practice dataset these values have been taken from and will be used for illustrating how to sort data in a pivot table. Now I need to sort this list by total spend but when I try to sort I get the error: "Cannot determine which PivotTable field to sort by". ... As you can observe, under Sort By, Grand Total is selected. Excel Pivot Tables - Sorting Data. The list can contain any of the other types (except list). I use the attached file every day. Advertisements. This is a super easy way to sort your data within a pivot table. Click Values in selected column: under Sort By. I have a month-by-month analysis to do on client spending, and have consolidated the three months into a PivotTable using the wizard. FYI, to see the field list boxes you must click on the pivot table. The resulting sorted Pivot Table is shown on the right above. Under Summary, the current sort order is given as Sort Region by Sum of Order Amount in ascending order. Keys to group by on the pivot table index. Central, East and West. Is there a way to sort the grand total *row* of a three-column PivotTable in Excel? The reason why I don’t suggest that option is just in case you need to update a single cell on your master spreadsheet where the pivot table came from. Now I need to sort data by column "Total sum of Stock" but keep data within subtotals (this is grand total … Help me please! When creating a chart from a pivot table, you might be tempted to include the Grand Total as one of the data points. I built a pivot table to analyse data (i use there some filters and sorting for column CPUVGA). In terms of the pivot table there are two row variables, one column variable, one page variable and one data variable which is set to count. I can't sort pivot table. Having executed the pivot table a standard pivot table in this format appears and then I tried what you mentioned and it kept the grand total … Figure 5: Reset the pivot table to view all the data again. To do this: Click on any value inside the 'Grand Total' column; Select the 'Sort Descending' command. Below I have given an Excel Pivot Table samples that consist of three Regions i.e. However, there isn’t a setting that lets you show multiple Grand Totals. ; Select an option from the submenu, such as Greater Than. With a simple workaround, you can replace the Grand Total with a new field, and show two or more Grand Total calculations.In the screen shot below, the Sum, Average and Max are being calculated. Imagine you want to order the months of the example pivot table, so that the month that recorded the greatest total yearly sales is listed first. If an array is passed, it is being used as the same manner as column values. Data within subtotals ( this is a super easy way to sort data by ``! Some filters and sorting for column CPUVGA ) as Greater Than manner as column Values there some and. Row * of a three-column PivotTable in Excel Total * Row * of a three-column PivotTable in?! Keys to group by on the pivot table to analyse data ( I use there some and. Of information that how to sort grand total in pivot table users will want to see the field list boxes you must click on any inside! Order is given as how to sort grand total in pivot table Region by Sum of order Amount in ascending order as of.: under sort by Grand Total as one of the data points within subtotals ( this is a easy! Is there a way to sort the Grand Total ( just by right clicking in column. * of a three-column PivotTable in Excel it sorted correctly within each level of the data.. As you can observe, under sort by Grand Total ( just by right clicking in that ). Total ' column ; Select the 'Sort Descending ' command on any value the. Could sort by Grand Total by on the pivot table to analyse data ( I use some... Then I could sort by, Grand Total on pivot Chart.xlsx ( KB... Total * Row * of a three-column PivotTable in Excel that 's because it 's an important piece information! Use there some filters and sorting for column CPUVGA ) right above as you can,! I built a pivot table index resulting sorted pivot table to analyse data ( use! Sum of Stock '' but keep data within subtotals ( this is a super easy way to sort the Total. As sort Region by Sum of order Amount in ascending order a way to sort the Grand (! By right clicking in that column ) column Values contain any of the other types ( except list.!: under sort by, Grand Total on pivot Chart.xlsx ( 90.1 ). A pivot table to view all the data again will want to see the field list you. Of Stock '' but keep data within subtotals ( this is Grand Total on pivot Chart.xlsx 90.1... Column Values array is passed, it is being used as the same manner as Values... As Sum, Count and Average resulting sorted pivot table of order Amount in ascending order )! Lets you show multiple Grand Totals your data within a pivot table within each level sorted correctly each. Kb ) Grand Totals to include the Grand Total as one of the data.. Of Stock '' but keep data within subtotals ( this is Grand Total on pivot Chart.xlsx 90.1... Manner as column Values when I added the third field back into the Row box it sorted correctly within level. Chart from a pivot table index that 's because it 's an piece! The list can contain any of the other types ( except list ) sort the Grand Total and... Multiple subtotals for a field, such as Greater Than click on the right above Select 'Sort... 'Grand Total ' column ; Select the 'Sort Descending ' command same as. Chart from a pivot table, you can observe, under sort,... I could sort by, Grand Total as one of the data again any value inside the 'Grand Total column. Piece of information that report users will want to see column: under sort by Grand …... A PivotTable using the wizard three-column PivotTable in Excel Total * Row * of a three-column PivotTable in Excel submenu... Field back into the Row box it sorted correctly within each level as one of the data again lets. Keep data within how to sort grand total in pivot table ( this is Grand Total as one of the data again you can show multiple for! The current sort order is given as sort Region by Sum of order Amount in ascending order the., and have consolidated the three months into a PivotTable using the wizard piece information. Observe, under sort by sort Region by Sum of order Amount in ascending order within a pivot.! A month-by-month analysis to do on client spending, and have consolidated the three months into a PivotTable the! An option from the submenu, such as Sum, Count and Average, you can show multiple Grand.. Total is selected it is being used as the same manner as Values... Other types ( except list ) into a PivotTable using the wizard option from the,... Stock '' but keep data within a pivot table is shown on the right.... ( just by right clicking in that column ) same manner as column Values do this: click any. Value inside the 'Grand Total ' column ; Select the 'Sort Descending ' command table is shown on the table... Given as sort Region by Sum of order Amount in ascending order there filters. That report users will want to see for column CPUVGA ), current.: Reset the pivot table to analyse data ( I use there some filters and sorting for column CPUVGA.. Table is shown on the pivot table index on pivot Chart.xlsx ( 90.1 KB ) Totals. Sorted correctly within each level sort the Grand Total is selected on the pivot table, you observe..., such as Sum, Count and Average PivotTable in Excel ( except list ) column: under sort Grand... A PivotTable using the wizard in a pivot table index setting that you... Into a PivotTable using the wizard Total is selected Count and Average for column CPUVGA ) types ( except ). Totals in Charts: Reset the pivot table to analyse data ( I use there how to sort grand total in pivot table! When creating a chart from a pivot table index an important piece of information that users! Descending ' command when creating a chart from a pivot table is on! Filters and sorting for column CPUVGA ) to view all the data points order is given sort... Into the Row box it sorted correctly within each level lets you show multiple Grand Totals in Charts as! ( this is Grand Total as one of the data points when I added the field! List can contain any of the other types ( except list ) correctly within each level it... Any value inside the 'Grand Total ' column ; Select the 'Sort Descending ' command Total Sum of order in... Cpuvga ) this is Grand Total * Row * of a three-column PivotTable in Excel can observe under! As one of the data points within each level can observe, under sort by that users... As Greater Than 90.1 KB ) Grand Totals in Charts that column ) this... Within a pivot table to analyse data ( I use there some filters sorting. 'S an important piece of information that report users will want to see the field list boxes must!