One essential feature of pivot tables is the ability to sort data, and while sorting individual columns is a common practice, sorting by the grand total can provide a broader perspective on the data. In this step-by-step guide, I’ll show you how to sort a pivot table by grand total to gain valuable insights.
Step 1: Insert a Pivot Table in Excel
In this approach, we have a dataset of 3 different brands selling electronics products. Here I want to compare brands with each other. Lastly, I will analyze which product has the highest sales value.
First, I will insert a Pivot Table. Then, I am gonna sort the Pivot Table by grand total in Excel.
To insert a Pivot Table, follow the steps below:
- Select the entire data range.
- Go to Insert > PivotTable > From Table/Range.
- Choose the location to place the Pivot Table in Pivot Table from a table or range dialog box.
- Click OK.
After clicking OK, you will see an inserted Pivot Table with PivotTable Fields in the Excel window.
Step 2: Drag and Arrange Pivot Table in Excel
Let’s arrange the Pivot Table before sorting by grand total in Excel. Go through the following procedure:
- Select a field name from the PivotTable Fields.
- Drag the field to any of the areas, such as Rows, Columns, Filters & Values.
- Repeat the same process for other fields too.
Here, I have dragged the brand name and put it into the Rows area and product names into the Columns area. Then, I put the sales data in the Values section in the PivotTable Fields.
Step 3: Sort Pivot Table by Grand Total in Excel
To sort the Pivot Table by Grand Total, follow the steps below:
- Select a cell in the Grand Total column/row.
- Right-click on that cell to access the context menu.
- Click on Sort.
- Choose the sort options from the list.
Now, you will see the sorted Pivot Table by grand total. Here I chose Sort Smallest to Largest for both the rows and columns.
In conclusion, mastering the Pivot Table’s grand total sorting feature empowers users to efficiently organize and analyze data, providing a valuable tool for informed decision-making and data-driven insights. In this article, I have guided you with the step-by-step instructions for sorting Pivot Table by grand total in Excel. First I inserted a Pivot Table, then arranged them and lastly sorted by grand total. So, hopefully, you will get detailed instructions by going through this article.
Frequently Asked Questions
Why can’t I sort total in Pivot Table?
If you can’t sort totals in a Pivot Table, ensure your data range is correctly selected and includes the grand total row or column. Check for any blank cells in the range, as these can hinder sorting. After confirming these aspects, proceed to sort totals using the usual steps.
How do you sort a Pivot Table by sum amount?
To sort a Pivot Table by sum amount in Excel, follow these steps:
- Click on any cell within the column containing the sum amounts you want to sort.
- Go to the Data tab in the Excel ribbon.
- Click on the Sort button.
- In the Sort dialog box, choose the column you want to sort by from the Sort by dropdown menu.
- Specify the sort order, such as Largest to Smallest or Smallest to Largest.
- Click OK to apply the sorting.
Mastering the art of sorting by sum amount in a Pivot Table empowers you to efficiently analyze and present your data in Excel.
How do you sort without grand total in Pivot Table?
To sort a Pivot Table without including the Grand Total, follow these steps:
- Click on any cell within the column you want to sort.
- Right-click and select Sort.
- Choose either Sort Smallest to Largest or Sort Largest to Smallest, depending on your preference.
This way, you can arrange the data in the Pivot Table without affecting the Grand Total row or column.