Software

Five of my favorite Excel worksheet tips

If you're not using these worksheet-related tricks, you're working harder than you have to! Susan Harkins shares five of her favorites.

We all have features and commands that we use more than others. If you don't use something though, you tend to forget that you can! Below, I've listed some of my favorite sheet-related tips - the ones I seem to use often enough that they're second nature.

#1: Resize sheet tabs

Sheet tabs are small, by nature, but you can change the default size as follows in Windows XP (classic):

  1. From the Start menu, choose Control Panel.
  2. Double-click Display.
  3. Click the Appearance tab.
  4. Click Advanced (bottom right).
  5. From the Item dropdown, choose Scrollbar.
  6. Change the setting, accordingly - the larger the setting, the larger the tabs.
  7. Click OK, Apply, and then OK.

If you're using Windows 7, do the following:

  1. From the Start menu, choose Control Panel.
  2. Click Appearance & Personalization
  3. Click Change Window Glass Color in the Personalization section.
  4. Click Advanced Appearance Settings.
  5. From the Item dropdown, choose Scrollbar.
  6. Change the setting, accordingly - the larger the setting, the larger the tabs.
  7. Click OK, Apply, and then OK.

Unfortunately, this setting will also change the size of your scrollbars - it's an all-or-nothing setting. If you can't handle the larger/smaller scrollbars, you're stuck with the default sheet tab size.

#2: Determine the number of sheets

By default, Excel offers three worksheets in a new workbook. You can add and delete sheets, as you like, but you can also change the number of default sheets, as follows:

  1. Click the File tab and then click Options under Help. In Office 2007, click the Office button, and then click Excel Options. In Word 2003, choose Options from the Tools menu.
  2. Click General in the left pane. In Word 2003, click the General tab.
  3. In the When Creating New Workbooks section, choose the number of sheets wanted from the Include This Many Sheets dropdown. In Word 2003, use the Sheets In New Workbook dropdown.

#3: Change tab color

Sheet tabs are the same color as the sheet, unless you change their color. Color-coded tabs are a great visual tool for identifying sheet types by sight. To change sheet tab color, do the following:

  1. Right-click the sheet tab.
  2. Choose Tab Color.
  3. Select a color!

Don't forget, you can group tabs to apply the same color to multiple tabs at the same time.

#4: Copy Page Setup settings

Each sheet has its own print options (orientation, margins, and so on). If you find yourself repeating the setup settingsā€¦ STOP! There's an easy way to copy these settings from one sheet to another:

  1. Select the sheet that contains the options you want to use in another sheet.
  2. Press [Ctrl] and click the tabs of the sheets you want to update (to create a group).
  3. Click the File tab.
  4. Click Print in the left pane.
  5. Click Page Setup (the link at the bottom). In Excel 2003, choose Page Setup from the File menu.
  6. Click OK to close the Page Setup dialog box.

#5: Restrict the work area

Most sheets don't use all the columns and rows available, so I recommend that you restrict access to just the area used. One way to do so is to hide all the columns and rows not used, as follows:

  • Columns: Select the first column in the range you want to hide and press [Ctrl]+[Shift]+[Right Arrow]. Right-click the selection and choose Hide.
  • Rows: Select the first row in the range you want to hide and press [Ctrl]+[Shift]+{Down Arrow]. Right-click the selection and choose Hide.

What are your favorite Excel worksheet tips?

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.

Editor's Picks