In this post, you will get to know how to create a 3D reference in Excel. 3D reference is handy when your dataset contains sales or profit details in multiple sheets with names. You need to find the net amount or average along with other mathematical data about profit and sales.

How to Create a 3D Reference in Excel with Names?

Below are some tried and tested methods you can follow to create a 3D reference in Excel. Let’s dive in to see how each method helps you figure out values:

Create a 3D Reference with Excel Average Function:

With the help of the Average function, you can easily create a 3D reference. In 3D reference, you often give names to sheets in which monthly profit data is stored. Here you will get to know the average profit for 4 months with the sheet names. Below are steps you can follow for the complete process:

• First, you need to store the average of the profit and for this create a new sheet.

• Enter the formula given below in cell C5:

=AVERAGE(Month1:Month4!C5) 

• In cell C5 of the sheets, the formula will give an average of the profits from Month 1 to Month 4.

• Press the ENTER key.
• The average profit will appear.

• Now, you need to drag the Fill Handle to Auto Fill the bottom cells.

• A 3D reference in Excel is created by using the Average function. Besides, you will also notice the average of the profits appears.

That’s it!

Create a 3D Reference in Excel with MAX Function:

With the help of the MAX function, you can easily make a 3D reference. Besides, you also give names to multiple sheets in which your data of monthly profit is stored. Let’s assume, you need to analyze the max profit of 4 months with the help of sheet names.

For this, you can follow the steps given below:

• Create a new sheet to store the maximum profit.

• Add the following formula in cell C5:

=MAX(Month1:Month4!C5) 

• You will get the maximum value of the profit available in cell C5 from Month 1 to Month 4.
• Press the ENTER key and you will see the maximum profit value.

• Now, drag the Fill Handle to AutoFill the bottom cells.

So, it is proved that you can easily generate a 3D reference with names in Excel by using the MAX function.

Create a 3D Reference in Excel with COUNT Function:

Similar to the above examples, you can use the COUNT function as well to create a 3D reference. You give names to multiple sheets in which your data of monthly profit is stored. Assume that you need to find the total months you suppose for the profit. For this, the COUNT function is useful to count months.

Let’s dive in to see how it happens:

• Make a new sheet to store the total months.
• Add the following formula in cell C5:

=COUNT(Month1:Month4!C5) 

• You will get the number of profit months given in the sheets from Month 1 to Month 4.
• Press the ENTER key and the number of months will appear.
• Now, drag the Fill Handle to AutoFill the bottom cells.

Hence it is proved that a 3d reference in Excel is created by using the COUNT function. Practice this method carefully.

Create 3D Reference in Excel with VAR Function:

With the VAR function, you can easily create a 3d reference in Excel in which you can give names to sheets. Suppose, you need to find the Variance of 4 months’ profit with the sheet names and for this, you need to follow the steps given below:

• First, make a new sheet in which you can store the profit Variance.

• Add the following formula in cell C5:

=VAR(Month1:Month4!C5) 

• In cell C5, the formula gives the profits Variance given in the sheets from Month 1 to Month 4.
• Press the ENTER key and the Variance of profit will appear.

• Now, drag the Fill Handle to AutoFill the bottom cells.

Eventually, you can see the VARIANCE function is helpful in creating a 3D reference in Excel with names.

Things to Consider:

• Your Excel sheets must be in order.
• Besides the above-mentioned functions, you can use other functions as well to generate a 3D reference in Excel by creating a similar type of formula used above. These functions are as below:

AVERAGEA Function

COUNTA Function

MAXA Function

MIN Function

MINA Function

STDEVA Function

STDEVP Function

STDEVPA Function

VARA Function

VARP Function

VARPA Function

Summary:

This post entirely explains how you can easily manage to create a 3D reference in Excel with names by using multiple functions. Each method explains almost a similar procedure except for a few different steps. You need to practice each method so that you can understand the difference between each approach.