Sometimes, it is important to show sequence numbers such as 1, 2, 3…..and so on. If you use database technologies, this is known as index column. 1, 2, 3…..are also used to denote rank.
Suppose you have a pivot table which is sorted in descending order for the value. For the sake of showing ranking, you want to insert an Index column. The purpose is that if somebody wants to know the ranking of say Jamaica, he will immediately get the info. Otherwise, he will need to count the position of Jamaica.
You can execute following steps to achieve this
- Insert a calculated field and give it a name say Rank. Put the formula = 1 in this. (simply putting 1 will also help)
- Drag this new field (named Rank here) into pivot table, values section.
- You should keep it as first field in values section. This is a matter of preference. I prefer it as first field.
- Now, click on Rank in Values section – Value Field Settings – Show Values As – Running Total In – OK
The sample file can be downloaded from Sequence_In_a_Pivot
https://youtu.be/MXKcWNBtEJo
Aw, this was a really nice post. Taking the time and actual effort to generate a superb article… but what can I say… I put things off a whole lot and don't manage to get nearly anything done.