How to Fill Across Worksheets in Excel (3 Quick Ways)

In this article, we will explain how to Fill across multiple worksheets in Excel, whether filling data, formats, or both.

Overview of Fill Across Worksheets in Excel

Example 1 – Using the Fill All Command to Fill Data and Formats

We can use the Fill command from the Editing group to fill across worksheets. Here, in sheet SalesPerson 1, we have a dataset of sales information in 4 columns.

Sample Dataset of One Sheet

However, in sheet SalesPerson 2, there are only two columns.

Sample Datasheet of Another Sheet

To fill all the data and formats from SalesPerson 1 to SalesPerson 2, we’ll use the Across Worksheets option from the Fill options.

Steps:

Select the portion of the sheet to be filled into the other sheet.
Hold the CTRL key and select the other sheet to fill into, here sheet SalesPerson 2.

After selecting the sheets, they will be grouped.

Open the Home tab >> Go to Editing >> Open Fill >> Select Across Worksheets.

Choosing Across Worksheets Option to Fill Across Worksheets in Excel

As a result, a dialog box will pop up.

Select All and click OK.

Select All to Fill Across Worksheets in Excel

At the bottom of the worksheet tab where the sheet names are displayed, right-click and select Ungroup Sheets.

Ungrouping Sheets

Now, SalesPerson 2 has the same content and formats as SalesPerson 1.

Using Fill All to Fill Across Worksheets in Excel

Example 2 – Using Fill Contents Command to Fill Data Only

Now let’s fill just the contents, not the formats. We’ll use the same dataset in sheet SalesPerson 1 with 4 columns.

Sample Dataset to Fill Only Contents

Again, we’ll fill into sheet SalesPerson 2 using Across Worksheets from the Fill options.

Sample Dataset to Only Fill Contents to Fill Across Worksheets in Excel

Steps:

Select the B4:E16 range.
Hold the CTRL key and select the other sheet to fill into, SalesPerson2.

The selected sheets will be grouped, as can be seen at the top of the worksheet.

Go to the Home tab >> Go to Editing >> Open Fill >> Select Across Worksheets.

Choosing Across Worksheets Option to Fill Across Worksheets in Excel

A dialog box will pop up.

Select Contents and click OK.

Choosing Contents

Right-click anywhere on the Sheet Names bar and select Ungroup Sheets.

Ungrouping Sheets

Now, in SalesPerson 2 only the data has been filled from SalesPerson 1, not the formats.

Applying Fill Contents to Fill Across Worksheets in Excel

Example 3 – Using Fill Format Command to Fill Formats Only

We’ll use Fill formats to fill the formats from one sheet to another sheet. Here, in sheet Employee1 there is a dataset of employee information containing 4 columns.

Formatted Dataset