Pinterest Pixel

Rank Largest to Smallest With Excel Pivot Tables

Bryan
Excel Pivot Tables have a lot of usefulĀ calculations under the SHOW VALUES AS option and one that can help you a lot is the Excel Pivot Table RANK LARGEST TO SMALLESTĀ calculation.
This option will immediately calculate the rankings (1 being the LARGEST value) for your values, allowing you to pinpoint the risks or opportunities quickly! It allows you to analyze data by ranking them and check what's on top and what is at the bottom!

Excel Pivot Tables have a lot of usefulĀ calculations under the SHOW VALUES AS option and one that can help you a lot is the Excel Pivot Table RANK LARGEST TO SMALLESTĀ calculation. This option will immediately calculate the rankings (1 being the LARGEST value) for your values, allowing you to pinpoint the risks or opportunities quickly! It allows you to analyze data by ranking them and check what’s on top and what is at the bottom!

Key Takeaways

  • Sort Values Quickly ā€“ Excel Pivot Tables allow you to easily sort numerical data from largest to smallest with just a few clicks, helping you spot top performers or high values instantly.

  • Use the Value Field Settings ā€“ You can apply custom sorting by accessing the Value Field Settings and selecting options like “Descending” based on specific fields.

  • Rank with Calculated Fields ā€“ Pivot Tables support the use of Rank formulas or DAX (in Power Pivot) to create a dynamic ranking column within the table.

  • Multi-level Sorting ā€“ You can sort by one field (e.g., Sales Amount) and then apply a secondary sort on another field (e.g., Region), giving you layered sorting control.

  • Dynamic Updates ā€“ Sorting from largest to smallest is automatically refreshed when underlying data changes, ensuring rankings are always up to date without manual rework.

You can easily do ranking in Pivot Table using either of the two methods mentioned below:

Let’s look at these methods in-depth!

Follow theĀ step-by-step tutorial on Excel Pivot Table Rank Largest to Smallest and download this Excel workbookĀ to practice along:

download excel workbook Rank-from-Largest-to-Smallest.xlsx

 

Using Sorting Option

STEP 1:Ā Insert a new Pivot table by clickingĀ on your data and goingĀ toĀ Insert > Pivot Table > New Worksheet or Existing Worksheet

Rank Largest to Smallest With Excel Pivot Tables

 

STEP 2:Ā In theĀ ROWSĀ section put in theĀ Sales MonthĀ field, in theĀ COLUMNSĀ put in the FinancialĀ Year field, and in theĀ VALUESĀ area you need to put in theĀ SalesĀ field twice, I explain why below:

Rank Largest to Smallest With Excel Pivot Tables

 

STEP 3:Ā Click the second Sales fieldā€™sĀ (Sum of SALES2) drop down and chooseĀ Value Field Settings

Rank Largest to Smallest With Excel Pivot Tables

 

STEP 4:Ā Select theĀ Show Values AsĀ tab and from the drop down chooseĀ Rank LargestĀ to Smallest. Ā 

Select Sales MonthĀ as the Base Field. This means that we will rank the Sales Values by the Sales Month (where Rank 1 is the Largest).

Also change theĀ Custom Name into Rank LargestĀ to SmallestĀ to make it more presentable. Ā ClickĀ OK.

Rank Largest to Smallest With Excel Pivot Tables

 

You now have your Pivot Table, showing the Rank LargestĀ to SmallestĀ for the sales data of years 2012, 2013, and 2014.

You can see that each red box is the ranking for each year (for Years 2012, 2013, 2014, and the Total Rankings).

Rank Largest to Smallest With Excel Pivot Tables

This is how you can easily Rank Pivot Table in few easy steps!

 

Using Calculated Field

STEP 1: Select any cell in the data and then Go to Insert > Pivot Table.

Rank Largest to Smallest With Excel Pivot Tables

STEP 2: In the dialog box, select New Worksheet and then click OK.

Rank Largest to Smallest With Excel Pivot Tables

STEP 3:Ā Drag and drop Sales Month in the Row field and Sales in the Values field.

Rank Largest to Smallest With Excel Pivot Tables

STEP 4:Ā Click on the filter button and Select More Sorting Option.

Rank Largest to Smallest With Excel Pivot Tables

STEP 5:Ā In the Sort dialog box, Select Sum of Sales in the Ascending by dropdown. Click OK.

Rank Largest to Smallest With Excel Pivot Tables

This will sort the data in ascending order!

Rank Largest to Smallest With Excel Pivot Tables

STEP 6: Click on any cell in the Pivot Table and Go to PivotTable Analyze > Calculated Field.

Rank Largest to Smallest With Excel Pivot Tables

STEP 7: In the Insert Calculated Field dialog box, Type =1 n the formula field. Click OK.

Rank Largest to Smallest With Excel Pivot Tables

STEP 8: Right-Click on the calculated field and select Value Field Setting.

Rank Largest to Smallest With Excel Pivot Tables

STEP 9:Ā In the Value Field Setting dialog box, Select Running Total in as Show Value as! Click OK.

Rank Largest to Smallest With Excel Pivot Tables

This will sort the values from largest to smallest and insert a rank field in the Pivot Table!

Rank Largest to Smallest With Excel Pivot Tables

Frequently Asked Questions

How do I sort data from largest to smallest in a Pivot Table?
Right-click on any numeric value in the Pivot Table, choose Sort ā†’ Sort Largest to Smallest, and Excel will automatically reorder the rows accordingly.

Can I rank items within each category in a Pivot Table?
Yes, you can enable multi-level sorting by dragging category fields into Rows and then sorting values within each category individually.

Is it possible to show the rank number directly in a Pivot Table?
Yes, by using Rank formulas or DAX functions in Power Pivot, you can display a ranking number alongside each item based on your sorting criteria.

Will the sorting update if I change the source data?
Absolutely. When you refresh the Pivot Table, the largest-to-smallest sort order automatically updates to reflect any new or changed data.

Can I sort text fields alphabetically after sorting numbers?
Yes, Excel allows you to sort numerical fields by size and then sort text fields alphabetically, giving you flexible control over how your Pivot Table is organized.

Conclusion

In this tutorial, you have learned how to rank in pivot table by either using the Sorting option or by inserting a calculated field.

PIVOT BANNER

If you like this Excel tip, please share it



Rank Largest to Smallest With Excel Pivot Tables | MyExcelOnline


Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.

See also  Learn How to Use ChatGPT in Under 5 Minutes!

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME ā€” no strings attached! Even if itā€™s on day 29, you wonā€™t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...