Your Excel workbook comprises several worksheets that contain first-quarter sales for ice cream sold in several of your company’s business locations. You recorded the figures for each month on a separate worksheet. That is, there is a worksheet for January, another for February, and a third for March. The position and layout of the tables on all three worksheets are identical.
Now, on a fourth worksheet, you want to create a table that will summarize the sales figures for all three months of the first quarter.
Because the data you want to summarize are located in exactly the same position on each of the the three worksheets, you can use excel 3-D calculations to add them together.
In this learning module, you will be shown how to use excel 3-D calculations to summarize data on multiple worksheets.
EXCEL CALCULATIONS: HOW DOES IT WORK?
The AutoSum function enables you to add together data from multiple worksheets, provided the cells to be added are in identical positions on each of the worksheets.
To calculate a sum over several worksheets, first open the worksheet that is to contain the summary table . In our example, you want to summarize data from the January, February, and March worksheets on the Quarter 1 worksheet. You will begin by summarizing the Chocolate flavor sales in Detroit. In the summary table, click cell B5. Then, on the Home tab, in the Editing group, click the AutoSum button.
The addition function =SUM() will be inserted into the cell.
Now you have to enter the 3-D cell references. A 3-D cell reference simultaneously links to the same cell address on multiple worksheets. To do this, just click the January worksheet’s tab, and then, while holding the [Shift] key depressed, click the the March worksheet’s tab. If you release the [Shift] key now, the January worksheet will display again. In the formula bar toward the top of the Excel window, you can see the partially constructed 3-D cell reference: =SUM(January:March!). The part of the formula “January:March!” expresses the range of worksheets from January to March. To include the address of the cells that are to be added together, click cell B4 in the January worksheet. Conclude the formula by clicking [Enter]. The summarized value for chocolate ice cream sales in Detroit during the first three months of the year now appears in cell B5 of the Quarter 1 worksheet.
Excel calculations: HINT
Instead of continuing to manually enter the SUM function with its cell references for the other ice cream flavors, you can use the AutoFill function instead. To do this, position the mouse pointer on the bottom-right corner of cell B5. It will change to a black cross. Now, while holding the mouse button depressed, drag the cross down until cell B8 is covered. When you release the mouse button, the SUM function will appear with appropriately adjusted cell references in all the cells you covered. As a result, the sales numbers for the other ice cream flavors and the total are displayed. You can use AutoFill to fill in the data for the remaining columns, as well. Just select the entire Detroit data column (B5 to B8), and then use your mouse to drag it by the lower-richt corner until the London column is covered, too.
The AutoFill function can only be used if the worksheets in question are identically structured.
You may notice that when you use the AutoFill function, you will overwrite any existing cell formatting. To retain the original formatting of the destination cells, move your mouse pointer just below and to the right of the range of cells you just filled in with AutoFill. Then click the SmartTag displayed below and to the right of the range you just filled. In the selection list that appears, activate the option.
Because you used excel 3-D calculations cell references to display the summary data in your new table, it will always contain the current consolidated sales figures for the first quarter, even if corresponding data on any of the other three tables changes.