Excel - How to link cells and calculate formulas across multiple worksheets Tutorial

In this video, you will learn how to link cells and calculate formulas across multiple worksheets in Microsoft Excel.
The video demonstrates the process of linking data from one worksheet to another within the same workbook or multiple workbooks.
By using the fill handle, you can quickly fill adjacent cells with a series of data.
This technique is useful for summing totals and calculating data across different worksheets.
By following the steps shown in the video, you will be able to efficiently perform calculations and streamline your data analysis in Excel.
This knowledge will help you improve your productivity and accuracy when working with multiple worksheets in Excel.

2:10
4265 views

Available in Microsoft365

Objectifs :

This video aims to teach users how to link data and calculate formulas across multiple worksheets within a single workbook or across multiple workbooks in Excel. It demonstrates practical steps for linking cells, using formulas, and efficiently filling data across adjacent cells.

Chapitres :

Introduction to Linking Data in Excel
Excel provides powerful features that allow users to link data and perform calculations across multiple worksheets. This functionality is essential for managing and analyzing data efficiently, especially when dealing with large datasets spread across different sheets.
Linking Cells Between Worksheets
To link data from one worksheet to another, follow these steps: 1. Navigate to the target worksheet (e.g., 'Quarter Two'). 2. Click on the cell where you want to display the linked data (e.g., cell B4). 3. Type the equal sign '=' to start the formula. 4. Click on the tab of the source worksheet (e.g., 'Quarter One'). 5. Select the cell containing the data you want to link (e.g., cell B6 for Australia). 6. Press 'Enter' to complete the formula. This process allows you to pull data from one sheet into another seamlessly.
Understanding the Formula Breakdown
The formula created consists of two main components: - The sheet name (e.g., 'Quarter One') indicates where Excel is searching for the value. - The cell reference (e.g., B6) specifies the exact location of the data within that sheet. This structure allows for dynamic updates; if the data in 'Quarter One' changes, 'Quarter Two' will automatically reflect that change.
Efficiently Filling Data Across Cells
To avoid repetitive steps when linking data for multiple countries, you can use the fill handle feature: - After entering the formula for the first country, click and hold the fill handle (a small square at the bottom-right corner of the selected cell). - Drag the fill handle to the right to fill adjacent cells with the corresponding formulas for other countries. This method ensures that all linked cells maintain the correct references relative to their positions.
Summing Data Across Worksheets
When working with balance sheets, you can sum data from all quarters: 1. Click on the cell where you want the subtotal to appear. 2. Type the equal sign '=' and select the corresponding cell from the 'Quarter One' sheet (e.g., B6). 3. Repeat this for other worksheets, ensuring that the countries are in the same order across all sheets. 4. Use the fill handle to quickly apply the formula to adjacent cells, including the total column. This approach streamlines the process of calculating totals across multiple sheets.
Calculating Data Across Workbooks
You can also use similar formulas to calculate data across different workbooks. However, ensure that the workbooks are open before inserting the formula. The process is similar to linking cells within a single workbook, but you will need to reference the workbook name in the formula.
Conclusion
Linking data and calculating formulas across multiple worksheets and workbooks in Excel enhances data management and analysis. By mastering these techniques, users can efficiently handle complex datasets, ensuring accuracy and saving time in their calculations.

FAQ :

What is the purpose of linking cells in Excel?

Linking cells allows you to reference data from one worksheet in another, ensuring that any updates to the source data are automatically reflected in the linked cells.

How do I create a formula that references another worksheet?

To create a formula that references another worksheet, start by typing an equal sign in the desired cell, then click on the tab of the worksheet you want to reference, and select the cell containing the data you need.

What is the fill handle and how do I use it?

The fill handle is a tool in Excel that allows you to quickly copy data or formulas to adjacent cells. Click and hold the fill handle, then drag it across the cells you want to fill.

Can I link data from multiple workbooks?

Yes, you can link data from multiple workbooks in Excel. However, you must have the other workbooks open to insert the formula that references them.

What is a subtotal in Excel?

A subtotal in Excel is a calculated sum of a specific range of data, often used to summarize totals for different categories or groups within a dataset.

Quelques cas d'usages :

Financial Reporting

In financial reporting, linking cells across multiple worksheets allows accountants to consolidate data from different quarters and generate comprehensive reports without manually updating figures.

Sales Data Analysis

Sales analysts can use linked cells to compare sales performance across different regions or time periods, enabling them to quickly assess trends and make informed decisions.

Project Management

Project managers can link data from various project phases in separate worksheets to track progress and budgets, ensuring that all team members have access to the most current information.

Inventory Management

In inventory management, linking worksheets can help businesses keep track of stock levels across multiple locations, allowing for better resource allocation and planning.

Budgeting

When creating budgets, users can link data from previous years' worksheets to forecast future expenses and revenues, streamlining the budgeting process and improving accuracy.

Glossaire :

Linking Cells

The process of connecting data from one worksheet to another within Excel, allowing for dynamic updates when the source data changes.

Workbook

A file in Excel that contains one or more worksheets. Each workbook can hold various types of data and calculations.

Worksheet

A single spreadsheet within a workbook, consisting of rows and columns where data is entered and manipulated.

Fill Handle

A small square at the bottom-right corner of a selected cell in Excel that allows users to drag and fill adjacent cells with a series of data or formulas.

Subtotal

A sum of a specific set of data within a larger dataset, often used to summarize totals for categories or groups.

Formula

An expression in Excel that performs calculations on values in cells, starting with an equal sign (=).

Cell Reference

The unique identifier for a cell in Excel, typically represented by the column letter and row number (e.g., B6).

00:00:06

formulas across multiple worksheets

00:00:07

within 1 workbook or multiple workbooks.

00:00:10

You can get data from one worksheet

00:00:13

to another in Excel this is called

00:00:16

linking cells in separate worksheets.

00:00:18

Under the quarter, two worksheet.

00:00:20

I want to show the total from the

00:00:22

prior quarter for all countries.

00:00:24

I click on the B4 cell.

00:00:26

I type in the equal sign afterwards.

00:00:29

I click on the quarter.

00:00:30

One sheet tab and click on

00:00:32

the total cell for Australia.

00:00:34

I then press enter let's see

00:00:36

how the formula is broken down.

00:00:39

Quarter one is the sheet name

00:00:40

where Excel is searching for the

00:00:42

value be 6 is the cell where the

00:00:44

value is displayed in the quarter,

00:00:46

two sheet.

00:00:46

To avoid repeating the same steps for all

00:00:49

the countries shown on my spreadsheet.

00:00:51

I placed them all in the same order.

00:00:53

I left click,

00:00:54

and hold the mouse button down while

00:00:56

dragging the fill handle to the right

00:00:59

to fill adjacent cells with a series of data.

00:01:01

Now I take a closer look at the formulas.

00:01:04

Under the Australia column I see

00:01:06

the output result from the quarter,

00:01:08

one sheet B6 cell and so on.

00:01:11

As all my spreadsheets are built,

00:01:13

the same.

00:01:14

I proceed identically with the

00:01:15

other worksheets.

00:01:22

Using the balance sheet worksheet,

00:01:24

I put the subtotal under each

00:01:26

country summing all the quarters.

00:01:28

In the before cell I type in the equal sign.

00:01:31

I press the quarter.

00:01:33

One sheet tab and click on the B6 cell.

00:01:36

I do the same with the other worksheets.

00:01:41

As all the quarters,

00:01:42

an balance sheet worksheets.

00:01:43

Display the countries in the same order.

00:01:45

I left click, and hold the mouse

00:01:47

button down while dragging the

00:01:49

fill handle to the right to fill

00:01:51

adjacent cells with a series of data,

00:01:53

including the total column.

00:01:54

I do the same with the yearly

00:01:57

sales for Co Tesda province.

00:01:59

You can use the same type of formula to

00:02:02

calculate data across other workbooks.

00:02:04

You must open them 1st in

00:02:05

order to insert the formula.

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