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

Get FREE Advanced Excel Exercises with Solutions!

In this article, I’m going to explain how to fill across worksheets in Excel. By using fill across you can fill contents, formats, and both from multiple sheets which is an easy and quick way to copy format and contents across sheets. To make it clearer, I’m going to show you different examples with different sorts of datasets. So, without further ado let’s begin the tutorial. Here, we will fill contents and formats across two sheets like the gif below.

Table of Contents hide

Fill Across Worksheets in Excel: 3 Ways

1. Filling Data with Format Across Worksheet Using Fill All Command

2. Applying Fill Contents Command to Fill Data Only

3. Utilizing Fill Format Command

Practice Section

Conclusion

Fill Across Worksheets in Excel: 3 Ways

In this article, we will discuss 3 ways to fill across worksheets in Excel. Firstly, we will fill both the contents and formats between two sheets. Secondly, we will fill only contents. Finally, we will emulate the format of a worksheet to another.

1. Filling Data with Format Across Worksheet Using Fill All Command

You can use the FILL command from the Editing group to fill across worksheets. Here, In SalesPerson 1 I have taken a dataset of sales information with 4 columns.

On the other hand, in SalesPerson 2 there are only two columns.

Now, to fill all the content and format from SalesPerson 1 sheets to SalesPerson 2 sheets I will use the Across Worksheets from the Fill options.

Steps:

To use this, first select the portion of the sheet from where you will fill across the other sheets.
Then, hold the CTRL key and select the other sheet to fill across.
Here, I selected the sheet SalesPerson 2.
After selecting the sheets, they will be a group of sheets.
Thereafter, open the Home tab >> Go to Editing >> open FILL >> then select Across Worksheets.

As a result, a dialog box will pop up. From there select All and finally click OK.

After that, st the bottom of the worksheet tab where sheets are selected, right-click on the mouse and select Ungroup Sheets.

In the meantime, you will see that SalesPerson 2 has the same content along with formats like SalesPerson 1.

2. Applying Fill Contents Command to Fill Data Only

From Across Worksheets, the Fill Contents will only fill the contents not the formats. Here, In SalesPerson 1 there is a dataset of sales information containing 4 columns.

But in SalesPerson 2 there are only two columns. Now, to fill contents from SalesPerson 1 to SalesPerson 2 I am going to use the Across Worksheets from the Fill options.

Steps:

First, select the contents of the sheet from where you will fill the other sheets.
Here, we will select the B4:E16 range.
Now, hold the CTRL key and select the other sheet to fill across.
In this case, I selected the sheet SalesPerson2.
Succeeding selected sheets will form a group. You can see it at the top of the worksheet.
Then, open the Home tab >> Go to Editing >> open FILL >> then select Across Worksheets.

Consequently, a dialog box will pop up.
From there, select Contents and finally click OK.

After that, at the bottom of the worksheet tab where sheets are selected, right-click on the mouse and select Ungroup Sheets.

Finally, we will find that in SalesPerson 2 only the contents are filled from SalesPerson 1.

3. Utilizing Fill Format Command

Using the Fill Formats you can fill the formats from one sheet to another sheet. Here, In Employee1 there is a dataset of employee information containing 4 columns.

On the contrary, in Employee2 there also has a dataset of employee information containing the same 4 columns but without any formats.

Now, to Fill Formats from sheets Employee1 to Employee2 sheet from the Fill options you can use the Across Worksheets.

Steps:

First, select all formats of the sheet from where you will fill the other sheets.
In this instance, we will select the B2:E15 cell range.
Then, hold the CTRL key and select the other sheet to fill across.
I selected the sheet Employee2.
You also can use the SHIFT key to select the sheets.
Selected sheets will form a group. You can see it at the top of the worksheet.
Now, open the Home tab >> Go to Editing >> open FILL >> then select Across Worksheets.

As a result, a dialog box will pop up.
From the box, select Formats and finally click OK.

Thereafter, in the footer of the worksheet tab where the sheets are selected, right-click on the mouse and select Ungroup Sheets.

Finally, you can see that the formats of Employee1 are filled across the sheets of Employee2.

In case you are using Excel version 2003 or earlier

First, select the sheets where you want to use the Fill.
Then open the Edit tab >> from the Fill >> select Across Worksheets.
Finally, a dialog box will pop up with 3 fill options

All
Contents
Formats

Select any options depending on your requirement.

Practice Section

I’ve given a practice sheet in the workbook to practice these explained ways to fill across worksheets. You can download it from the above.

Download to Practice

Fill Across Worksheets.xlsx

Conclusion

In this article, I tried to explain 3 ways to fill across worksheets in Excel. These different ways will help you to perform fill across multiple worksheets. Last but not least if you have any kind of suggestions, ideas, and feedback please feel free to comment down below.