Using 3D Formulas to range across Excel worksheets

Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.


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. 

using 3d formulas to range across excel worksheets 31733 - Using 3D Formulas to range across Excel worksheets

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)  

 

using 3d formulas to range across excel worksheets 31735 - Using 3D Formulas to range across Excel worksheets

(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)  

 

using 3d formulas to range across excel worksheets 31736 - Using 3D Formulas to range across Excel worksheets

If we want to get an average of our electricity spend over the years, we can do that too: 

=AVERAGE(2013:2018!B14) 

 

using 3d formulas to range across excel worksheets 31739 - Using 3D Formulas to range across Excel worksheets

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.  

using 3d formulas to range across excel worksheets 31741 - Using 3D Formulas to range across Excel worksheets

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. 

using 3d formulas to range across excel worksheets 31743 - Using 3D Formulas to range across Excel worksheets

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. 

using 3d formulas to range across excel worksheets 31744 - Using 3D Formulas to range across Excel worksheets

Now, click on the cell you want to add across your worksheets. 

using 3d formulas to range across excel worksheets 31746 - Using 3D Formulas to range across Excel worksheets

Press Enter, and you will be taken back to your original worksheet and the total will appear in your selected cell. 

using 3d formulas to range across excel worksheets 31747 - Using 3D Formulas to range across Excel worksheets

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) 

 

using 3d formulas to range across excel worksheets 31749 - Using 3D Formulas to range across Excel worksheets

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.  

using 3d formulas to range across excel worksheets 31750 - Using 3D Formulas to range across Excel worksheets

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.) 

using 3d formulas to range across excel worksheets 31752 - Using 3D Formulas to range across Excel worksheets

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 (=).  

img 5da7d6a177eaf - Using 3D Formulas to range across Excel worksheets

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 

using 3d formulas to range across excel worksheets 31754 - Using 3D Formulas to range across Excel worksheets

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.) 

using 3d formulas to range across excel worksheets 31756 - Using 3D Formulas to range across Excel worksheets

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 

 


Want More?

Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.