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

Displaying a Chart Legend

A legend can help explain the various lines or objects visible in a chart. Microsoft Chart allows you to turn on or off the ...

Discover More

Quickly Inserting the Date Your Way

Tired of messing with inserting the date and then changing it to a format that is more to your liking? There's a quick way ...

Discover More

Transferring Data between Worksheets Using a Macro

Macros can be used for all sorts of data processing needs. One need that is fairly common is the need to move data from one ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

MORE EXCELTIPS (RIBBON)

Viewing Two Worksheets At Once

If you need to work on two worksheets in the same workbook at the same time, Excel makes this rather easy to do. All you need ...

Discover More

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

Discover More

Hiding and Unhiding Worksheets

Worksheets are easily accessible in a workbook, but you may not want them to be so open. You can hide worksheets so they ...

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 3 - 0?

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.

Links and Sharing