Software

10 mistakes to avoid when working with multiple worksheets

One of the best ways to boost your Excel efficiency is also one of the best ways to shoot yourself in the foot. Here are some pitfalls to watch out for when you're dealing with more than one sheet.

Multiple sheets provide a logical way to organize related data. You'll probably evaluate that data within the context of its sheet and its relation to data in other sheets. It's a flexible arrangement, but with all that flexibility often comes a bit of confusion. Here are some things you shouldn't do when you're working with multiple sheets.

Note: This article is also available as a PDF download.

1: Forget to group sheets

Forgetting to perform tasks on grouped sheets is probably the most common mistake users make. Groups are two or more sheets seen as one. In other words, whatever you do to the active sheet, Excel duplicates in all the other sheets in the group. Grouped sheets are a great way to cut down on repetitive tasks, such as formatting. Almost anything you can do in a single sheet, you can do in a grouped sheet, including setting options, print properties, and more.

To create a group, hold down [Ctrl] and click the tabs for the sheets you want to include in the group. Or click a tab, hold down [Shift] and click a second tab. Excel will group the two tabs you clicked and all the tabs in between. To select all the sheets in the workbook, right-click any tab and choose Select All Sheets.

2: Forget to ungroup sheets

If forgetting to group sheets is the most common mistake, forgetting to ungroup them is probably the second most common mistake. Remember, whatever you do to the active sheet, Excel duplicates in all the sheets in the group -- even replacing unseen data without warning. There's really nothing you can do but be alert. However, Excel displays [Group] in the title bar when you're working in a group, so keep your eye out for that visual clue. It might be just the reminder you need.

To quickly ungroup sheets, right-click any sheet in the group and choose Ungroup Sheets. Or click any ungrouped sheet tab. To remove a single sheet from a group, hold down [Ctrl] and click that sheet's tab.

3: Fail to be consistent

Being inconsistent from sheet to sheet is a big mistake. For instance, the sheet in Figure A displays quarterly sales totals in row 10. If the sheets are consistent, you can enter a single formula anywhere in the workbook to determine the yearly total for each salesperson or region. For example, the formula in Qtr4!B11 totals the subtotals in cell B10 in all four sheets.

Figure A

A single formula can evaluate data in multiple sheets.

This formula returns the correct result only if cell B10 in all four sheets actually contains the quarterly total for Smith. An offset of even a single cell will return incorrect results -- and you might not realize it. Consistency takes a bit of planning, but it's worth it.

4: Fail to validate grouped sheets

You can automate tasks for grouped sheets using VBA, which increases efficiency even more. Before committing changes, run a check on the group to ensure that the appropriate sheets are actually selected. Failure to do so could lead to disaster. The short procedure in Listing A prints each sheet in the current group to the Immediate window. Of course, you'll want to customize the checking process, but cycling through a Sheets collection that contains only selected sheets is the way to access grouped sheets. In the absence of a group, the collection will reference the active sheet. In addition, the Sheet collection, in this instance, does not include hidden sheets.

Listing A

Function ListSheets()
  'List all sheets in current group.
  'If no group, lists active sheet.
  Dim sht As Object
  For Each sht In ActiveWindow.SelectedSheets
    Debug.Print sht.Name
  Next sht
End Function

5: Forget to reference another sheet

You can copy a formula from sheet to sheet just as you can copy a formula from cell to cell. Copying a formula from one sheet to another requires just a bit of intent, though. When you copy a formula from one sheet to another, Excel copies just the formula. It does not copy a sheet reference, unless that reference is an explicit component of the copied formula. This can lead to errors if you want the copied formula to reference the original sheet and not the target sheet. Be sure to enter the sheet reference manually if you want the formula to reference the original sheet.

6: Work harder than you have to, part 1

Spreading data across multiple sheets is supposed to make you more productive and efficient. Unfortunately, if you don't know how to view more than one sheet at a time, you might waste time flipping back and forth between sheets. Instead, view multiple sheets at the same time, as follows:

  1. Click the tab of a sheet you want to view.
  2. From the Window menu, choose New Window.
  3. Repeat steps 1 and 2 until you all the sheets you want to view open in an individual window.
  4. From the Windows menu, click Arrange.
  5. Select a viewing option.
  6. Be sure to select the Windows Of Active Workbook option if you have more than one workbook open.
  7. Click OK.

7: Copy from the Clipboard

Pasting content from the Clipboard is one of the few instances where Excel will not duplicate an action for all sheets in a group. If you paste content from the Clipboard, Excel will paste the data only into the active cell on the active sheet.

  1. You can copy from one worksheet to all the others in the group, as follows:
  2. Select the cell or range that contains the data you want to copy.
  3. Create the group.
  4. From the Edit menu, choose Fill. In Excel 2007/2010, click Fill in the Editing group on the Home tab.
  5. Select Across Worksheets.
  6. Choose the appropriate options and click OK. You can choose to copy just the contents, just the formatting, or both.

8: Trust the spell checker

If you expect Excel to check all the sheets in the workbook, you might be embarrassed. This feature checks only the current sheet. To ensure that Excel runs a spelling check on all of the sheets in a workbook, create a group that contains all the sheets in the workbook before running it.

9: Print more than you need

Excel prints the current sheet when you print, which is good. Printing every sheet in the workbook would be wasteful and inefficient. If you create a group, Excel will print all the sheets in the group, which is a quick and easy way to print a lot of data at one time. But Excel will print each sheet on a separate sheet of paper, which might not be what you want.

Your printer properties might be the quickest and easiest way around this behavior. All printers are different, so you may have to poke around a bit to find the route, but here's how to get started from Excel. First, create the group and choose Print from the File menu. In the Print dialog box, click Printer Properties. In Excel 2007, click the Office button; in Excel 2010, click the File menu. Click Printer Properties.

This is where steps will vary, but look for a Pages Per Sheet option. Most likely, it'll be with your printer's layout options. Select the number of sheets you want on each page, as shown in Figure B.

Figure B

Most printers will let you specify the number of sheets to print on each page.

10: Work harder than you have to, part 2

Anytime you find yourself doing a lot of repetitive work, you're probably working harder than necessary. Multiple sheets often contain the same labels and formatting, but different values. If you create the workbook from scratch, it's easy enough to create several template sheets as you create one -- you simply create a group and Excel duplicates your actions. If you're working with an existing sheet you want to copy, this doesn't work.

Here's a fast way to create new sheets from an existing sheet:

  1. Click the tab you want to copy, and Excel will display a small triangle and a paper icon.
  2. Hold down [Ctrl] and Excel will add a plus sign (+) to the icons.
  3. Drag the sheet to the right or left. Excel will create a new sheet based on the copied sheet.

You can quickly create a number of sheets this way.


About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

2 comments
wpshore
wpshore

Do these suggestions apply to Excel 2007? Thanks.

ssharkins
ssharkins

I think everything's pretty much the same in 07 and 10 -- if you find one that's different, let me know and I'll see what else I can come up with.

Editor's Picks