3D Referencing & External Reference in Excel

What Is 3D Referencing in Excel?

In Excel, you can use the 3D reference feature to select the same cell from multiple worksheets. By simply specifying the worksheet name, you can reference the same cell or a range of cells across different sheets.

For example, if you have four worksheets named Jan, Feb, Mar, and Apr, each containing numerical data, you can add values from the same cell in all these sheets without manually selecting each one. The range for the SUM function must be Jan: Apr!C3 if you wish to sum the value of cell location C3.

You can get the list of functions compatible with 3D referencing in Excel.

The structure needed for a 3D reference within a formula is:

=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.
Or
=Function(First_worksheet: Last_worksheet!range) in the case where it’s a range of cells in the different worksheets, that is under evaluation.

Introduction to Dataset

In our example, three hypothetical popular beauty vloggers Elizabeth, Mary, and Pamela, 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 its brand through the video views, obtained from the above three beauty vloggers. The source data is shown below.

The workbook Utilizing 3D References contains five sheets, one named TotalBrandExposure, and the three other sheets contain the view counts for each of the vlogger’s four videos, and the total views in cell C9. There is another sheet named Chart. All the sheets are in the same workbook.

Using 3D Referencing in Excel: 3 Examples

Example 1 – 3D Referencing with SUM Function

Steps:

Select cell D4 in the TotalBrandExposure sheet.
Enter the following formula.

=SUM(Elizabeth:Pamela!C9)

We start by referring to the first sheet in the formula, and added a colon. The last sheet we want to refer to 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 added, followed by the cell of interest, which in this case is cell C9.

The formula will total each of the counts in C9 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.

We obtain a value of 343251 views upon pressing ENTER, which is the sum of the total views of the three vloggers.

Example 2 – 3D Referencing with MIN Function

Steps:

In the TotalBrandExposure sheet, select cell D6.
Enter the following formula into that cell.

=MIN(Elizabeth:Pamela!C5:C8)

We are using a 3D reference with a range, and the MIN function, evaluates the C5:C8 range in the sheets specified in the reference.

Press ENTER to get a value of 1011, which is the minimum value across the C5:C8 range in the three sheets.

Example 3 – Inserting Chart in Excel

Steps:

Add a new blank worksheet Chart in the workbook.

Go to the Insert tab.
Click on the Insert Column or Bar Chart drop-down icon on the Charts group.
Select 2-D Clustered Column from the available options.

It inserts a blank chart in the worksheet.

Right-click anywhere on the chart area.
From the context menu, choose the option Select Data.

The Select Data Source dialog box opens.

In the Chart data range box, enter the reference of cells in the B4:C8 range in the Elizabeth worksheet.
Click OK.

We will get the chart of Elizabeth’s Organic Views for each video.

When Does 3D Referencing Change?

Example 1 – If You Insert New Worksheet

Let’s say you have created a copy of the worksheet Elizabeth between the worksheets Elizabeth and Mary. Excel will name it Elizabeth (2) automatically as there is another sheet available with the same name in the corresponding workbook.

Go to the worksheet TotalBrandExposure and you’ll find a new value of Total Brand Exposure.

It happens because we put a new worksheet between the first and last sheet of the reference. So, Excel takes it into the calculation. As a result, the Total Organic Views of Elizabeth is getting summed up twice and created the final value of 499163 instead of 343251.

Example 2 – If You Move Worksheet

Let’s assume, you moved the worksheet Mary from the middle of Elizabeth and Pamela to the right of Pamela.

The Total Brand Exposure gets decreased.

Because we altered the position of sheet Mary from between the first sheet and the last sheet of the reference, Excel excludes it from the calculation. It displays the value as 248055 instead of 343251.

How to Create a 3D Reference in Excel with Names

Steps:

Go to the Formulas tab.
Select Define Name on the Defined Names group.

The New Name dialog box pops up.

Enter the Name as Organic_Views.
Enter=Elizabeth:Pamela!$C$5:$C$8 in the box of Refers to.
Click OK.

Go to the worksheet TotalBrandExposure.
Select cell D6 and enter the following formula.

=MIN(Organic_Views)

Press ENTER.

We’re getting the same result as Example 02 using a predefined name.

2 Examples of Applying External Reference in Excel

We have one master workbook called Applying External Reference and three other different workbooks, containing the view count data from the three individual vloggers. We have one master workbook called Applying External Reference, one workbook named Elizabeth, the other named Mary, and the last one named Pamela. The sample source data is shown below.

Example 1 – Calculating Sum from Different Workbooks

Steps:

Open all four workbooks and keep them in different tabs.
In the worksheet called TotalExposure, in cell C4, start the formula with =SUM.
Open parenthesis and navigate to the workbook called Elizabeth and select cell C9, as shown below.

Insert a comma in the formula bar, and navigate to the workbook called Mary and select cell C9.

Enter a comma in the formula bar and navigate to the workbook called Pamela and select cell C9.

While still in the Pamela workbook, close the parenthesis in the formula bar and press ENTER.

It will return back to cell C4, in the TotalExposure worksheet and the combined view count is displayed.
Save your workbook once completed.

Example 2 – Finding the Minimum Value from Different Workbooks

Steps:

Open all four workbooks.
In the worksheet called TotalExposure, in cell C6, start the formula with =MIN.
Open parenthesis and navigate to the workbook called Elizabeth and select the range C5:C8, as shown below.

Enter a comma in the formula bar, and navigate to the workbook called Mary and select the range C5:C8.

Insert a comma in the formula bar, and navigate to the workbook named Pamela and select the range C5:C8.

While still in the Pamela workbook, close the parenthesis and press ENTER.

It will return back to cell C6 in the TotalExposure worksheet and the minimum result is displayed.
Save your workbook once completed.

Updating Links of External Reference

When opening workbooks that contain links to other workbooks, it will ask if update needs to be performed, in order to reflect any changes made in the linked data or not to update as shown in the image below.

Note: When the source workbooks are not opened the formula bar will show the external reference with the entire path to the workbooks.
To update the workbook always, follow the steps below.

Steps:

Go to the Data tab.
Click on Edit Links on the Queries & Connections group.

The Edit Links wizard will open.

Select Startup Prompt on the left-below side of the box.

The Startup Prompt dialog box pops up.

Select Don’t display the alert and update links.
Click OK.

It returns to the Edit Links wizard.

Click on Close.

Save the workbook.

The next time you open the workbook Applying External Reference, the alert will not be shown, however, the values will be updated with any changes made in the source workbooks.

Bonus: Free Template

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.

Template Description:

You can input your name, the name of the channel, your total number of subscribers, and the desired view count milestone, as well as the individual video data in the Table provided.
This template calculated the current date, and the video with the highest number of views, and the video with the highest number of likes. We can also get the Total views and Total likes calculated. You will also get whether or not you have met your desired Viewing Milestone, based on the data in the Table.
It will deliver the name of the video with the most Likes and the name of the video with the most Views.
You can enter individual video data in the table and input the Name of the Video and the Date when you uploaded it. Once you input the Date, you can get the Month in the next column.
You can choose a category from the Category options, and enter the view count for the video at hand, and the number of likes. You’ll find the like-to-view ratio.
You can also filter the Table using the slicer which filters data on the Category Column.