Using 3D Formulas to range across Excel worksheets
3D Formulas in Excel allow you to make calculations using data from many different worksheets, including ones that don’t exist yet. This can be incredibly useful, when you have similar figures for different periods across worksheets and want to use data from all of those periods.
For our example, we have a file with expenses for a number of years, a different worksheet for each year. The data is in the same format on all of the sheets, that’s crucial.
At the most basic level, all you need to use data from different sheets is the cell addresses and the worksheet names. When using data from another worksheet in a formula, you enter the worksheet name, followed by an exclamation mark (!).
So to start simply, let’s say we want to add the July electricity figures from 2017 and 2018. In that case we would click in the cell where we want the total to appear and type:
=SUM(2017!B2+2018!B2)
(Note that Excel puts single quotes around the worksheet names after your press Enter, but you don’t need to type these.)
What if we want to add up our total electricity cost for the past six years? Rather than type each of the worksheets in, we can enter them as a range, as long as the data we want is in the same cell on every sheet.
Our total electricity cost is in cell B14, so we only need to enter that once. To enter a range of worksheets, type them with a colon in between, then the exclamation mark, then follow this by the cell address:
=SUM(2013:2018!B14)
If we want to get an average of our electricity spend over the years, we can do that too:
=AVERAGE(2013:2018!B14)
Mouse it
If you don’t want to type the formula in, it is possible to do most of it with the mouse instead.
First, click in the cell where you want the total to appear and type the equals sign. Then click on the Sum button and choose the formula you want to use.
Now, click on the tab for the first worksheet in your range (2013). You will notice that you will be taken to that worksheet, but the tab of the worksheet you were on will also remain highlighted.
Then, hold down the Shift key and click on the tab for the last worksheet in your range (2018). Now all your worksheet tabs plus your original worksheet tab will be highlighted.
Now, click on the cell you want to add across your worksheets.
Press Enter, and you will be taken back to your original worksheet and the total will appear in your selected cell.
Adding and Deleting Worksheets
If you add or delete any of the worksheets within the selected range of sheets, your calculation will be automatically updated to reflect the change.
Note, however, that any added sheet must be within the original range selected – if you add a new worksheet before or after the original range, it will not be included. So what if we want to be able to keep adding years to our example above and have the total keep updating? In that case, we could enter a blank or dummy worksheet which we select as the end worksheet in our range.
=SUM(‘2013:Leave Blank’!B14)
Then, whenever we want to add a worksheet for a new year, we make sure that we always add it before our blank worksheet. The figures for the new year will then be automatically added in, and the total in our result cell will change to include the new figure.
Naming Your 3D Reference
Just as you can name cells and ranges of cells in Excel, you can also create a name for your 3D range, which can then be used in formulas.
To create a named range, first go to Formula | Define Name. (At this point it doesn’t matter what cell you have clicked in.)
In the New Name dialog, enter the name you want to give this range, and delete everything in the Refers To field except the equals sign (=).
Now, just as we did before, we simply click on the first worksheet, Shift-click the second worksheet and click on the cell or cells we want, then click OK.
Now anytime we want to use these total electricity figures in a formula, we can simply type All_Electricity instead of having to select the range again. (Usually we won’t have to type the whole name out though, as when we start typing the name, a list of matching names will appear that we can select from.)
So now when we want to fill our total and average electricity fields, we can just enter formulas using the range name, rather than having to select the range again.
=SUM(All_Electricity)
=AVERAGE(All_Electricity)
And of course we can create more complicated formulas using multiple named ranges as well. So if we create a similar range for our internet figures, called All_Internet, we can then add up our cumulative electricity and internet expenditure using the formula:
=SUM(All_Electricity,All_Internet)
What Functions Can Be Used in 3D Formulas?
Not all functions can be used with a 3D range. The full range of functions that can be used are as follows:
- SUM
- AVERAGE, AVERAGEA
- COUNT, COUNTA
- MAX, MAXA
- MIN, MINA
- PRODUCT
- STDEV, STDEVA, STDEVP, STEVPA
- VAR, VARA, VARP, VARPA