by Allen Wyatt
(last updated May 19, 2017)
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:
Figure 1. The Paste Special dialog box.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11191) applies to Microsoft Excel 2007 and 2010.
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!
Freezing the top rows in a worksheet so that they are always visible is easy to do. Freezing the bottom rows is not so easy. ...Discover More
Need to create a large number of worksheets using specific names? If so, you'll love the ideas presented in this tip.Discover More
Need a worksheet's name to change based on the contents of a cell? You'll need to rely on a macro to do the changing, but it ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.