Meraz Al Nahian
The article provides 7 methods on how to create a 3d reference in excel with names. 3d reference is very useful when you have sales or profit details in different Excel sheets with names and you want total amount or average or other statistical information about sales or profit.
In this article, we have profit data of various automobile companies of different months. We also keep the data for individual months in respective sheets- Month1, Month2, Month3, and Month4.
Download Practice Workbook
7 Ways to Create a 3d Reference in Excel with Names
1. Using Excel AVERAGE Function to Create a 3d Reference with Names
The AVERAGE Function is one of the functions that we use to create a 3d Reference. We give different names to different sheets where we stored the monthly profit. Now we want to find the average of profit for 4 months using the sheet names. Let’s go through the process below for a better understanding.
Steps:
- First, create a new sheet to store the average of the profit.
- Now type the following formula in cell C5.
=AVERAGE(Month1:Month4!C5)
The formula will return the average of the profits that are mentioned in cell C5 of all sheets from Month1 to Month4.
- Press the ENTER button and you will see the average profits of the Porsche
- After that, apply the Fill Handle to AutoFill the lower cells.
Thus you can create a 3d reference in excel with names with the help of AVERAGE Function and determine the averages of the profits of these companies.
2. Applying Excel SUM Function to Create a 3d Reference with Names
Another function that we can use to create a 3d Reference is the SUM Function. We give different names to different sheets where we stored the monthly profit. Now we want to find the sum of profit for 4 months using the sheet names. Let’s go through the procedure below.
Steps:
- First, create a new sheet to store the sum of the profit.
- Now type the following formula in cell C5.
=SUM(Month1:Month4!C5)
The formula will return the sum or total amount of the profits that are mentioned in cell C5 of all sheets from Month1 to Month4.
- Press the ENTER button and you will see the sum of profits of the Porsche
- After that, apply the Fill Handle to AutoFill the lower cells.
Thus you can create a 3d reference in excel with names with the help of SUM Function and determine the sum of the profits of these companies.
Read More: How to Use SUM and 3D Reference in Excel (Detailed Analysis)
3. Implementing MAX Function to Generate 3d Reference in Excel with Names
The MAX Function is another function that we can use to create a 3d Reference. We give different names to different sheets where we stored the monthly profit. Now we want to find the maximum profit for 4 months using the sheet names. Let’s go through the process below for a better understanding.
Steps:
- First, create a new sheet to store the maximum amount of the profit.
- Now type the following formula in cell C5.
=MAX(Month1:Month4!C5)
The formula will return the maximum amount of the profits that are mentioned in cell C5 of all sheets from Month1 to Month4.
- Press the ENTER button and you will see the maximum profit of the Porsche
- After that, apply the Fill Handle to AutoFill the lower cells.
Thus you can create a 3d reference in excel with names with the help of MAX Function and determine the maximum profits of these companies during the period.
Read More: 3D Referencing & External Reference in Excel
4. Creating 3d Reference in Excel by Using PRODUCT Function with Names
Another function that we can use to create a 3d Reference is the PRODUCT Function. We give different names to different sheets where we stored the monthly profit. Now we want to find the product of profits for 4 months using the sheet names. Let’s go through the procedure below.
Steps:
- First, create a new sheet to store the product of the profit.
- Now type the following formula in cell C5.
=PRODUCT(Month1:Month4!C5)
The formula will return the product of the profits that are mentioned in cell C5 of all sheets from Month1 to Month4.
- Press the ENTER button and you will see the product of profits of the Porsche
- After that, apply the Fill Handle to AutoFill the lower cells.
The numbers you see are ridiculously large. So we want to show them in a convenient way. We will convert these numbers to Quintillion (10^18) format This step is optional. So I’ll describe the process shortly.
- First, go to Number Format >> More Number Formats…
- Select Custom.
- Type $0.00,,,,,,Q in the “Type:” section
- Click OK.
After that, you will see the product of profits in Quintillion format. Usually, the product of data is useful for statistical applications.
Thus you can create a 3d reference in excel with names with the help of product Function and determine the product of the profits of these companies.
5. Utilizing Excel COUNT Function to Create a 3d Reference with Names
The COUNT Function is one of the functions that we can use to create 3d References. We give different names to different sheets where we stored the monthly profit. Now if we want to count the number of months that we considered for the profit period, we can use the COUNT Function to count these months using the sheet names. Let’s go through the process below for a better understanding.
Steps:
- First, create a new sheet to store the number of months.
- Now type the following formula in cell C5.
=COUNT(Month1:Month4!C5)
The formula will return the number of months or profit periods that are mentioned in all sheets from Month1 to Month4 which is 4 for all companies.
- Press the ENTER button and you will see the number of months.
- After that, apply the Fill Handle to AutoFill the lower cells.
Thus you can create a 3d reference in excel with names with the help of the COUNT Function and determine the profit period of these companies.
6. Using STDEV Function Function to Create a 3d Reference
Another function that we can use to create a 3d Reference is the STDEV Function. We give different names to different sheets where we stored the monthly profit. Now we want to find the Standard Deviation of profit for 4 months using the sheet names. Let’s go through the procedure below.
Steps:
- First, create a new sheet to store the Standard Deviation of the profit.
- Now type the following formula in cell C5.
=STDEV(Month1:Month4!C5)
The formula will return the Standard Deviation of the profits that are mentioned in cell C5 of all sheets from Month1 to Month4.
- Press the ENTER button and you will see the Standard Deviation of profits of the Porsche
- After that, apply the Fill Handle to AutoFill the lower cells.
Thus you can create a 3d reference in excel with names with the help of STDEV Function and determine the Standard Deviation of the profits of these companies.
7. Utilizing Excel VAR Function Function to Create a 3d Reference
The VAR Function is one of the functions that we may use to create 3d References. We give different names to different sheets where we stored the monthly profit. Now we want to find the Variance of profit for 4 months using the sheet names. Let’s go through the process below for a better understanding.
Steps:
- First, create a new sheet to store the Variance of the profit.
- Now type the following formula in cell C5.
=VAR(Month1:Month4!C5)
The formula will return the Variance of the profits that are mentioned in cell C5 of all sheets from Month1 to Month4.
- Press the ENTER button and you will see the Variance of profits of the Porsche
- After that, apply the Fill Handle to AutoFill the lower cells.
Thus you can create a 3d reference in excel with names with the help of VAR Function and determine the Variances of the profits of these companies.
Things to Remember
- You should maintain the order of your excel sheets. For example, if you keep your sheets in the following order and use the formula we used in this article, you will get incorrect results.
- There are other functions that you can use to generate a similar types of formulas in this article to create 3D References. They are given below.
-
- AVERAGEA Function
- COUNTA Function
- MAXA Function
- MIN Function
- MINA Function
- STDEVA Function
- STDEVP Function
- STDEVPA Function
- VARA Function
- VARP Function
- VARPA Function
Practice Section
Here, I’m giving you the dataset that we used in this article so that you can practice these formulas on your own.
Conclusion
We can summarize the bottom line as if you go through the whole article, you’ll understand the basic concept of how to create a 3d reference in excel with names. The application of 3d reference is very useful because it saves us from the waste of time. If you have any feedback regarding this article, please leave them in the comment box. It will help me enrich my upcoming article.
