Excel - How to simultaneously edit and calculate formulas across multiple worksheets Tutorial

In this video, you will learn how to simultaneously edit and calculate formulas across multiple worksheets in Microsoft 365. The video covers the process of grouping worksheets, making changes that apply to all worksheets, formatting tables, and using formulas.
This tutorial will help you efficiently manage and manipulate data across multiple worksheets, saving you time and effort.

3:07
4309 views

Available in Microsoft365

Objectifs :

This section aims to provide a comprehensive understanding of how to manage and format identical tables across multiple worksheets in Excel, including simultaneous editing, formatting, and calculations.

Chapitres :

Introduction to Managing Multiple Worksheets
In Excel, it is common to have identical tables across multiple worksheets, especially when dealing with data that spans several months. This guide will demonstrate how to efficiently manage and format these tables simultaneously, enhancing productivity and ensuring consistency across your data.
Activating Multiple Worksheets
To begin, we will activate the January, February, and March worksheets. Click on the January sheet tab, then press and hold the Shift key while clicking on the March sheet tab. This action groups the three worksheets, allowing any changes made in one worksheet to reflect in the others.
Editing Cell Content Across Worksheets
With the worksheets grouped, navigate to cell A1. Delete the existing text and replace it with 'Item'. This change will automatically update the A1 cell in all three worksheets, demonstrating the power of grouped editing.
Formatting the Table
Next, we will format the table. Select the title cells, then go to the Home tab and click on 'Cell Styles'. To add borders, right-click on the selected cells and choose 'All Borders'. This will enhance the visual presentation of your tables.
Calculating Subtotals
To calculate subtotals across the worksheets, click on cell E2. Enter the formula by typing '=' followed by selecting cell B2, then typing '*' and selecting cell C2. Press Enter to apply the formula. Since the tables are identical, you can copy the formula downwards, and it will apply to all three worksheets.
Formatting Numbers as Currency
To format numbers as currency, select the relevant cells, right-click, and choose 'Format Cells'. Under the Number tab, select 'Accounting' and set the decimal places to 0. This ensures that all monetary values are displayed correctly.
Deleting Columns Across Worksheets
If you need to delete a column, such as column D, right-click on the column header and select 'Delete'. This action will remove the column from all grouped worksheets simultaneously.
Using AutoSum for Total Calculation
To subtotal the quantity and amount including tax, select cells C2 to D6. Click on the AutoSum button in the Home tab. This will calculate the total for all worksheets, providing a quick overview of your data.
Ungrouping Worksheets
Once you have completed your edits, you may want to ungroup the worksheets. Right-click on one of the selected worksheet tabs and choose 'Ungroup Sheets'. This action will make the worksheets independent, allowing for individual modifications.
Conclusion
This section has provided a comprehensive overview of how to manage and format identical tables across multiple worksheets in Excel. By utilizing grouping, formatting, and calculation techniques, you can streamline your workflow and maintain consistency in your data management.

FAQ :

How do I group multiple worksheets in Excel?

To group multiple worksheets, hold down the Shift key and click on the tabs of the worksheets you want to group. This allows you to make simultaneous changes across all selected sheets.

What happens when I make changes to grouped worksheets?

When worksheets are grouped, any changes made in one worksheet will automatically apply to all other grouped worksheets.

How can I format cells in Excel?

To format cells, select the cells you want to format, right-click, and choose 'Format Cells.' You can then select various formatting options, including number formats, borders, and styles.

What is the purpose of the AutoSum feature?

The AutoSum feature allows you to quickly add up a range of numbers in Excel. Simply select the cells you want to sum and click the AutoSum button to get the total.

How do I ungroup worksheets in Excel?

To ungroup worksheets, right-click on one of the selected worksheet tabs and choose 'Ungroup Sheets.' This will make the worksheets independent again.

Quelques cas d'usages :

Simultaneous Data Entry

In a financial reporting scenario, a team can use grouped worksheets to enter data for multiple months at once, ensuring consistency across all reports.

Bulk Formatting

When preparing a presentation, a user can format titles and styles across several worksheets simultaneously, saving time and ensuring uniformity in appearance.

Consolidated Reporting

A project manager can use formulas to calculate totals across multiple worksheets for different departments, allowing for quick consolidation of data for reports.

Budget Tracking

A finance team can maintain separate worksheets for each quarter while using grouped worksheets to update budget figures across all quarters simultaneously.

Data Analysis

Analysts can apply the same formulas to multiple worksheets to analyze trends over time, ensuring that calculations are consistent and accurate across all data sets.

Glossaire :

Worksheet

A single page within an Excel workbook where data is organized in rows and columns.

Cell

The intersection of a row and a column in a worksheet, identified by its column letter and row number (e.g., A1).

Grouped Worksheets

Multiple worksheets that are selected together, allowing simultaneous changes across all selected sheets.

Formula

An expression that calculates the value of a cell, often involving mathematical operations and references to other cells.

Cell Styles

Predefined formatting options in Excel that can be applied to cells to enhance their appearance.

AutoSum

A feature in Excel that automatically adds up a range of numbers in selected cells.

Accounting Format

A number format in Excel that displays numbers as currency, aligning the currency symbols and decimal points.

00:00:06

tables across multiple worksheets carrying

00:00:08

different values in this exercise,

00:00:10

we have 3 spreadsheet. Displaying

00:00:13

the first three months of the year.

00:00:17

The 3 tables are identical they're

00:00:19

built the exact same way carrying

00:00:21

the same column and row headers.

00:00:23

I now want to simultaneously change the

00:00:26

title appearing in the A1 cell of the

00:00:30

January February and March worksheet.

00:00:32

I press on the January sheet tab.

00:00:34

I then press and hold the shift

00:00:36

key down on my keyboard while

00:00:38

clicking on the March Sheet Tab My 3

00:00:41

worksheets are now active an grouped.

00:00:44

From now on all changes made in

00:00:46

one worksheet will simultaneously

00:00:48

change in the other worksheets.

00:00:51

I am now viewing multiple

00:00:53

worksheets on my desktop screen.

00:00:54

If you wish to know more

00:00:56

about this functionality.

00:00:57

Please refer to the video

00:00:59

how to simultaneously.

00:01:00

View multiple worksheets.

00:01:01

I click on the A1 Cell I.

00:01:04

Delete the word name and type.

00:01:06

Instead, the word item.

00:01:08

And now the three worksheets

00:01:10

showed the last modification.

00:01:12

I now want to format my table.

00:01:14

I select the titles and under the home tab.

00:01:17

I click on cell styles.

00:01:20

I want to add borders to my table.

00:01:23

I first select the cells.

00:01:24

Then I do a right Click to show the menu.

00:01:27

I then choose all borders.

00:01:31

I am done formatting the table.

00:01:33

I further want the subtotal to

00:01:35

appear on all my worksheets.

00:01:37

I click on the E2 cell.

00:01:40

I type in the equal sign.

00:01:42

I select the B2 cell.

00:01:44

I type in the star sign.

00:01:46

I further select the C2

00:01:48

cell then press enter.

00:01:50

I then copy over the formula

00:01:52

downwards as the tables are identical,

00:01:54

the formula is applied

00:01:55

to all three worksheets.

00:01:57

I now want to format numbers as currency.

00:02:00

I select them, then I do a right click,

00:02:03

and choose format cells.

00:02:06

Under the number tab I select

00:02:08

the accounting format under

00:02:10

the decimal places field.

00:02:11

I type in 0.

00:02:20

Next I want to delete column D from

00:02:23

all the worksheets I right click

00:02:25

on column D and choose delete.

00:02:28

And with this, I want to subtotal the

00:02:31

quantity and amount included tax of all

00:02:34

worksheets I select cells from C2 to D6.

00:02:37

Under the home tab I click

00:02:40

on the auto sum button.

00:02:42

All is added up the total

00:02:44

appears on all worksheets.

00:02:46

To ungroup the three worksheets,

00:02:48

I do a right click on one

00:02:50

of the selected worksheets.

00:02:51

I then click on ungroup sheets.

00:02:53

Now the three worksheets are independent

00:02:56

they no longer work as a group.

00:02:58

This section has given you a

00:03:00

wide range of ideas on how to

00:03:03

calculate an format tables across

00:03:05

group multiple worksheets.

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