Lotus Grouped Worksheets

by Allen Wyatt
(last updated April 2, 2021)

8

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

Merging Only a Date from Access

When you are merging data from an Access database, you may get more information than you want, especially when it comes ...

Discover More

Adjusting Row Height when Wrapping Text

If you have some cells merged in a worksheet, and you wrap text within that merged cell, Excel won't automatically resize ...

Discover More

Suppressing Endnotes

Endnotes are a beneficial necessity for some documents. If you want to print a copy of the document without endnotes, ...

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)

Determining the Complexity of a Worksheet

If you have multiple worksheets that each provide different ways to arrive at the same results, you may be wondering how ...

Discover More

Freezing Top Rows and Bottom Rows

Freezing the top rows in a worksheet so that they are always visible is easy to do. Freezing the bottom rows is not so ...

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

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. Maximum image size is 6Mpixels. 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 9 + 8?

2021-05-15 12:27:51

John Mann

@Ken There are still people using Lotus 1-2-3. I have the latest version of it on my computers (as well as Open Office and MS Office 2010).

It's not so odd that the tip references 1-2-3 since it mentions a user who had been using it for a long time before his office switched to Excel. 1-2-3 & Open Office do have the advantage of retaining proper menu bars and context-sensitive toolbars instead of the Ribbon and QUAT user interface (Imy QUAT) stretched across 1/2 the screen on my laptop.


2021-04-02 15:39:22

Ken Kast

Kind of funny to introduce the tip by referencing Lotus, which hasn’t been published for almost 20 years. That means most folks under the age of 40 have no idea what it refers to.


2017-05-21 22:33:53

Ruthie A. Ward

Odd that you didn't mention creating a macro to select the sheets you wanted in the group and then applying the formatting.


2017-05-19 08:13:53

lilotz

To quickly copy text and/or format to multiple worksheets, group the worksheets, highlight the cells you want copied to all the grouped worksheets, click Fill, choose Across worksheets. You will then have a choice of All, Content, or Format. Presto chango: everything is copied and or formatted onto all the worksheets in the group.


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 :):)


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

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.