Pinterest Pixel

Index in Excel Pivot Tables

One cool thing with Excel is you are able to show values as the Index in Pivot... read more

Download Excel Workbook
Bryan
Posted on

Overview

Index in Excel Pivot Tables | MyExcelOnline

One cool thing with Excel is you are able to show values as the Index in Pivot Table. In a nutshell, the Index will tell you the relative importance of a cell i.e. it will show you the relative importance of each value when compared to its row, column, and grand total.

It can help you make decisions 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).

 

Don’t forget to download the Exercise Workbook below and follow the step-by-step tutorial on Index in Pivot Table along with us:

download excel workbookIndex.xlsx


STEP 1: Here is our Pivot Table. The two tables are exactly the same.

Index in Excel Pivot Tables

STEP 2: Go to the second Pivot Table, and click on the arrow of Sum of SALES and select Value Field Settings

Index in Excel Pivot Tables

STEP 3: Select Show values as > Index. Click OK

See also  Pivot Table Conditional Formatting

Index in Excel Pivot Tables

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

Index in Excel Pivot Tables

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

STEP 5: Select the Sales for Bottles-Americas then multiply it by the Grand Total.

Index in Excel Pivot Tables

STEP 6: Multiply the Grand Row Total of Bottles and the Grand Column Total of Americas.

Index in Excel Pivot Tables

STEP 7: Divide them and you will get the Index!

Index in Excel Pivot Tables

The values are exactly the same!

Index in Excel Pivot Tables

Thus, you can calculate the index number for any value by using the formula below:

= (Value of cell * Grand total of Grand Totals) / (Grand Row Total * Grand Column Total)

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.

See also  Pivot Table Report Layouts

Index in Excel Pivot Tables

But, the index numbers are 0.98 and 1.06.

Index in Excel Pivot Tables

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

 

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

  • If all the values in the Pivot Table are equal, the index in Pivot Table will be 1.
  • If the index in Pivot Table is less than 1, it means that the value is of lower importance than its row and column values.
  • If the index in Pivot Table is greater than 1, it means that the value is of greater importance than its row and column values.

There are many more custom calculations in the Pivot Table such as % of Column, % of Row, etc, Click Here to know all about it!

See also  101 Excel Pivot Tables Examples

 

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

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Index in Excel Pivot Tables | MyExcelOnline
Index in Excel Pivot Tables | MyExcelOnline
Bryan

Bryan is a best-selling book author of the 101 Excel Series paperback books.

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!