Working on a project that contains visuals such as graphs, charts, and tables usually contains multiple features. 3D reference Excel appears normally when you work on a set of cells on multiple worksheets. Using this simple and fast function combines data from several worksheets having the same structure. 3D reference in Excel is used in place of Consolidate feature of Excel.
How to Generate a 3D Reference Excel?
Generating a 3D reference across different worksheets, the below-given formula can be practiced.
=Function (First_sheet:Last_sheet!cell)
Or,
=Function (First_sheet:Last_sheet!range)
The above formula is used to explain the following dataset. In the dataset, you can see the sales data of different salespeople for 3 years 2019, 2020, and 2021 respectively.
To calculate the total sales amount you can use the 3D reference formula. Sales in 3 years for all the salespersons are added in another sheet Total.
Excel Functions Supporting 3-D References
Below are some Excel functions that let you use 3D references:
SUM – it adds numerical values
AVERAGE – it calculates the arithmetic mean of numbers
AVERAGEA – it calculates the arithmetic mean of values, such as text, numbers, and logical
COUNT – it counts cells with numbers
COUNTA – it counts non-empty cells
MAX – it returns the largest value
MAXA – it returns the largest value such as text and logical
MIN – it finds the smallest value
MINA – it finds the smallest value such as text and logical
PRODUCT – it multiplies numbers
To calculate a sample deviation of a particular set of values, you may use STDEV, STDEVP, STDEVA, or STDEVPA.
To return a sample variance of a particular set of values, you can use VAR, VARA, VARP, and VARPA.
How to Use 3D Cell Reference in Excel?
Example #1
Here we will explain the procedure with the dataset containing the price list of some products given in the Price List sheet.
In worksheet 2 Product Sold, we have managed to find a number of products sold.
In sheet 3 Sales Done, you can calculate the product sales.
Enter the formula in cell B2 “=” and link to sheet 1. Now, select the price given for the first product.
In cell B2, you can see the function bar of Excel links to the first price list sheet. Add an asterisk sign to perform the multiplication function in Excel.
Go back to Sheet 2 and select the total products sale in cell B2.
Now, you will see the function bar of Excel links the second sheet to the B2 cell.
Press ENTER key and you will have the sales done by product 1.
Drag the formula to cell B2 and you will find Excel calculating the sales by default.
The above example explains with two reference sheets Sheet 1 (Price List) and Sheet 2 (Product Sold). Moreover, you can see the calculation of sales done in the third sheet Sales Done.
So, this is it. You may call it a 3-D reference in Excel.
Things to Consider
To find 3D reference Excel, ensure that the data must be in the same pattern in all the worksheets.
Also, Excel refers to the specific cell range when the worksheet is deleted or moved.
Any worksheet you add between the referencing worksheet, you can change the result because Excel links to the particular cell range.
