Copy Data or Formatting to Multiple Worksheets in Excel

Quickly copy all or parts of a single worksheet - data, formatting, or both - to multiple worksheets in Excel at the same time.

This is not the same as copy/pasting the worksheet; the method shown here doesn't require you to to go to each worksheet individually.

This works for all worksheets in Excel or just the specified worksheets.

Sections:

Quickly Copy Data/Formatting to Multiple Worksheets

Delete Data Across Worksheets

Notes

Quickly Copy Data/Formatting to Multiple Worksheets in Excel

Quick version: Select data to copy > Hold Ctrl or Shift and select worksheets to copy to > Home tab > Fill button (right side of the ribbon menu) > Across Worksheets > OK.

1. Select the data to be copied. You can select specific ranges, tables, or even the entire worksheet itself.
   
2. Hold Ctrl and click the worksheets to which you want to copy the data or formatting. If you hold Shift and click the last worksheet in the workbook, then all worksheets will be selected.
   
3. Go to the Home tab and look to the right and click the Fill button and then Across Worksheets
   
4. In the window that opens, choose if you want to copy the just the Formatting, Contents of the cells, or All (everything).
   
5. Hit OK and That's it!

Now, if you go to the other worksheet tabs that you selected, you will see the result:

Notice that all formatting and data copied over; this includes the conditional formatting that was listed for the cells and the formulas in the Highest/Lowest/Total columns.

If you didn't want to copy the sales data for the table over, you could have individually selected the ranges to copy across the worksheets and copied them one-by-one using the above method; or, you could copy it all and follow the steps in the next section to delete the data for the sales.

Delete Data Across Worksheets

If you copied all of your master worksheet, like in the above example, but you want to remove some parts from the copied worksheets, follow these simple steps:

1. Go to one of the worksheets with the copied data.
2. Select the data that you want to delete.
3. Hold Ctrl and select all other worksheet tabs from which you want to delete this data.
4. Hit Delete.

This will duplicate the delete command to all selected worksheets.

Notes

This technique is wonderful when you spend time creating a template in one worksheet and want to duplicate that out to all, or a selection, of the worksheets in the workbook.

Download the sample file to see the above Example in Excel.