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
View Two Sheets in Same Workbook
Center Headings Without Merging
Copy a Worksheet to another Workbook
Enter Data on Multiple Sheets at Once
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.
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.
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.
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
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
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.
While working in an Excel file, you can create a new window, and see two of the worksheets at the same time.
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.
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,
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:
Watch this short video to see the steps for copying and renaming an Excel worksheet. The written instructions are below the video:
To quickly copy a worksheet:
To quickly rename a worksheet:
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:
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.
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:
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:
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!
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
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.
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.
To save worksheet(s) as PDF file:
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.
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.
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
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.
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:
Delete or Add Worksheet Objects
Worksheets, Freeze and Zoom Macros
Hide Sheets Based on Tab Colour
Last updated: May 10, 2023 3:35 PM