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.

Overview of Fill Across Worksheets in Excel


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.

Sample Dataset of One Sheet

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

Sample Datasheet of Another Sheet

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.

Choosing Across Worksheets Option to Fill Across Worksheets in Excel

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

Select All to Fill Across Worksheets in Excel

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

Ungrouping Sheets

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

Using Fill All to Fill Across Worksheets in Excel


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.

Sample Dataset to Fill Only Contents

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.

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

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.

Choosing Across Worksheets Option to Fill Across Worksheets in Excel

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

Choosing Contents

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

Ungrouping Sheets

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

Applying Fill Contents to Fill Across Worksheets in Excel


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.

Formatted Dataset

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

Unformatted Dataset

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.

Clicking On Across Worksheets Option to Fill Across Worksheets in Excel

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

Selecting Only Formats

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

Ungrouping Grouped Sheets

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

Utilizing Fill Format to Fill Across Worksheets in Excel

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.

Read More: Copy and Paste Formulas Without Changing Cell References


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


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.


Related Articles


<< Go Back to Copy Paste in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shamima Sultana
Shamima Sultana

Shamima Sultana, BSc, Computer Science and Engineering, East West University, Bangladesh, has been working with the ExcelDemy project for 2 years. She has written over 75+ articles and reviewed 1000+ articles for ExcelDemy. She has also led several teams with Excel VBA and Content Development works. Currently, she is working as the Project Manager and oversees the day-to-day work, leads the services team, allocates resources to the right area, etc. Her work and learning interests vary from Microsoft... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo