Considering the pivot dataset in the Excel pivot table would actually be useful in some scenarios to rank your dataset. You can display ratings in Excel Pivot, but depending on the version of Excel you are using, there is one of two ways to achieve this rating.
First create the pivot table as usual. In this case, let's take a fictional data set that includes sales volumes and values for Sales A and K
- Select a data range using the mouse or
- Press CTRL + A to select data cells using the shortcut key (my favorite way)
- Insert tab
- Group of groups
- Select pivot table
- The dialog box should be pre-filled with your data, if this is not the case, please select a data range at this stage.
- Choose to place your pivot table on a sheet of sheet sheet or a new one.
At this point, we need to enter our data measure, but add it twice. Yes, it is correct, add it twice.
Therefore, in our example, we want to see the sales volumes for both sellers and A. Drag the Sales Person data field to the Row area. Then go and drag the sales volume to the area where it should read the Sum Of Sales Area. Then drag it again.
- Now we need to turn one of the data from the measurement of the amount of sales area into our ranking column of the pivot table.
- Right-click the second instance of the sales amount.
- From the drop-down menu, select "Show Value As"
- Scroll down and select "Rank from smallest to largest"
- Click OK
The above hint method works for Excel 2010 and beyond. If you have earlier versions of Excel 2003 to 2007, the method for achieving ranking order is slightly different.
- Sort your pivot table by the value you want to assign
- Add the calculated field to the table, where the formula = 1
- This will significantly add the value 1 to each line.
- Right-click on the newly created field and go to the value settings
- Select the "Show Value As" tab
- Change the setting “Show Total In Start Info.”
This will add another field that indicates the rating of each of the sellers from A to K based on their sales amount.
Thus, you can add a rank to a pivot table in Excel, this method depends on the version of Excel you are using.
