Lotus Grouped Worksheets

by Allen Wyatt
(last updated August 31, 2015)

4

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. Display the Home tab of the ribbon.
  7. Click the down-arrow under the Paste tool and choose Paste Special from the resulting choices. Excel displays the Paste Special dialog box. (See Figure 1.)
  8. Figure 1. The Paste Special dialog box.

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

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11191) applies to Microsoft Excel 2007 and 2010.

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

Tasks for Each Workbook

Excel allows you to control how it uses the Windows Taskbar. This tip explains the two ways Excel can use the Taskbar and how ...

Discover More

Permanent Watermarks in a Document

Need to add a graphic watermark to a document? It's not that hard to do, but making the watermark permanent can be a bit more ...

Discover More

Selectively Changing Endnotes to Footnotes

Want to turn some (but not all) of your endnotes into footnotes. Rather than do it manually, you can apply the shortcut ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

MORE EXCELTIPS (RIBBON)

Finding a Worksheet with a Specific Value in a Specific Cell

If you have a lot of worksheets in workbook, finding the exact one you want can be a bit tricky. This tip looks at various ...

Discover More

Condensing Multiple Worksheets Into One

Want a quick way to combine your worksheets? This tip features a simple macro to do the task for you.

Discover More

Finding the Size of Individual Worksheets

Your workbooks can contain many, many worksheets. Which of those worksheets are the largest, however? Here's some ideas on ...

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:

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 7 + 8?

2012-01-09 11:05:29

Bill

I knew these techniques but I did not realize you could copy column widths - one of those precious nuggets that was not intended as the primary learning but will enhance my future efforts. Thanks a lot! I always learn new things even in the routine tasks.


2012-01-09 07:49:51

Tom C

You can also do the following:
1. Set up the worksheet the way you want it.
2. Right click on the worksheet tab.
3. Select "Move or Copy"
4. Check the "Create a copy" box
5. Click "OK"
6. Repeat the above until you have all the sheets you want.


2012-01-07 20:44:00

Juan

If you are using Spanish version of Excel, press Ctrl+E to select all the cells of a worksheet.
Very valuable tip!!


2012-01-07 12:26:56

Juan

Excellent tip!! I didn't know that you can copy the format and text of a worksheet to multiple worksheets, this can save a huge time to the user who created many same sheets :):)


Newest Tips
Subscribe

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

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing
Share