Home > Structure > Worksheets > Sheet Tips Excel Worksheet Tips & MacrosTips 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 LettersWhen 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 LettersTo 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.
|
Macro to Change R1C1 SettingIf you frequently change the headings from numbers to letters, or letters to numbers, you can use a macro to do make the change quickly.
Macro CodeCopy 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 LettersWatch 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 |
Open New Window for WorkbookWhen you're working in 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 WorkbookWhile 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. |
To see both windows,
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. |
Video: Copy and Rename a WorksheetWatch this short video to see the steps for copying and renaming an Excel worksheet. The written instructions are below the video: |
Center Headings Without MergingIt'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 MergingTo 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. |
Stop Row AutoFitWhen 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.
|
Video: Prevent Scrolling on a WorksheetTo 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 WorksheetEven 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:
To manually clear the Scroll Area:
|
Add Compact Navigation ButtonsInstead 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 CaptionsHere are the steps for creating a compact button and caption:
|
Get the Sample Files
|
Delete or Add Worksheet Objects
Worksheets, Freeze and Zoom Macros
Hide Sheets Based on Tab Colour
Last updated: November 13, 2023 11:22 AM