Copy Data or Formatting to Multiple Worksheets in Excel

Add to Favorites
Author: | Edits: don

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.
    dfe564421f9e23e83b3f2e5d7bacaaf8.png
  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.
    69d1a09fd9f5178085e6bd348a0a61a8.jpg
  3. Go to the Home tab and look to the right and click the Fill button and then Across Worksheets
    0e2e6805eeed6392993186acc4add479.png
  4. In the window that opens, choose if you want to copy the just the Formatting, Contents of the cells, or All (everything).
    39f52ca901338b886f4eab8cbd1da4f2.png
  5. Hit OK and That's it!

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

f8ac23cee07d2db30cdd77b1f60c082e.png

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.

Question? Ask it in our Excel Forum


Downloadable Files: Excel File 1, Excel File 2

Similar Content on TeachExcel
How to use Vlookup Across Multiple Worksheets in Excel
Tutorial: This tutorial shows you how to use the Vlookup function across multiple worksheets within ...
Quickly Copy Cell Formatting to Other Cells in Excel
Tutorial: How to copy all of the formatting of a single cell to other cells in the spreadsheet witho...
Quickly Copy the Last Action to Multiple Cells in Excel
Tutorial: In the previous tutorial I talked about the Redo button in Excel and how using Ctrl + Y ca...
Add a Drop Down Menu to a Cell in Excel - Data Validation Macro
Macro: Add a drop down menu or list to a cell in Excel with this free Excel macro. This is a grea...
Guide to Combine and Consolidate Data in Excel
Tutorial: Guide to combining and consolidating data in Excel. This includes consolidating data from ...
Apply Conditional Formatting to Multiple Cells with a Single Formula
Tutorial: How to use a single formula to apply conditional formatting to multiple cells at once in ...