Apply Worksheet Changes to All Worksheets in Excel

Add to Favorites
Author:

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 Existing Worksheets

Delete Data Across Worksheets

Notes

Quickly Copy Data/Formatting to Existing 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

Similar Content on TeachExcel
Easy Way to Manage Names in Excel
Tutorial: How to manage Names in Excel so they are easy to view and change as needed. This is a li...
How to Add Boxes, Buttons, Arrows, Speech-Bubbles, Hearts, and More to a Spreadsheet in Excel
Tutorial: In this tutorial I am going to cover inserting and editing Shapes in an Excel workbook, as...
Print Preview Screen Display for The Current or Selected Worksheets in Excel
Macro: This free Excel macro allows you to display the print preview screen or window for the cur...
How to use Vlookup Across Multiple Worksheets in Excel
Tutorial: This tutorial shows you how to use the Vlookup function across multiple worksheets within ...
Print Selected Worksheets in Excel
Macro: This free excel macro will print all selected worksheets in Excel. If there are no ot...
Remove All Filtering From a Worksheet in Excel
Macro: This Excel macro removes all filtering from a worksheet in Excel. This allows you to compl...