Please Note: This article is written for users of the following Microsoft Excel versions: , 8, , 10, and . If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Lotus Grouped Worksheets.

Lotus Grouped Worksheets

by Allen Wyatt
(last updated November 24, 2014)

Prior to switching over to Excel, Lotus 1-2-3 was the spreadsheet program of choice where Bill worked. In Lotus you were able to create a "grouped worksheet." Each worksheet in a group assumed the same formatting automatically. This was extremely useful, but Bill has yet to discover the same capability in Excel. He wondered if anyone had any suggestions for this capability in Excel.

There is no way to create persistent worksheet groups in Excel. You can, however, create "on-the-fly" worksheet groups, most often referred to as a "selection set" of worksheets.

Creating such a set is simple—just select the first worksheet you want in the group, then hold down the Shift key as you click the tab for the last sheet. Now, all worksheets between the first and last are part of the selection set. You can also hold down the Ctrl key as you click on various tabs to add or remove worksheets from the group.

Once your selection set is established, then any formatting you do to one of the sheets is also done to the other sheets in the group. The only potential "gotcha" is that you need to be careful if you start entering information in cells because entering data in one sheet will also cause it to be entered in the same place in other sheets in the group.

You can also, if desired, easily copy formatting (and only formatting) from one worksheet to another. Follow these steps:

  1. Format a worksheet the way you desire.
  2. Select all the cells in that worksheet by pressing Ctrl+A.
  3. Press Ctrl+C. This copies everything on the worksheet to the Clipboard.
  4. Select the worksheet (or worksheets) to which you want to copy the formatting.
  5. Press Ctrl+A. This selects all the cells on the worksheet (or worksheets).
  6. Choose Paste Special from the Edit menu. Excel displays the Paste Special dialog box. (See Figure 1.)
  7. Figure 1. The Paste Special dialog box.

  8. Choose the Formats radio button.
  9. Click OK.
  10. If you want to also copy column widths, repeat steps 6 through 8, but instead choose the Column Widths radio button.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11190) applies to Microsoft Excel , 8, , 10, and . You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Lotus Grouped Worksheets.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Quickly Changing Font Sizes

A quick little shortcut can help you easily step through different font sizes for whatever text you've selected. Word ...

Discover More

Tracked Changes Won't Go Away

Track Changes is a great tool when editing a document, but the ways that it affects your document can sometimes be confusing. ...

Discover More

Selecting a Paper Size

Excel can print your worksheet on just about any paper size you can imagine. How you select the paper size you want used ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

MORE EXCELTIPS (MENU)

Synchronized Workbook and Worksheet Names

When you work on older workbooks in Excel, you may notice that the name of the worksheet tab and the workbook itself are the ...

Discover More

Unbreakable Formula References to Worksheets

Excel allows you, in your formulas, to include references to cells on other worksheets. Those references include the name of ...

Discover More

Viewing Same Cells on Different Worksheets

When switching from one worksheet to another, you might want to view the same portion of the new worksheet that you were ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)