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.

As a result, a dialog box will pop up.

Select All and click OK.

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

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

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.

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

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.

A dialog box will pop up.

Select Contents and click OK.

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

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

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.