Excel - How to use 3-D reference or tridimensional formulas Tutorial

In this video, you will learn about how to determine the right tool to use in collaborative work using Microsoft 365. The video covers the importance of defining your collaboration needs, identifying the different uses of Microsoft 365, and promoting change within your organization.
This will help you make informed decisions on which tools to use for effective collaboration.

3:01
3775 views

Available in Microsoft365

Objectifs :

This video aims to teach viewers how to use 3D references in Excel to efficiently calculate totals and averages across multiple worksheets, specifically for preparing a quarterly sales report.

Chapitres :

Introduction to 3D References
In this video, we will explore how to utilize 3D references in Excel to simplify calculations across multiple worksheets. This method is particularly useful when dealing with large datasets spread over several sheets, such as a quarterly sales report.
Calculating Total Sales
To calculate the total number of items sold in the first three months of the fiscal year, we will use the SUM function with a 3D reference. The formula we will use is: `=SUM(January:March!C2)` This formula allows us to add the values from cell C2 across the January, February, and March worksheets without having to type each reference individually.
Advantages of 3D References
Using 3D references offers significant advantages, especially when dealing with numerous worksheets. It reduces the time spent on typing and updating formulas, thereby minimizing the risk of errors. Instead of manually entering each cell reference, we can select a range of sheets and perform calculations more efficiently.
Using AutoSum for Quick Calculations
To quickly calculate the total, follow these steps: 1. Click on cell B2. 2. Click on the AutoSum button. 3. Select the January sheet tab. 4. Hold down the Shift key and click on the March sheet tab. 5. Click on cell C2 and press OK. This will sum the values from cell C2 across the selected worksheets.
Copying Formulas for Consistency
Since the January, February, and March tables have identical layouts, we can easily copy the formula downwards to calculate totals for other items. For example, to subtotal the amount under the 'Amount Included Tax' header, simply copy the formula to the right. To maintain the currency format, use the AutoFill options and select 'Fill Without Formatting.'
Calculating Averages with 3D References
In addition to summing values, we can also calculate averages using 3D references. To find the average quantity of sold items and the monthly sales revenue, follow these steps: 1. Click on cell B10. 2. Go to the Home tab and select the AutoSum dropdown. 3. Choose 'Average.' 4. Select the January worksheet, hold down the Shift key, and click on the March sheet tab. 5. Click on cell C7 and press OK. This will give you the average of the total amount included tax, which can also be copied to the right using 'Fill Without Formatting.'
Conclusion
In summary, using 3D references in Excel allows for efficient calculations across multiple worksheets, saving time and reducing errors. By mastering these techniques, you can streamline your data analysis processes, especially for tasks like preparing quarterly sales reports.

FAQ :

What is a 3D reference in Excel?

A 3D reference in Excel allows you to reference the same cell or range of cells across multiple worksheets. This is particularly useful for calculations that involve data from several sheets.

How do I use the AutoSum feature?

To use the AutoSum feature, select the cell where you want the total to appear, click on the AutoSum button, and Excel will automatically suggest a range of cells to sum. You can adjust the range if necessary and press Enter.

What is the difference between a formula and a function in Excel?

A formula is a user-defined expression that performs calculations using cell references, operators, and functions. A function is a predefined formula that performs a specific calculation, such as SUM or AVERAGE.

How can I calculate the average of values across multiple worksheets?

To calculate the average across multiple worksheets, use the AVERAGE function with a 3D reference. Select the cell for the average, click on the AVERAGE function, and then select the range of sheets while holding the Shift key.

What should I do if I have many worksheets to include in a calculation?

Using a 3D reference is highly efficient for calculations involving many worksheets. This method allows you to include multiple sheets in a single formula without having to type each sheet name individually.

How do I maintain formatting when copying formulas in Excel?

To maintain formatting when copying formulas, use the 'Fill Without Formatting' option available in the AutoFill options menu after dragging the fill handle.

Quelques cas d'usages :

Quarterly Sales Reporting

Using 3D references to compile sales data from multiple worksheets for quarterly reports. This method allows for quick calculations of total sales across different months without manually updating each sheet.

Budget Analysis

Applying the average function across multiple worksheets to analyze monthly expenses. This can help in identifying trends and making informed budgeting decisions.

Inventory Management

Utilizing 3D references to calculate total inventory sold across different product categories stored in separate worksheets. This can streamline inventory tracking and reporting.

Financial Forecasting

Employing subtotal calculations across multiple worksheets to summarize financial data for forecasting purposes. This can enhance accuracy in financial planning.

Sales Performance Review

Using 3D references to evaluate sales performance over multiple quarters by aggregating data from various worksheets. This can provide insights into sales trends and performance metrics.

Glossaire :

3D Reference

A method in Excel that allows users to reference the same cell or range of cells across multiple worksheets. This is useful for performing calculations that involve data spread over several sheets.

AutoSum

A feature in Excel that automatically adds up a range of cells. It can also be used to quickly apply other functions like AVERAGE, COUNT, etc.

Formula

An expression in Excel that performs calculations on values in cells. Formulas can include functions, operators, and references to other cells.

Function

A predefined formula in Excel that performs a specific calculation using specific values, called arguments. Examples include SUM, AVERAGE, and COUNT.

Fiscal Year

A one-year period that companies use for financial reporting and budgeting. It may not align with the calendar year.

Subtotal

A total calculated for a subset of data, often used in financial reports to summarize amounts before arriving at a grand total.

Cell

The intersection of a row and a column in a spreadsheet, where data can be entered or calculated.

Currency Format

A formatting option in Excel that displays numbers as monetary values, often including a currency symbol and decimal places.

00:00:05

simultaneously edit an calculate

00:00:07

formulas across multiple worksheets.

00:00:09

We demonstrated how to simultaneously.

00:00:10

Calculate data on 3 separate

00:00:12

worksheets in this video.

00:00:14

We're going to learn how to use

00:00:16

3D reference or how to add the

00:00:19

same cell on multiple worksheets

00:00:21

applying the sum function.

00:00:23

In this exercise,

00:00:24

we want to prepare the quarterly

00:00:26

sales report to find out the

00:00:28

number of items sold on the first

00:00:30

three months of the fiscal year.

00:00:31

To obtain the quantity total we will type

00:00:34

in the following formula equal January.

00:00:37

C2 February C2 March.

00:00:39

C2 the formula seems suitable at first,

00:00:42

it, however, may be inconvenient

00:00:44

with the following scenarios,

00:00:46

the number of sheet tabs is great.

00:00:49

The insertion of additional sheet.

00:00:51

Tabs typing in or updating the

00:00:53

formula then will promptly be time.

00:00:56

Consuming not counting the increased

00:00:58

possibility of Errors in that formula

00:01:01

we're always adding the C2 cell.

00:01:03

However,

00:01:03

we are doing it on different worksheets.

00:01:06

There are huge advantages using the

00:01:08

3D reference to perform this task.

00:01:11

I click on the B2 cell,

00:01:12

then I click on the auto sum button.

00:01:18

I click on the January sheet tab.

00:01:20

I then press and hold the shift key down

00:01:23

while clicking on the March sheet tab.

00:01:26

I then click on the C2 cell and press OK.

00:01:31

Equal some January March indicates

00:01:33

that I am adding values found

00:01:35

on January and March Worksheets.

00:01:37

The C2 cell content is added from

00:01:40

separate worksheets you also have

00:01:42

the option to select several cells.

00:01:45

I used the same formula by copying it over

00:01:48

downward to calculate the other items.

00:01:50

The January February and March

00:01:52

tables have identical layouts.

00:01:54

This means that after the

00:01:56

quantity header located in column.

00:01:58

CI see the amount included

00:02:00

tax located in column D.

00:02:03

To subtotal the amount under

00:02:05

the amount included tax header.

00:02:06

I copy over the formula towards the right.

00:02:10

To keep the currency format under

00:02:12

the total amount included tax header.

00:02:14

I click on the active auto

00:02:16

fill options button,

00:02:17

then I select fill without formatting.

00:02:20

We can also use the average function

00:02:22

as a Tri dimensional formula.

00:02:24

I want to know the average quantity of

00:02:26

sold items and the monthly sales revenue.

00:02:29

I click on the B-10 cell under the home tab.

00:02:32

I click on the Autosum drop down

00:02:35

list and select average.

00:02:37

I click on the January worksheet.

00:02:39

I then press and hold the shift

00:02:41

key down on my keyboard while

00:02:43

clicking on the March sheet tab.

00:02:45

I then click on the C7 cell and press OK.

00:02:49

To obtain the average of total

00:02:50

amount included tax I copy over the

00:02:53

formula towards the right and then

00:02:54

select fill without formatting.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

2497 Views

Video Training Microsoft Excel - Intermediate level

Increase your scope of action with our intermediate tutorials dedicated to formulas, advanced visual enhancement and data analysis with pivo...

ALL COURSES