Do you know how to sort pivot table values from largest to smallest? If not, then worry not! We’ve got you covered!
A powerful arsenal in every aspiring data analyst’s kit, Microsoft Excel. This has been dominating the industry for decades.
Pivot tables undoubtedly uplift the spreadsheet software’s potential and allow users to aggregate the data based on a specific criterion.
It is the crucial feature of Excel and Power BI, the dynamic visualization software required to create interactive dashboards and generate insights.
But how do you sort pivot table values from largest to smallest? If you, too, are struck by this fundamental but baffling question, here is a step-by-step guide explicitly curated to solve your issue!
1. What is a Pivot Table?
From its extensive formulae to interactive features, Excel offers a bunch of tools that aid in the entire data analysis process, from the collection of data to the creation of dashboards. One such feature is the pivot table.
It is an attribute that helps summarize an extensive dataset into an aggregated table that delivers meaningful insights.
This feature offered by Microsoft Excel and Power BI is primarily used to club relevant rows and columns. It then displays organized information in either a dashboard or a report.
For instance, you have a dataset that consists of thousands of rows and dozens of columns. After deploying the initial data analysis steps, such as preprocessing and cleaning, you’ll want to visualize the data in sorted tables and charts.
Pivot Tables make these visuals seamless, thus streamlining the analysis process.
2. Why Use a Pivot Table?
Pivot tables are generally created to analyze small data packages from a vast data set. This tool allows the analyst to manage the extensive dataset by sorting through relevant columns and rows and extracting values based on filters applied.
Here are six reasons why you should consider using this feature:
2.1. Streamlined Data
Pivot tables let you streamline your data per your current requirements.
Instead of sifting through thousands of rows of messy data and sorting each cell manually, you could utilize this feature to smoothen your dataset.
2.2. Customized and Aggregated View
No analyst can derive insights from a thousand-row-long dataset by merely sifting through it. Pivot tables offer customized and aggregated views of extensive datasets, simplifying the analysis process significantly.
2.3. Filtering Options
While pivot table is inherently a feature of Microsoft Excel and Power BI, they are easily adaptable to the users’ requirements.
Flexibility is at the core of this tool, which lets you filter your data by any parameter of your choice.
2.4. Entirely Dynamic Summaries
While filters are a popular feature of pivot tables, its core offering remains dynamic data.
For instance, if your dataset is altered, the pivot table aligned with the dataset will automatically reflect the changes.
This ability comes in handy with enormous datasets and thorough analysis.
2.5. Advanced Charts Offering
Due to this extensive feature, creating advanced charts has become relatively easier. You can create any number of dynamic charts for your dashboard without sticking to the decades-old formats.
2.6. Interactive Dashboards
Since this tool offers several advanced-level charts, such as histograms and 3D maps, engaging dashboards can be easily created.
Moreover, due to the slicers and timelines offered by this powerful tool, you can easily create an interactive dashboard that portrays meaningful insights.
3. How do you Create a Pivot Table?
To create a pivot table, first select your data and convert it into a table using the shortcut Ctrl + T, and you will be able to transform this:
Into this:
After converting your raw data into a table, click the Pivot Table option from the Insert ribbon. You’ll be asked to input the data range/table range if you haven’t yet transformed the data into a table.
However, if you have a table, the popup window will automatically display the table range and ask whether your pivot should be on the same worksheet or a different one.
For clarity purposes, always choose a new worksheet.
You will then be directed to a new sheet where a side panel entitled PivotTable Fields will await you.
This panel consists of four sub-categories that are essentially provided to help you enhance your reports.
3.1. Filters
This option lets you further drill down your selection and view only the rows/columns you currently want.
For example, you want to create a Manufacturing Price vs. Sales Price report for a particular product.
You could add the Product as a filter and view only the prices for the current selection.
3.2. Rows
In this case, you’ll choose Product as a row to create a report centred around the products offered by the company.
Rows are nothing but vertical bands of values.
3.3. Values
The value you wish to analyze based on the Product is assigned in this subcategory.
To create a simple yet profound report here, you’ll choose Manufacturing and Sales Price as values.
3.4. Columns
This feature lets you choose which columns from your dataset should appear in your current report.
In the example (Manufacturing Price vs. Sales Price Report), you’ll choose both Manufacturing Price and Sales Price as Values, eventually becoming Columns.
The significant difference between the two is that Values can only be numerical, while Columns can be numerical or alphabetical.
Now that you are clear on the subcategories of the Pivot Table let us jump to the next step and add some values to it!
4. How do you add Values to a Pivot Table?
Aside from providing efficient reports, the best feature of a pivot table is its “drag-and-drop” functionality.
To add values to a pivot table, all you have to do is drag the option from the categories listed in the panel and drop it in one of the field areas.
To create a Manufacturing Price vs. Sales Price Report, you will have to drag Products into the Rows fields, Manufacturing Price and Sales Price into the Values field, and a small table will form on the left side of the sheet (as you can see in the screenshot provided below).
Now, you might have noticed the haphazard arrangement of the table and must be wondering how to sort pivot table values from largest to smallest numerically and alphabetically. More on that below!
5. How do you Sort Pivot Table Values from Largest to Smallest?
To sort a pivot table values from largest to smallest (or vice-versa) in a matter of minutes, follow these five simple steps:
- Select any cell in the pivot table and right-click on that cell.
- You will find a pop-up window hovering over the partition you just clicked.
- Sift through the options and hover over the “Sort” feature.
- Another small pop-up window will be opened in which you will be asked to either “sort smallest to largest” or vice versa.
- You can choose any of the features of your choice to have the pivot table sorted.
After sorting the Sales Price values from largest to smallest, here is how the resultant table appears:
As you might have observed, the product VTT has been listed first due to the kind of sorting that has been applied.
Since this product boasts a maximum sales price (of around 2701), it has been listed first. Meanwhile, “Amarilla” has shifted to the bottom of the list due to its low sales.
Please note that you cannot have both values (columns) sorted simultaneously. If you are focusing on Sales Price alone, sort this column or vice versa.
5.1. How do you use sort and Filter options for a Pivot Table?
You can also sort the pivot table using the ribbon toolkit offered by Microsoft Excel. Follow these steps. Watch your aggregated data set being filtered by the in-built functions provided by the spreadsheet software itself:
- Select a cell in the pivot table and head to the Home ribbon.
- From the Editing section, choose the Sort and Filter option.
- Select either one of the three available options–”Sort Smallest to Largest,” “Sort Largest to Smallest,” and “Custom Sort.”
- In this case, choose the first option to sort the pivot table in the ascending order.
Please note that the cell you select before clicking on the Sort and Filter option becomes the filter criterion for the current iteration.
In this case, I have chosen Manufacturing Price as the filter criteria.
Now that you have mastered sorting pivot tables numerically, don’t you want to know how you could do the same alphabetically?
6. How do you sort Pivot Table Values Alphabetically?
Suppose you want to sort a pivot table based on the Product name instead of continuously dodging between Sales and Manufacturing prices.
Follow these three steps and watch your pivot table being transformed into a categorized list:
- Select the small downward arrow button above the “Row Labels” cell.
- Upon clicking on it, a pop-up window with multiple options will appear on your screen.
- From the above two sorting options listed, choose whether you want to filter the data in an ascending (A-Z) manner or descending (Z-A) manner.
After choosing the first option, here is what a pivot table looks like:
Amarilla is listed first, followed by Carretera, and the list eventually ends with VTT. Microsoft Excel utilizes built-in intelligence features to sort the data alphabetically or numerically.
It almost always provides accurate results and categorizes the pivots. However, if you aren’t satisfied with the sorting, you could always custom-sort your pivot table.
7. Advanced Pivot Tables Sorting Technique (Custom Sort)
With the abovementioned techniques, your dilemma of “how to sort pivot table values from largest to smallest” can be easily solved.
But what if you want to change the sorting direction as well? Here is where the custom sort option jumps in and rescues you!
To categorize the pivot table using the Custom Sort options, you could choose from these two methods:
7.1. Method 1: By Using The Home Ribbon
This is the easiest method to custom-sort data of any pivot table. Follow these steps and choose from the four options to classify your data the way you want:
- Click on any cell in the pivot table.
- Click on the Sort And Filter option from the Home Ribbon’s Editing section.
- Choose the Custom Sort option from the dropdown list, which will open a pop-up menu with four sorting options divided into two categories–Sort Options and Sort Direction.
- The Sort Options consists of two formatting choices–Smallest to Largest and Largest to Smallest–and the Sort Direction tab lets you choose between the Top to Bottom and Left to Right filters.
- Choose two out of these four options and watch your pivot table getting sorted by these advanced filters.
Due to this advanced sorting option offered by Excel, you can exercise more control over your pivot table and choose the filter direction.
This is the easiest method to apply the extensive filters in minutes and a few clicks.
7.2. Method 2: By Using The “More Sort Options”
If you are unable to access the Custom Sort option through the Home ribbon, you can follow these steps to access this extensive feature:
- Click on any cell in the pivot table (preferably from the column you want to use as the filtering parameter).
- Right-click on the cell and hover over the Sort option.
- A small pop-up window will appear over the screen, containing three options–“Sort Smallest to Largest”, “Sort Largest To Smallest,” and “More Sort Options”.
- Choose the last option, and a window similar to the one mentioned in the previous sorting method will appear on your screen.
- Choose the filter criteria you want to apply from the two tabs–Sort Options and Sort Direction.
Your aggregated dataset would be classified based on the two options you have selected in just a few clicks–similar to the previous method.
8. What Should we do if the Pivot table isn’t being Sorted?
A common problem you might face while sorting your pivot table is improper or no filtering.
This might occur due to several reasons, including improper referencing and the presence of custom lists.
Here are three methods you could deploy to solve this error quickly and sort your pivot table.
8.1. Method 1: Check The Reference Cells
Check whether your pivot table refers to the dataset you are trying to sort.
First, ensure your data is structured into a table to complete this cross-checking process. Then, to be on the safe side, create another pivot table.
Drag the values into the columns and rows you aspire to, then choose a filter.
Deploy any of the sorting techniques mentioned above, and if the problem persists, try the remaining two solutions.
8.2. Method 2: Refresh The Pivot Table
If you have cross-checked the reference cells and your pivot table isn’t yet sorted, try refreshing the pivot table by following these steps:
- Select any cell from the pivot table.
- From the PivotTable Analyze ribbon, choose the Refresh option.
- Alternatively, you could also use the shortcut Alt + F5.
Then, apply any sorting techniques, and your dataset will be filtered based on the criteria. However, if the problem isn’t solved, you must uncheck the custom lists.
8.3. Method 3: Uncheck The Custom List Presence
You cannot filter the pivot table because the custom lists specify the sorting criteria dynamically. To uncheck this list, follow these steps:
- Select any cell from the pivot table and right-click on it.
- Choose the PivotTable Options from the pop-up window.
- A new window will appear on the screen, and click on the Totals And Filters tab.
- Uncheck the “Use Custom Lists When Sorting” option from the Sorting group.
- Click on the “OK” button and retry applying any sorting techniques.
This will eliminate any issues you might face regarding the sorting procedure. And now, you will have a filtered pivot table in a few clicks.
Key Takeaways
Microsoft Excel remains the leading spreadsheet software that has dominated the data field for decades.
Similarly, Pivot tables continue to be a prominent feature. From creating charts to an aggregated dataset, the pivot table offers many features. Sorting the pivot table is crucial while creating interactive dashboards and reports.
You must filter the data numerically or alphabetically to curate these dashboards. Hopefully, your dilemma of “how to sort pivot table values from largest to smallest” is solved now!
Last Updated on December 7, 2023 by Apeksha Soni
