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.
- Select the data to be copied. You can select specific ranges, tables, or even the entire worksheet itself.
- 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.
- Go to the Home tab and look to the right and click the Fill button and then Across Worksheets
- In the window that
opens, choose if you want to copy the just the Formatting, Contents of the cells, or All [everything].
- 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:
- Go to one of the worksheets with the copied data.
- Select the data that you want to delete.
- Hold Ctrl and select all other worksheet tabs from which you want to delete this data.
- 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.
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 ...