Have you ever needed to set up multiple worksheets in Excel in exactly the same way? Did you enter the column and row headers and format cells on one sheet, then do the exact same thing on all the others? Or did you create one, then copy the data and paste it into each additional worksheet? Copying and pasting is definitely the better of those two options, but there’s an even better way. When you select multiple worksheets in Excel you can edit, format, enter data, and print from each selected worksheet all at once.
To select multiple worksheets:
- At the bottom of the window, click on the tab with the name of the first worksheet.
- Hold down the Shift key on the keyboard and click on the tab with the name of the last worksheet.
The first and last worksheet and all the worksheets between will be selected.
To select worksheets not next to each other:
- Click on the tab of the first worksheet to select it..
- Hold down the Ctrl button on the keyboard and click on the tab of each additional worksheet you want to select.
This can be combined with the Shift key to select continuous groups of tab and tabs not connected to the group.
When you select multiple worksheets, [Group] will appear at the top beside the file name.
The worksheet tabs at the bottom will appear lighter.
Formatting and Entering Data:
Once you’ve selected worksheets, any changes you make to the active worksheet will appear on the other worksheets. I mean anything. Enter text? It will show up on all the worksheets. Add a background color, change the font, and merge cells? Ditto.
Printing From Multiple Worksheets:
- Select the worksheets you want to print.
- If you want to only print a selection, select cells. If you want to print everything, skip this step.
- Go to File > Print, or Office Button > Print, depending on your version of Excel.
- In the Print What section:
- If you selected certain cells to print, click Selection. In 2010 you’ll have to click the drop down arrow on the first box in the Settings section for this option.
- If you want to print each entire worksheet, click Active Worksheet(s) or Print Active Sheets. In 2010 you may need to click the drop down arrow on the first box in the Settings section for this option.
- Click Preview (because it’s always a good idea to check, or you might accidentally print 50 blank pages).
- Close the preview, and when you’re ready, click OK.
Click on a worksheet outside the selected worksheets. If you’ve selected all the worksheets in your workbook, then clicking on any worksheet, other than the active one, will deselect the others.
A Word of Caution
I find it very easy to accidentally select more than one worksheet and suddenly start overwriting data on the other worksheet. If you do this, hopefully Ctrl+Z (the keyboard shortcut for Undo) will fix the problem. Then deselect the tabs by clicking on a tab that is not selected.