Three-dimensional formula in MS Excel

by Anantha Krishnan S

To begin with, I would like to share one of the tips which I have come across in the recent times. It’s on setting up 3-dimensional formula in excel. This simple-yet-effective operation will help you save time and maintain consistency across the workbook.

In many business situations, you may be required to set up a workbook with multiple worksheets of same template. Examples ranges from a sales head tracking sales data across different regions to a production planner accessing the inventory of several items across production plants etc. Here the key is the use of same template and summarizing all in a single worksheet.

For better understanding, let’s assume that you are a statistician at ESPNCRICINFO.com. You are supposed to compile data and produce some interesting content with the help of data on number of fours, sixes, dot balls, overs played and wickets in the upcoming India-Australia-England series.

The tournament involves 7 matches. It would be easier to compile data if we set up the workbook with same template.

ananth1

Let’s create 7 worksheets first. For this open seven empty worksheets. (By the way, you can change the default number of worksheets through ‘File -> Options -> General’ menu). Also, create one more summary sheet as shown here.

ananth2

Then, for replicating the template to all 7 sheets, select Sheet 1, press & hold down the SHIFT key and press the last worksheet – sheet 7.

ananth3

With all sheets selected, enter the template you want to replicate in Sheet1.

ananth4

Thus whatever you enter in Sheet1 will be replicated to other selected sheets as well. Use different sheets (till Sheet7) for capturing data for subsequent matches.

By the end of the tournament, we can use 3-dimensional formula to summarize the tournament statistics. For example, to calculate the total attendance of the tournament, type =SUM( ,move your cursor to the first cell (cell B1 of sheet1), hold down the Shift key and click the last cell (cell B1 of sheet7). Lastly, enter a right parentheses in the formula bar, and you will see the formula SUM(sheet1:sheet7!B1). This formula tells Excel to sum cell B1 in all worksheets, starting with Sheet1 and ending with Sheet7. Also, you can copy this formula and extend this till B6 as shown here.

ananth5

I just used this example to give you an idea about 3-dimensional formula. You can use all your complex excel functions maintaining same template to extend the use for capturing complex real life scenarios.

Anantha Krishnan S is a second year PGP student at IIMB, and part of the Spreadsheet Modelling for Business Decision Problems course. 

Advertisements