# Rank Largest to Smallest With Excel Pivot Tables

Excel Pivot Tables have a lot of usefulĀ calculations under the SHOW VALUES AS option and one that... read more

## Overview

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!

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

Let’s look at these methods in-depth!

In the example below I show you how to get the Excel Pivot Table Rank Largest to Smallest:

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

### 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

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:

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

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.

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).

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.

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

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

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

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

This will sort the data in ascending order!

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

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

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

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

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

### 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.

Make sure to download our FREE PDF on theĀ 333 Excel keyboard Shortcuts here:

##### 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.