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.

25 comments
birumut
birumut

Well done! Thank you very much for professional templates and community edition sesli chat sesli sohbet

rcstan
rcstan

Steps to clone Page Setup of a worksheet 1. Via Print Preview, set up Headers, Borders, Margins, etc., to create a master worksheet 2. Click Close to exit Setup 3. Via the File menu, click Page Setup... and then OK 4. Select one or more other worksheet Tabs 5. Type: Ctrl+Y All other worksheets will immediately adopt the Page Setup parameters of the master worksheet. It is also possible to transfer a customized Page Setup to other workbook tabs using Ctrl+Y exactly as described. This procedure will replace any existing Page Setup. p.s. I've never seen this documented. I discovered it in 1998 and it has worked on every version of Excel since.

rcstan
rcstan

To quickly move to another tab in a workbook: Ctrl + PageUp or PageDn

Ken.Mulholland
Ken.Mulholland

Ctrl+s (regularly and frequently) Ctrl+1 (format as necessary) Alt+= Crtl+tab (to tab between w'bks) F2,F9 - to change a formula to its result. and of course Ctrl+z.

ps2057
ps2057

For workbooks that contain identical pages, such as a form for each day of the week, click on the page tab, hold the control key down and click on multiple page tabs. Any edits made to the first page will be replicated on the others. All sheets must be unprotected for this to work.

dajomu1
dajomu1

To copy a sheet; hold down ctrl and drag the sheet you want to copy. When you see a little arrow, drop it there. This is for copying sheet within the same workbook.

mrogers4christ
mrogers4christ

Simply press Shift+F11 to add a new blank worksheet, Simply press F12 to 'save as'

Shadeburst
Shadeburst

Is there any way to access the sheet tabs without right-clicking on the tab, or indeed using the mouse at all? I would love to be able to rename a tab and move it to a consolidated workbook all without having to go backwards and forwards between keyboard and mouse.

aikimark
aikimark

Does Excel have a setting for new worksheet locations? It is a bit of a pain to have to drag newly inserted worksheet tabs to the end of the list.

david
david

To enter a sum at the bottom of a column of figures use Alt= It is much quicker than searching for the Sigma sign or typing in the formula.

Neon Samurai
Neon Samurai

For me the big one to get users onto is recognizing the multiple levels that exist within every cell The formatting display layer The formula display layer The value display layer Be aware of what layer you want to work with and anways use Paste Special so that you are only pasting the applicable layer you want instead of the vulgar crtl+v that blindly over-writes all layers with the copied content.

rcstan
rcstan

See comment below with subject "Cycle worksheet tabs" to see how easy this can be done. It's the greatest thing since sliced bread!

david
david

Alt-O, H, R lets you rename a tab. Alt-E,M for Move or Copy Alt-E, L Delete current sheet. These are legacy shortcuts from pre-ribbon versions.

thomascwhitfield
thomascwhitfield

Nevermind. That doesn't work. I thought if you used the shortcut keys to switch between tabs then used the context menu key that would work but it still opens the menu where ever the active cell is. Guess your just stuck with switching between keyboard and mouse, and I can relate to how annoying that is, and not just in Excel.

thomascwhitfield
thomascwhitfield

Use the context menu key. Its between the right-ctrl and right-alt keys; Basically it is a key to emulate right-clicking.

thomascwhitfield
thomascwhitfield

I'm using 2010, but when I press the "Insert Worksheet" button that is next to Worksheet 3, it inserts the sheet at the end, regardless of which worksheet is currently active. I don't remember this being any different in previous versions of Excel.

ssharkins
ssharkins

Lots of good tricks with Quick Sum.

Shadeburst
Shadeburst

Thanks ThomasC, gave me an idea. There is another way, namely using Excel Basic, but that means you have to import the code module into the workbook first and that can be just as much of a pain!

aikimark
aikimark

I guess they finally fixed it.

Shadeburst
Shadeburst

Say you want the new worksheet to go between Sheet 1 and Sheet 2. Right-click Sheet 2, Click Insert and new worksheet will go before Sheet 2.

aikimark
aikimark

This is trivially easy to do in VBA. Public Sub AppendSheet() Worksheets.Add after:=Worksheets(Worksheets.Count) End Sub

seanferd
seanferd

Even if you click on the last tab, "insert" is all that is available, and it inserts between the last two tabs. If you want to insert between two tabs it works fine - just click on the tab on the right of the insert position you want. Inserting from the file menu always places the tab in the first position. Move or copy is the best method here, so if you keep a blank sheet, you can always Move or Copy (you'd want to tick the copy checkbox) it and determine its insertion point by choosing one from the list, including (move to end). Regardless as to method, there is indeed an un-aesthetic amount of clicking or dragging.

aikimark
aikimark

@Susan, I'm using Excel 2003.

ssharkins
ssharkins

As mentioned by thomascwhitfield, the Insert Worksheet tab will do just that. Is there a reason you can't use it?

aikimark
aikimark

@Shadeburst I know that you can control the insert behavior that way. I wanted to adjust some setting that would cause newly 'inserted' worksheets to be 'appended' to the worksheets collection (at the end)