Excel - Combine data from multiple worksheets Tutorial

In this video, you will learn how to combine data from multiple worksheets using Microsoft 365. The video covers the process of merging data from different worksheets in Excel, allowing you to streamline your data analysis and reporting.
This skill will help you save time and improve your productivity when working with large datasets.

2:50
4131 views

Available in Microsoft365

Objectifs :

This video aims to teach viewers how to consolidate data from multiple worksheets into a master worksheet using 3D references in Excel. It covers the steps to summarize and report results effectively, ensuring that the master worksheet updates automatically when source data changes.

Chapitres :

Introduction to Data Consolidation
In this section, we explore the concept of data consolidation in Excel. Consolidating data allows users to summarize and report results from separate worksheets into a single master worksheet. This is particularly useful when dealing with data from different sources or worksheets that may have varying layouts.
Understanding 3D References
3D references in Excel provide the advantage of combining data from multiple worksheets, even if they have different table layouts. The worksheets can either be part of the master worksheet's workbook or from other workbooks, allowing for flexible data management.
Setting Up the Master Worksheet
In this exercise, we will create a summary worksheet that serves as the master worksheet. We will import data from three different worksheets: Factory 1, Factory 2, and Factory 3. To begin, click on cell A1 in the summary worksheet and select the 'Consolidate' option.
Using the Consolidate Dialog Box
Within the Consolidate dialog box, choose the function you want to apply to your data. In this case, we will use the 'Sum' function. Next, specify the range of cells to consolidate. To select ranges from other workbooks, click the 'Browse' button and navigate to the desired worksheets.
Adding Ranges from Different Worksheets
For Factory 1, select the range from A1 to A4 and press the 'Add' button. Repeat this process for Factory 2, where the range is identical. For Factory 3, select the range from A1 to A6 and press 'Add'. Ensure to check the 'Top Row' and 'Left Column' boxes to maintain the header structure.
Linking Source Data
To ensure that the master worksheet updates automatically when the source data changes, check the 'Create links to source data' box. This links the data from the factory worksheets to the master worksheet.
Viewing Consolidated Results
After consolidation, the results will be displayed in the master worksheet. You may notice some rows are hidden, indicated by a plus sign next to row 5. Clicking on this plus sign will expand the consolidated data. You can toggle between different levels of detail to view the summary or the detailed breakdown.
Updating Source Data
To demonstrate the linking functionality, click on the Factory 1 worksheet and enter a new value (e.g., 500,000) in cell B2. After pressing enter, return to the summary worksheet to observe that the total amount has been updated automatically, confirming that the source data is properly linked.
Conclusion
In summary, this video has provided a comprehensive guide on how to consolidate data from multiple worksheets into a master worksheet using 3D references in Excel. By following these steps, users can efficiently manage and report data, ensuring that their master worksheet remains up-to-date with any changes made in the source worksheets.

FAQ :

What is the purpose of consolidating data in Excel?

Consolidating data in Excel allows users to combine information from multiple worksheets into a single master worksheet, making it easier to analyze and report results.

How do I create a 3D reference in Excel?

To create a 3D reference, you can use the consolidation feature in Excel, selecting ranges from different worksheets and applying a function to combine the data.

What happens when I link source data to the master worksheet?

When you link source data to the master worksheet, any changes made in the source worksheets will automatically update the master worksheet, ensuring that the data is always current.

Can I consolidate data from different workbooks?

Yes, you can consolidate data from different workbooks by selecting the appropriate ranges in the consolidation dialog box and using the browse button to navigate to the other workbooks.

What should I do if the cell ranges in my worksheets are different?

If the cell ranges in your worksheets are different, you can select the specific ranges for each worksheet during the consolidation process and add them individually.

Quelques cas d'usages :

Financial Reporting

A finance team can use data consolidation to combine monthly financial reports from different departments into a master worksheet, allowing for a comprehensive overview of the company's financial status.

Sales Data Analysis

Sales managers can consolidate sales data from various regional offices into a master worksheet to analyze overall performance and identify trends across different markets.

Project Management

Project managers can consolidate progress reports from multiple project teams into a master worksheet to track overall project status and resource allocation.

Inventory Management

An inventory manager can consolidate stock levels from different warehouses into a master worksheet to monitor total inventory and make informed restocking decisions.

Performance Tracking

HR departments can consolidate employee performance reviews from various teams into a master worksheet to evaluate overall employee performance and identify areas for improvement.

Glossaire :

Consolidation

The process of combining data from multiple sources into a single master worksheet for analysis and reporting.

3D Reference

A reference that allows users to consolidate data from multiple worksheets, even if they have different layouts.

Master Worksheet

The primary worksheet where consolidated data from other worksheets is displayed and analyzed.

Function Dropdown List

A menu in the consolidation dialog box that allows users to select the mathematical function (e.g., SUM) to apply to the data.

Source Data

The original data from which the master worksheet pulls information for consolidation.

Linking

The process of connecting the master worksheet to the source data so that updates in the source data automatically reflect in the master worksheet.

Header Row

The top row of a worksheet that typically contains labels for each column of data.

Cell Range

A selection of multiple cells in a worksheet, defined by the starting and ending cell references (e.g., A1:A4).

00:00:05

out how to combine data.

00:00:07

To summarize and report results

00:00:08

from separate worksheets,

00:00:09

you can consolidate data from each

00:00:12

worksheet into a master worksheet.

00:00:14

3D reference offers the advantage of

00:00:16

combining data from multiple unidentical

00:00:17

worksheets with different table layouts.

00:00:19

Worksheets can be part of the

00:00:21

master worksheet's workbook or

00:00:23

part of the other workbooks.

00:00:25

The data is consolidated

00:00:26

so that it can be updated

00:00:29

in a similar fashion.

00:00:30

In this exercise,

00:00:31

the summary worksheet is the

00:00:34

master worksheet and we're

00:00:35

importing data from factory 1

00:00:37

factory 2 and factory 3 worksheets.

00:00:40

Using these summary worksheet,

00:00:42

I click on the A1 cell under data.

00:00:45

I select consolidate.

00:00:46

Under the consolidate dialog box

00:00:48

and the function dropdown list.

00:00:51

I choose the formula.

00:00:52

I want to apply to my data in this scenario.

00:00:55

I leave the sum function.

00:00:58

Under the reference field,

00:00:59

i select the range of cells

00:01:01

i want to consolidate to select a

00:01:03

range of cells located in other

00:01:05

workbooks, click on the browse button.

00:01:07

I click once under the reference field.

00:01:10

I then select the factory one worksheet.

00:01:12

I further select the range of cells,

00:01:15

A1 to cell 4I then press the add button.

00:01:19

Afterwards,

00:01:19

I click on the factory 2 worksheet.

00:01:22

Since the range of cells is identical.

00:01:24

I just press the add button.

00:01:27

After clicking on factory 3,

00:01:29

I see that the range of cells is different.

00:01:32

I select again, the cells from A1 to cell 6.

00:01:35

I then press the add button.

00:01:38

To keep the same header row and column,

00:01:40

I checked the top Row and left column boxes.

00:01:44

I also want to link the source

00:01:46

data to the master worksheet.

00:01:48

This way when the values change

00:01:50

in the factory.

00:01:51

One factory 2 an factory 3 worksheets the

00:01:54

master worksheet is updated automatically.

00:01:56

For this reason,

00:01:57

I checked the create links to

00:02:00

source data box.

00:02:01

Now the results are displayed you

00:02:03

see that some rows are hidden on

00:02:05

the upper left side of the window.

00:02:07

There is a plus sign next to row 5.

00:02:10

When I click on the plus sign the

00:02:13

consolidated data is broken down.

00:02:16

When I click on the level 2 all the

00:02:19

consolidated data is broken down.

00:02:21

And when I click on the level one,

00:02:23

the consolidated summary is displayed

00:02:25

when I click on the value 200,000,

00:02:28

the formula bar shows the

00:02:30

source worksheet cell.

00:02:31

I click on the factory one worksheet.

00:02:34

I then type in the amount 500,000

00:02:36

in the B2 cell and press enter.

00:02:39

When I click on the summary worksheet.

00:02:41

I see that the total amount

00:02:43

has changed and been updated.

00:02:45

The source data has been properly

00:02:46

linked to the master worksheet.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

2679 Views

Video Training Microsoft Excel - Advanced level

With our "advanced" tutorials, discover and master the full potential of Excel, using complex functions, macros and the power of DCTs and Po...

ALL COURSES