Home > Structure > Worksheets > Sheet Tips Excel Worksheet Tips & Macros Here are a few of my favourite tips and macros for Excel worksheets. Also, see how to change column heading numbers back to letters, if you open a workbook with that problem!

Change Column Headings to Letters

When you open an Excel file, you might see numbers -- 1, 2, 3 -- across the column headings, instead of letters -- A, B, C. This is R1C1 reference style, and it can be a useful feature sometimes.

Video: Change Column Numbers to Letters

To see why the column headings change to numbers, instead of column letter, and how to switch the column headings back to letters, watch this short video. The written instructions are below the video.

There are written steps on the Change Column Headings to Letters page.

Note: If you want to use a macro to change the column headings, see the Macro section below.

Open New Window for Workbook

When you're working in Microsoft Excel, you can create a new window for a workbook, and see two sheets at the same time.

This short video shows the steps, and has a quick tip for moving sheets, so you don't have to waste time dragging them a long distance!

Thanks to UniMord, who told me about this time-saving tip.

View Two Sheets in Same Workbook

While working in an Excel file, you can create a new window, and see two of the worksheets at the same time. This is a good way to troubleshoot formulas, as shown in this short video. The written instructions are below the video.

⏰ Video Timeline ⏰

• 0:00 Worksheet with Formulas
• 0:28 Show/Hide Worksheet Formulas
• 1:13 Open a Second Window
• 2:21 Close the Second Window

View Two Sheets in Same Workbook

While working in an Excel file, you can create a new window, and see two of the worksheets at the same time.

To create a new window:

1. On the Excel Ribbon, click the View tab
2. Click the New Window command

   

3. The file name in the title bar will now show a number at the end

   

To see both windows,

1. On the Excel Ribbon, click the View tab.
2. Click the Arrange All command

   

3. Select one of the Arrange options, such as Vertical
4. If you have multiple workbooks open, and only want to see the windows for the active workbook, add a check mark to "Windows of active workbook"
5. Click OK.

   

6. At first, both windows will show the same worksheet.

   

7. To see a different sheet in one of the windows, scroll through the sheet tabs, and click on the tab that you want to see in the second window.

   

NOTE: If you save the file with two windows open, and close Excel, those two windows will appear the next time that you open the file. To return to one window, see the instructions in the next section.

Close One Window in Excel Workbook

If you have two windows open within an Excel workbook, you can close one of the windows, and go back to a single window.

To close one of the windows,

1. Select the window that you want to close. NOTE: It is best to leave open the window that is numbered 1 -- otherwise, some of the view settings, such as hidden gridlines, might be lost.
2. Click the X at the top right of the selected window, to close it.

   

3. OR, use the keyboard shortcut, Ctrl + W, to close the selected window.
4. To return the remaining window to full size, double-click its title bar, OR click the Maximize button at the top right of the file's window.

   

View Two Workbooks in Excel Window

In Excel 2010, and earlier versions, there is only one main window in an Excel session. You can arrange the workbook windows, to see two or more Excel files at the same time.

To see two or more Excel files:

1. On the Ribbon, click the View tab, then click Arrange All

   

2. In the Arrange Windows dialog box, click one of the Arrange options, such as Horizontal, then click OK.

   

Video: Copy and Rename a Worksheet

Watch this short video to see the steps for copying and renaming an Excel worksheet. There are written instructions below the video, and steps for adding new worksheets:

• Excel Sheet Name Rules
• Quickly Copy a Worksheet
• Quickly Rename a Worksheet
• Add a New Worksheet
• Add & Rename New Worksheet

Excel Sheet Name Rules

It’s hard to find the sheet naming rules in Excel’s help, but you may have seen an Excel error message that lists them.

Sheet Name Rules

1. The sheet name can’t be more than 31 characters
2. You can’t delete the existing name and leave the sheet tab blank
3. Sheet name cannot include any of the following 7 characters:
   •         :     \    /    ?    *   [    ]
4. History is a reserved word in Excel, and cannot be used as a sheet name

Sheet Name Recommendations

In addition to the sheet naming rules shown above, I have a couple of recommendations, that I follow in my own Excel files:

1. Use only letters, numbers and underscores in sheet names.
   • I avoid space characters, if possible. A one-word sheet name makes formulas and macros easier to create -- no extra quote marks are needed around the name.
   • For example, I’d use SalesData or Sales_Data, instead of Sales Data.
2. Use different names for worksheets and named ranges, to avoid confusion.
3. Even though they're allowed, I avoid apostrophes and exclamation marks. Those characters could cause extra work in formulas and macros too!

Quickly Copy a Worksheet

To quickly copy a worksheet, follow these steps:

1. Click on the sheet tab that you want to copy.
2. Press the Ctrl key, and drag the sheet tab to the location where you want the copy. You will see a small arrow, and a sheet symbol with a + sign, as you drag.

   

3. When the arrow is in the location where you want the copy, release the mouse button, and then release the Ctrl key.

Quickly Rename a Worksheet

To quickly rename a worksheet, follow these steps:

1. Double-click on the sheet tab, to select the current sheet name
2. Type a new name (see rules in next section), and press the Enter key

Add a New Worksheet

To quickly add a new worksheet, follow these steps:

1. Select an existing sheet tab, to the left of where you want the new sheet to appear
2. At the right end of the visible sheet tabs, click the new sheet button -- it has a plus sign on it (+)
3. (optional) To rename the new sheet, follow the steps in the previous section.

Add & Rename New Worksheet

Here's a quick way to add and rename a worksheet, to the right of the last sheet in the workbook:

1. Select the last sheet in the workbook
2. At the right end of the visible sheet tabs, double-click the new sheet button -- it has a plus sign on it (+)
• 
3. A new sheet is inserted at the end of the workbook's sheets, and the new sheet's name is selected on its sheet tab
4. Type a new name for the sheet (maximum 31 characters), and then press the Enter key

Thanks to Alex Blakenburg for sharing this tip!

Center Headings Without Merging

It's best to avoid merged cells, if possible, because they can make it harder to sort and filter a worksheet. To create multi-column headings, you can center text over several columns, without merging the heading cells. For example, type the heading "Qtr 01", and center it over the columns for January, February and March.

This video shows the steps, and there are written steps below the video

Center Headings Without Merging

To center headings, without merging the cells:

1. Type the heading in one of the cells where you want it centered. In this example, "Qtr 01" is typed in cell B3.

   

2. Select all the cells where you want the heading centered -- cells B3:D3 in this example.
3. On the Ribbon's Home tab, click the Alignment Settings button, at the bottom right of the Alignment group.
   Or, press Ctrl + 1 -- the shortcut to open the Format Cells window -- and click the Alignment tab.

   

4. In the Horizontal drop down, select Center Across Selection, then click OK

The heading will be centered across the selected columns, but the cells are not merged -- you can select any cell individually.

TIP: Add an outside border to the centered heading, so it looks like a single, merged cell.

Prevent Footer Scaling

If you're printing worksheets at a reduced scale, you can adjust the settings so that the footer text doesn't end up too small to read.

To change the footer setting for the active sheet:

• Go to the Page Layout tab on the Excel Ribbon.
• In the Page Setup group of commands, click the little arrow at the bottom right of that group, to open the Page Setup dialog box.



• Click the Header/Footer tab, and at the bottom, remove the check mark from "Scale With Document". (Note: This setting will affect the Header too.)



• Then, click OK to exit.

Copy a Worksheet to another Workbook

NOTE: If both workbooks are visible, you can use the mouse shortcut to drag a copy from one window to another.

To copy a worksheet to a different workbook:

1. Open both workbooks
2. Activate the workbook which contains the sheet that you want to copy
3. Right-click on the sheet tab that you want to copy, and click Move or Copy.

   

4. From the "To Book" drop down, select the name of the workbook where you want to add a copy of the worksheet.
5. From the "Before Sheet" drop down, select a location for the sheet.
6. Add a check mark to "Create a Copy", and click OK

   

7. Check that the sheet appears in both workbooks, and then you can save and close the files.

Enter Data on Multiple Sheets at Once

If you select two or more sheets, anything you do on the active sheet will also happen on the other selected sheets.

WARNING: After you finish your changes, remember to ungroup the sheets!

1. To select all the sheets, right-click on any sheet tab, and click Select All Sheets.
2. In the Title Bar at the top of the Excel window, you should see the file name, with [Group] after the name. That will remind you that multiple sheets are selected.
3. Make your changes on the active sheet.
4. Then, to ungroup the sheets, right-click on the sheet tab for one of the selected sheets
5. Click Ungroup Sheets.

Stop Row AutoFit

When you reduce the font size in worksheet cells, Excel automatically reduces the row height, with its AutoFit feature. To prevent that automatic change, follow these steps to stop row AutoFit

First, make a small change to the row height, using one of these methods

• Drag down on the row divider
• OR, right-click the row button, click Row Height, and make a small change

In this screen shot, the height of Row 3 has been adjusted slightly. Row 4 has NOT been changed.

Next, to see how that change stops row AutoFit, reduce the font size in B3:C4. In this exampled the font was changed from 20 to 12.

• The row height for Row 3 did NOT AutoFit, even though the font is smaller
• The row height for Row 4 DID AutoFit, automatically adjusting for the smaller font

AutoFit Column Width

You can quickly adjust a column's width by double-clicking on its border in the column headings. Sometimes that makes the column much too wide though, if there is heading text in the top few rows.

To avoid that problem, you can adjust a column's width to fit specific cells, instead of the entire column.

I show the steps in this short video, and you can see more details on my Contextures Blog: Adjust Excel Column Widths to Fit Specific Cells

Fix Column Widths - Quick Trick

Use this quick tip to fix Excel column widths, when setting up similar worksheets

Allow Changes on Protected Sheet

In some worksheets, you might want to allow users to make changes in specific cells, while keeping the rest of the worksheet locked. This short video shows the steps for setting that up.

Save Worksheets as PDF File

To save worksheet(s) as PDF file:

1. Select the worksheet(s) that you want in the PDF file
2. On the Ribbon, click the File tab, then click Save & Send
3. Click Create PDF/XPS Document, then click Create PDF/XPS

   

4. Select a folder for the file, and type a file name,
5. (optional) Click the Options button, and select the file settings that you need, and click OK.

   

6. Click Publish, to create the PFD file in the selected folder.

Video: Prevent Scrolling on a Worksheet

To prevent people from scrolling on a worksheet, you can set the sheet's ScrollArea property. Watch this video to see the steps, and the written instructions are below the video.

Prevent Scrolling on a Worksheet

Even if a worksheet is protected, you are able to scroll in any direction, and see what is on the sheet. To prevent scrolling, you can change the ScrollArea property for the worksheet, either manually or with a macro.

The Scroll Area setting is handy as a navigation aid, and it might deter people from clicking or scrolling through the worksheet. However, it can be easily removed, so do not depend on it to add security to your file.

NOTE: The Scroll Area has to be set each time the workbook opens. To automate this, you can use a macro, as shown in the next section.

To manually set the Scroll Area:

1. On the Ribbon, click the Developer tab, and click Properties

   

2. In the Properties window, enter a range address in the ScrollArea property box

   

3. Click on the worksheet, and you will be restricted to clicking and scrolling in the Scroll Area.

To manually clear the Scroll Area:

1. On the Ribbon, click the Developer tab, and click Properties
2. In the Properties window, delete the address in the ScrollArea property box
3. Click on the worksheet, and you will be restricted to clicking and scrolling in the Scroll Area.

Set Scroll Areas With a Macro

The Scroll Area setting has to be set each time the workbook opens, and you can automate that with a macro.

Copy the SetAllScrollAreas macro to a regular module in your workbook. It will set the Scroll area for each worksheet in the file, based on the Used Range on that sheet.

Sub SetAllScrollAreas()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    ws.ScrollArea = ws.UsedRange.Address
Next ws
End Sub

Then, add the SetAllScrollAreas macro name to the Worksheet_Open procedure in the ThisWorkbook module. This will set the scroll areas on all sheets, each time that the workbook is opened and macros are enabled.

Private Sub Workbook_Open()
    SetAllScrollAreas
End Sub 

Change Number of Sheets in New Workbook

In Excel 2010 and earlier, there are 3 sheets in a new workbook, by default. You can change that setting to a different number. See the steps in in Excel 2007, this short video.

Note: In Excel 2010, click the File tab, instead of the Office button, and go to the General category in the Options.

Add Compact Navigation Buttons

Instead of using big buttons with hyperlinks for workbook navigation, use compact buttons, with captions that overflow to the right. The captions are part of the button, so they're clickable too! -- thanks to AlexJ for this tip!

This video shows how to set up the buttons, and there are written steps are shown below the video.

Set Up Buttons and Captions

Here are the steps for creating a compact button and caption:

• Add a small shape to the worksheet, and type a caption
• Click the shape's border, and on the Ribbon's Home tab, format the font as black, bold, 14pt
• Right-click on the shape, and click Size and Properties
• On the Shape Options tab, click the Text Box triangle, to expand the section
• Add a check mark to Allow text to overflow shape
• Remove the check mark for Wrap text in shape
• On the shape, add a few spaces at the start of the text, so it moves to the right of the shape
• Right-click the shape, click Link, and add your hyperlink
• Click the button or caption, to follow the hyperlink

Get the Excel Files ✅

1. Click here to get the ScrollArea sample file. It is zipped, and in xlsm format, and contains macros.
2. Click here to get the Center Without Merging sample file. It is zipped, and in xlsx format, and does not contain any macros.
3. Click here to get the Compact Navigation Buttons sample file. It is zipped, and in xlsx format, and does not contain any macros.
4. Click here to get the AutoFit Columns sample file. It is zipped, and in xlsx format, and does not contain any macros.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

More Excel Tips and Tutorials

Worksheet Macros

Column Heading Numbers, Fix

Error - Cannot Shift Objects

Delete or Add Worksheet Objects

Add Worksheets Automatically

Blank Cells, Fix

Excel Freeze Panes

Google Sheets, Tips

Custom Views

Worksheets, Freeze and Zoom Macros

Hide Sheets Based on Tab Colour

Worksheet Protection Selector

Worksheet AutoFilter VBA

Status Bar Tips

Copy VBA to Sheet

Workbook Tips

VBA - Get Started