Fill Across Worksheets in Excel (3 Quick Ways)

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. In this article, I’m going to explain how to fill across worksheets in Excel.

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.

I explained these ways for Excel version 2007 or later.

Download to Practice

Ways to Fill Across Worksheets

1. Using Fill ALL

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.

Using Fill ALL

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.

Using Fill ALL

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.
➽ I selected the sheet SalesPerson 2
You also can use the SHIFT key to select the sheets.

Using Fill ALL to Fill Across Worksheets

After selecting the sheets, it 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

Using Fill ALL to Fill Across Worksheets

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

Using Fill ALL to Fill Across Worksheets

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

Using Fill ALL to Fill Across Worksheets

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

Using Fill ALL to Fill Across Worksheets

2. Using Fill Contents

From Across Worksheets, the Fill Contents will only fill the contents not formats.

Here, In SalesPerson 1 there is a dataset of sales information containing 4 columns.

Using Fill Across Worksheets

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.

Using Fill Across Worksheets

Here, to use this, first select the contents of the sheet from where you will fill the other sheets.
Now, hold the CTRL key and select the other sheet to fill across.
➽ I selected the sheet SalesPerson 2
You also can use the SHIFT key to select the sheets.

Using Fill Contents to Fill Across Worksheets

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

Using Fill Contents to Fill Across Worksheets

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

Using Fill Contents to Fill Across Worksheets

At the bottom of the worksheet tab where sheets are selected, right-click on the mouse and select Ungroup Sheets

Using Fill Contents to Fill Across Worksheets

Here, In SalesPerson 2 only the contents are filled from the SalesPerson 1.

Using Fill Contents to Fill Across Worksheets

3. Using Fill Formats

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.

Using Fill Formats

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.

Using Fill Formats

First, select all formats of the sheet from where you will fill the other sheets.
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.

Using Fill Formats to Fill Across Worksheets

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

Using Fill Formats to Fill Across Worksheets

Now, a dialog box will pop up. From there select Formats and finally click OK.

Using Fill Formats to Fill Across Worksheets

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

Using Fill Formats to Fill Across Worksheets

Now, you can see that the Formats of Employee1 are filled across the sheets of Employee2

Fill Formated

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.

practice sheet

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.

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo