Excel Worksheet Tips & Macros

Tips and macros for working with Excel worksheets efficiently, scrolling, saving, and default settings. Also, see how to change column heading numbers back to letters.

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.

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

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

Change Column Headings to Letters

To change the reference style back to A1 style, instead of R1C1, you can follow the steps below, to change an option setting. Or, use the macro in the next section, if you have to change the R1C1 setting frequently.

• In the Excel Ribbon, click the File tab, at the far left
• Next, in the list at the left, click Options (you might need to click More, and then click Options)
• In the Options window, at the left, click the Formulas category.
• In the Working with Formulas section, add or remove the check mark from ‘R1C1 reference style’
• Click OK to close the Options window

Macro to Change R1C1 Setting

If you frequently change the headings from numbers to letters, or letters to numbers, you can use a macro to do make the change quickly.

• Copy the following macro code to a regular code module your Excel Personal Workbook, and run it when needed.
• Or, watch the video below, to see how to record your own macro, and add a button for the macro on the Quick Access Toolbar.

Macro Code

Copy the following macro code to a regular code module your Excel Personal Workbook, and run it when needed.

NOTE: This macro toggles the reference style, so if the current setting has R1C1 (numbers) reference style turned on, it turns that setting off, to go back to A1 style (letters), and vice versa

Sub ToggleR1C1
Application.ReferenceStyle = _
   xlA1 + xlR1C1 – Application.ReferenceStyle
End Sub

Video: Record Macro to Change Column Headings to Letters

Watch this video, to see how to record your own macro, and add a button for the macro on the Quick Access Toolbar.

Note: This video uses a slightly longer version of the macro code, shown below.

Macro Code From Video

Here is the final code shown in the video above. It uses If...Else...End If to change the reference style setting.

Sub ToggleR1C1_FromVideo
	If Application.ReferenceStyle = xlA1 Then
		Application.ReferenceStyle = xlR1C1
	Else
		Application.ReferenceStyle = xlA1
	End If
End Sub

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.

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. The written instructions are below the video:

• Quickly Copy a Worksheet
• Quickly Rename a Worksheet

Quickly Copy a Worksheet

To quickly copy a worksheet:

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:

1. Double-click on the sheet tab, to select the current sheet name
2. Type a new name, and press the Enter key

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

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 dropdown, 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

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.

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 secgtion
• 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 Sample 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.

More Excel Tips and Tutorials

Worksheet VBA

Delete or Add Worksheet Objects

Add Worksheets Automatically

Excel Freeze Panes

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