{"id":24081,"date":"2021-12-22T06:21:36","date_gmt":"2021-12-22T05:21:36","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=24081"},"modified":"2024-02-06T16:01:54","modified_gmt":"2024-02-06T15:01:54","slug":"index-in-pivot-table","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/index-in-pivot-table\/","title":{"rendered":"Index in Excel Pivot Tables"},"content":{"rendered":"

\"Index<\/a><\/p>\n

One cool thing with Excel is you are able to show values as the Index<\/a><\/strong> in Pivot Table. In a nutshell, the Index will tell you the relative importance of a cell\u00a0<\/strong>i.e. it will show you the\u00a0relative importance of each value<\/strong>\u00a0when compared to its row, column, and grand total.<\/p>\n

It can help you make decisions<\/strong> if, for example, you want to increase the price of your product, then you will be able to identify the area that will be most impacted (based on the Index).<\/p>\n

 <\/p>\n

Don’t forget to download the Exercise Workbook<\/strong> below and follow the step-by-step tutorial on Index in Pivot Table along with us:<\/p>\n

\n

download excel workbook<\/strong><\/i><\/span>Index.xlsx<\/span><\/a><\/p>\n

\n<\/div>\n

\n

STEP 1:<\/strong><\/span> Here is our Pivot Table. The two tables are exactly the same.<\/p>\n

\"Index<\/p>\n

STEP 2:<\/strong><\/span> Go to the second Pivot Table, and click on the arrow of\u00a0Sum of SALES<\/strong> and select\u00a0Value Field Settings<\/strong><\/p>\n

\"Index<\/p>\n

STEP 3:<\/strong><\/span> Select\u00a0Show values as > Index.\u00a0<\/strong><\/em>Click\u00a0OK<\/strong><\/p>\n

\"Index<\/p>\n

STEP 4:<\/strong><\/span> Now we have our Index<\/strong> values! The higher value means that it is the most important value for that column, for example, Bottles<\/strong> have the greatest impact on the Americas Region<\/strong> in terms of sales.<\/p>\n

\"Index<\/p>\n

Now let’s have a quick calculation to show how Index is calculated. Let us target the Bottles Sales for the Americas Region.<\/p>\n

STEP 5:<\/strong><\/span> Select the Sales for Bottles-Americas<\/strong> then multiply it by the Grand Total<\/strong>.<\/p>\n

\"Index<\/p>\n

STEP 6:<\/strong><\/span> Multiply the Grand Row Total of Bottles<\/strong> and the Grand Column Total of Americas<\/strong>.<\/p>\n

\"Index<\/p>\n

STEP 7:<\/strong><\/span> Divide them and you will get the Index!<\/p>\n

\"Index<\/p>\n

The values are exactly the same!<\/p>\n

\"Index<\/p>\n

Thus, you can calculate the index number<\/strong> for any value by using the formula<\/a> below:<\/p>\n

= (Value of cell * Grand total of Grand Totals) \/ (Grand Row Total * Grand Column Total)<\/p>\n

In this example, you can see that the sales amount of bottles in Africa (2,084,910) and soft drinks in Europe (2,085,086) are almost equal<\/strong>.<\/p>\n

\"Index<\/a><\/p>\n

But, the index numbers are 0.98 and 1.06<\/strong>.<\/p>\n

\"Index<\/a><\/p>\n

The reason behind this difference is that grand total of Africa is greater than that of Europe<\/strong>. So, when the Africa bottles sales amount is divided by a larger number, it results in a lower index<\/strong> number.<\/p>\n

 <\/p>\n

These index numbers give the user a clear picture of the importance of each sales amount<\/strong> as per the row, column, and grand totals. The following should be noted with respect to the index number:<\/p>\n