How to Sort Pivot Table by Grand Total in Excel

This guide will discuss how to sort pivot table by grand total in Excel

Excel is an excellent tool to use for many difficult calculations. Furthermore, we can easily store, organize, and summarize data using Excel’s different built-in functions and tools.

And one powerful tool that categorizes our data set and displays the total sum of the values is the pivot table. So the pivot table is used to organize, summarize, format, and edit data sets. Using the pivot table, we can easily compare data and see the trends and patterns found within the data set.

Additionally, we can perform multiple things using the pivot table. For instance, we can group data by time and dates, values, and other criteria or conditions. However, this time we will focus on sorting a pivot table by the grand total.

Let’s take a sample scenario wherein we must sort a pivot table by grand total in Excel.

Suppose you are making a sales report containing the different sales of certain products made by three different stores. And you want to create a pivot table to summarize the values found in the data set by various categories. 

Furthermore, you want to sort the pivot table to display the values in descending order to easily show the largest sales made at the top and the smallest sales made at the bottom.

Great! Now we can move on and dive into a real example of sorting pivot table by grand total in Excel.

 

A Real Example of Sorting Pivot Table by Grand Total in Excel

Let’s say we have a data set containing the sales of certain products made from three different stores. So we have three columns in our data set. Firstly, we have a column for the three different stores. Then, we have a column for the different products sold at each store. 

Lastly, we have the sales of each product from the stores. So our initial data set would look like this:

Initial data set

 

Our task is to sort our pivot table by the grand total values. Fortunately, we can easily perform this task using the sort tool. Moreover, the sort tool is used to reorganize or rearrange the columns or rows in a pivot table according to the specific criteria or conditions we set.

First, we must convert our data set into a pivot table. To do this, we simply click the PivotTable tool in the ribbon. Then, we will select the pivot table fields we want to display or add to our pivot table. In this case, we want to add all the columns in our data set to our pivot table.

So we will select the store, product, and sales in their respective areas. And this will give us a pivot table of our original data set. But, one thing to note is that the pivot table automatically sums the values of our data set. So we will see a grand total row and column for each value in our data set.

Essentially, the pivot table will automatically summarize the values from the data set and present grand total values for each data value. In this case, we have a grand total column for the three stores and a grand total row for the different products.

Furthermore, we will utilize the grand total values to sort our pivot table. To do this, we simply have to right-click any grand total value and click sort. For instance, we want to sort the products based on their grand total values in descending order meaning the largest to smallest.

In this case, we will select the products’ grand total value row and sort them accordingly. If we want to sort the grand total values by store, we will select a value from the gran total value column instead. 

When we sort the grand total of the store in descending order, store C arranges to the first row of the pivot table, meaning it has the largest grand total value. On the other hand, store A goes down to the last row, meaning it has the smallest grand total value.

Similarly, the arrangement of the products also changes when we sort them in ascending order. In this case, the ballpen moves to the right column, which has the largest grand total value, while the pencil moves to the left column, meaning it has the smallest grand total value out of the three products.

So our final data set would look like this:

Final data set

 

 You can make your own copy of the spreadsheet above using the link attached below. 

Amazing! Now we can explain the steps of how to sort pivot table by grand total in Excel.

 

How to Sort Pivot Table by Grand Total in Excel

In this section, we will discuss the step-by-step process of how to sort a pivot table by grand total in Excel. Additionally, each step has detailed instructions and pictures to guide you along the way.

To apply this method, simply follow the steps below.

1. Firstly, we need to convert our data set into a pivot table. To do this, we will select the entire data set and go to the Insert tab. Then, we will click PivotTable in the Tables sections.

Convert to pivot table

 

2. Secondly, the Create PivotTable window will open. In this window, we will choose Existing Worksheet and input a blank cell location to display our pivot table. Lastly, we will click OK to apply the changes.

Display pivot table

3. Once we click OK, the PivotTable Fields panel will open on the right side of the worksheet. In this panel, we will select the fields we want to display in our pivot table.

Firstly, we will drag the Store field to the Rows box. Next, we will drag the Product field to the Columns box. Lastly, we will drag the Sales field to the Values box.

Sort Pivot Table by Grand Total in Excel

 

4. Now, we can sort our pivot table by grand total values. Let’s say we want to sort the pivot table based on the grand total values of the stores. So we will be using the grand total columns. To do this, we will select any cell in the grand total column and right-click.

Then, we will click Sort in the dropdown menu. Next, we can choose how we want to sort the pivot table. For instance, we want to sort the stores by the largest value to the smallest value. So we will click Sort Descending.

Sort Pivot Table by Grand Total in Excel

 

5. And tada! We have successfully sorted the pivot table by grand total in Excel.

Sort Pivot Table by Grand Total in Excel

 

6. We can also sort the grand total row pertaining to the product values. For example, we want to sort the product values from the smallest value to the largest value. So we will select any cell from the grand total row and right-click.

Then, we will select Sort and Sort Ascending in the dropdown menu.

 

Sort Pivot Table by Grand Total in Excel

 

7. And tada! We have sorted the pivot table based on the grand total values of the products.

Sort Pivot Table by Grand Total in Excel

 

And that’s pretty much it! We have successfully explained how to sort a pivot table by grand total in Excel. Now you can apply this easy and simple method to your work whenever you need to sort your pivot table by the grand total values. 

Are you interested in learning more about what Excel can do? You can now use the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like