3D Referencing & External Reference in Excel
Creating 3D referencing in Excel within a formula involves referring to the same cell or the same range of cells, on different worksheets. Each of the sheets, one is using, to create the 3D reference should have the same pattern and type of data.
Creating an external reference means that one is referring to a cell or a range of cells, in another Excel workbook or workbooks.
Both of these referencing types are very efficient when one wants to collate data from multiple sheets or workbooks.
So, let’s get started with a simple example, to illustrate how to create a 3D reference and an external reference.
Table of Contents
- 1 Introduction
- 2 3D Referencing in Excel within a SUM Function
- 3 3D Referencing within a MIN Function
- 4 Creating an External Reference in order to sum data in different workbooks
- 5 Creating an External Reference in order to find the minimum value from different workbooks
- 6 Download Working Files
- 7 Conclusion
- 8 Useful Links
YouTube has more than 400 popular vlogging channels, devoted exclusively to beauty tutorials. While exploring the world of beauty vlogging on YouTube, one can find whole video tutorials on how to create a smokey eye effect, contouring one’s cheeks, bridal makeup tips, cosplay makeup and even stage makeup is covered. Many of these beauty vloggers have millions of subscribers and receive numerous sponsorship deals and endorsements.
Read More: How to Use the FORMULATEXT Function in Excel
In our example, three hypothetical popular beauty vloggers, have received a sponsorship from a big hypothetical beauty brand. The sponsorship entails, each of the three bloggers creating four videos with the sponsor’s makeup products. The beauty brand is now measuring the exposure of their brand through the video views, obtained from the three beauty vloggers. The source data is shown below.
3D Referencing in Excel within a SUM Function
1) The workbook contains four sheets, one called TotalBrandExposure, the three other sheets contain the video counts and totals for each of the vloggers four videos, in cell B7. All the sheets are in the same workbook.
2) On the TotalBrandExposure sheet, we’d like to create a 3D reference that totals the combined amount of views for the three vloggers. We will thus create a SUM formula and use a 3D reference, in order to do this.
3) So, on the TotalBrandExposure sheet in cell A4, we enter the following formula:
We start by referring to the first sheet in the formula, we then add a colon. The last sheet we want to refer too follows the colon. All sheets in between the first sheet and the last sheet will also be included in the calculation. An exclamation mark is then added, and followed by the cell of interest, which in this case is cell B7.
The formula will total each of the amounts in B7 for the first sheet specified, the last sheet specified and all the sheets in between, so in other words Elizabeth’s, Mary’s and Pamela’s total views will be added.
4) Upon pressing CTRL-ENTER, a value of 343251 views is obtained, which is the sum of the total views of the three vloggers.
3D Referencing within a MIN Function
1) 3D referencing is supported by the SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV, STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP and VARPA Functions only.
We are now going to use the MIN Function in conjunction with a 3D reference, in order to see what the minimum value in the range B3: B6, for the three different vloggers is, in other words out of the videos made by the three vloggers, what was the lowest view count.
2) So, in cell A7, on the TotalBrandExposure sheet, we enter the following formula:
This time we are using a 3D reference with a range, and the MIN Function, evaluates the B3: B6 range in the sheets specified in the 3D reference.
3) Upon pressing CTRL-ENTER a value of 1011 is obtained, which is the minimum value across the B3: B6 range in the three sheets.
The structure needed for a 3D reference within a formula is thus:
=Function(First_worksheet: Last_worksheet!cell_of_interest) in the case where it’s a single cell in the different worksheets, that is under evaluation.
=Function(First_worksheet: Last_worksheet!range) in the case where it’s a range of cells in the different worksheets, that is under evaluation.
Creating an External Reference in order to sum data in different workbooks
We now will consider the situation, where we have one master workbook called total exposure and three other different workbooks, containing the view count data from the three individual vloggers. So, basically, we have one master workbook called TotalBrandExposure, one workbook named Elizabeth, the other named Mary and the last one named Pamela. The source data is shown below.
1) So, with all the workbooks open, we start in cell A4, in the TotalExposure workbook. We are now going to use the SUM Function in conjunction with an external reference, in order to see what the total view count for all the three vloggers, is.
2) In the workbook called TotalExposure in cell A4, start the formula with =SUM. Open parenthesis and then navigate to the workbook called Elizabeth and select the cell B7, as shown below.
3) Type a comma, in the formula bar, and then navigate to the workbook called Mary and select the cell B7, as shown below.
4) Type a comma, in the formula bar and then navigate to the workbook called Pamela and select the cell B7 as shown below.
5) While still in the Pamela workbook, close parenthesis using the formula bar and press CTRL-ENTER as shown below.
6) Upon pressing CTRL-ENTER, one is returned back to cell A4, in the TotalExposure workbook and the combined view count is delivered, save your workbook once completed.
Creating an External Reference in order to find the minimum value from different workbooks
1) We are now going to use the MIN Function in conjunction with an external reference, in order to see what the minimum value in the range B3: B6 for the three different vlogger’s data, is.
2) So, with all four workbooks open, in the workbook called TotalExposure in cell A7, start the formula with =MIN. Open parenthesis and then navigate to the workbook called Elizabeth and select the range B3: B6, as shown below.
3) Type a comma, in the formula bar, and then navigate to the workbook called Mary and select the range B3: B6, as shown below.
4) Type a comma, in the formula bar, and then navigate to the workbook called Pamela and select the range B3: B6 as shown below.
5) While still in the Pamela workbook, close parenthesis and press CTRL-ENTER as shown below.
6) Upon pressing CTRL-ENTER, one is returned back to cell A7, in the TotalExposure workbook and the minimum result is delivered, save your workbook once completed.
When opening workbooks, that contain links to other workbooks, one will be asked whether one wants to update the master workbook, in order to reflect any changes made in the linked data, or not to update as shown below. Usually one would want to update the master workbook with the latest data. Note when the source workbooks are not opened (the formula bar will show the external reference with entire path to the workbooks).
1) One can change this default behavior by going to Data>Connections>Edit Links.
2) In the Edit Links dialog box, there are a number of options.
3) One can choose to update the values, change the source to other workbooks, to open the source workbooks (in this case either Elizabeth, Mary or Pamela), break the links to these workbooks, or check the status of the links.
4) One can also change the default start up the behavior of the alert by clicking on Startup Prompt and choosing one of the options. In this case, we will select the Don’t display the alert and update the links as shown.
5) Click Ok and then click Close and save the workbook, and then the next time, you open the workbook TotalExposure, the alert will not be shown, however, the values will be updated with any changes made in the source workbooks Elizabeth, Mary, and Pamela.
And there you have it.
Download Working Files
3D Referencing and External referencing allows one to create extended formulas and incorporate data from multiple sheets or workbooks. Please feel free to comment and tell us if you use 3D references or external references often.
Bonus: We have also included a FREE vlogging Excel template, which will allow you to record vlogging data from your YouTube channel or other vlogging social media channels.
- You can input your name, the name of the channel, and your total number of subscribers, the desired view count milestone, as well as the individual video data in the Table provided.
- The current date is calculated automatically, as is the video with the highest number of views, and the video with the highest number of likes. Total views and total likes are also calculated automatically. You will also be told whether or not you have met your desired viewing milestone, based on the data in the Table.
- The name of the video with the most Likes is delivered automatically as is the name of the video with the most Views. This is especially handy in the cases where one has many rows of Table data.
- When entering individual video data in the table, you can input the name of the video, and the date it was uploaded. The month is automatically delivered in the next column, once you input the date.
- You can choose a category from the category options provided, enter the view count for the video at hand, the number of likes and the like to video ratio is calculated automatically.
- You can also filter the Table using the slicer which filters data on the Category Column.