Opening the vault on Excel, part 4

Excel expert Neale Blackwood CPA began writing his monthly column in INTHEBLACK magazine in 2002. He recently “opened the vault” on those early articles to review the information and add newer and easier solutions or built-in functions.

I answered four questions in June and July 2003.

1. Help! I’ve added so many buttons to the toolbar it’s hard to find the one I’m after.

This question is a bit dated these days as the menu/toolbar interface from the earlier versions of Excel no longer exists (except in the VBA window!).

The current Excel interface uses Ribbon tabs and the Quick Access Toolbar. Any icon from the Ribbon can be added to the Quick Access Toolbar by right clicking the icon and selecting Add to Quick Access Toolbar. Unfortunately, many people do not use (or know about) the Quick Access Toolbar and it languishes away at the top left of the Excel screen.

Figure 1.

The Quick Access Toolbar is green and displayed above the Ribbon when Excel is installed. 

Figure 2.

It will only have a few icons displayed when it is installed. I display it below the Ribbon as it is quicker to access, plus it uses colours on the icons. Right click the Quick Access Toolbar and choose the Show Quick Access Toolbar Below the Ribbon option – see Figure 3.

Figure 3.

You can edit the Quick Access Toolbar and move the icons so that you can group similar icons together – e.g. formatting icons. 

Right click the Quick Access Toolbar and select Customize Quick Access Toolbar. This opens a dialog that enables you to add extra icons as well as move icons around. See Figure 4

Figure 4.

The existing icons are shown on the right. If you click an icon on the right, you can use the arrow icons on the right of the dialog to move it up or down.

The list on the left of the dialog has icons you can add to your toolbar. The drop-down above the left list allows you to select different lists of icons. You can double click an icon on the left to add it to your toolbar list on the right.

The top option on each list is called a Separator. This is a thin vertical line that you can use to separate different sections of your toolbar.  

In Figure 5, I have highlighted in yellow the useful lists. If you use macros you can run them off an icon on the toolbar.

Figure 5.

If you make a change, make sure you click OK (bottom right of dialog) to ensure Excel captures the changes.

(In a recent CPA training session I ran, one attendee asked why he wasn’t told about the Quick Access Toolbar 12 years ago, when it was introduced.)

2. Is there a quick way to copy between sheets in the same workbook?

From Excel 2013 onwards, you have the ability to put Excel on separate screens. Back when these articles were first written dual screens were rare. These days, dual screens are common and allow you to see multiple Excel files at the same time.

If you click the View ribbon, one of the options is New Window as per Figure 6.

Figure 6.

Clicking this icon will open a new window of the current file with the same sheet visible. In the new window you can select a different sheet. You can also move that window to a separate screen. You can then easily copy between the screens.

The companion video will demonstrate a technique to copy ranges by dragging them between windows using the Ctrl and Alt keys.

Be careful using multiple windows for the same file because the new windows that open do not have the same display options. For example, if you had used freeze panes in the file, the new windows will not have those freeze panes settings. This means you need to close the windows carefully.

Each window will have a number on the end of the file name in the top section of the window. See the example in Figure 7.

Figure 7.

The file is called Test. Note the numbers following the name in the green banners at the top of each window.

To ensure you keep your display options, always close the windows with the highest numbers first (you could have more than two). Use the X in the top right corner to close the window.

If you save and close the file when there are multiple windows visible they will be visible when you open the file again.

3. How does Excel correct my typing errors?

It’s not magic, although it seems like it sometimes. There is an option called AutoCorrect in Office that applies to Excel. AutoCorrect has a list of commonly misspelled words with their correct spelling. It identifies errors and corrects them as you type. 

You can use this feature to your advantage in three ways (and these apply to the rest of Office).

a) You can add non-standard words that you frequently spell incorrectly so that it fixes them automatically.

b) You can create your own shortcuts to create common phrases or sentences. (I used shortcuts when I wrote my book.)

c) You can use shortcuts to help create formulas. This type is specific to Excel and is like b), but it wouldn’t be used in the other Office apps.

You use the same interface to create all three types.

There are a couple of ways to display the AutoCorrect dialog.

Press in sequence (do not hold the keys down) Alt T A. This is an old shortcut that still works in the current Excel version.

Otherwise, click the File ribbon Tab, click Options (left-hand side) and then click Proofing (left-hand side) and then click the AutoCorrect Options button. As you can see, the above keyboard shortcut is quicker.

Figure 8.

Figure 9 has an example of a) – I usually make a typo in the word “regards”. This fixes it automatically. Click Add to add the new combination to the settings.

Figure 9.

Figure 10 has an example of b) where I want to enter p-t to display the word PivotTable.

Figure 10.

Figure 11 has an example of c) where you want reduce typing effort when creating a function, in this case the INDEX function.

Figure 11.

In Figure 12 you can see how this works. You type =iii in a cell and then press the space bar and the formula is entered.

Figure 12.

Warning for b) and c) examples: be very, very careful about the shortcut combinations you choose, as every time you type it, it will get replaced. As an example, my email is with iiNet. If I used ii instead of iii, I might have some issues. 

Note any changes you make to AutoCorrect are made on the PC or laptop you are using. They won’t be available on other machines. 

4. Can I create a range name that only works in a single sheet?

In general, range names apply throughout the file. It is unusual to create a name that only applies to a single sheet, but it is possible. 

As an example, let’s say we have three state sheets. Let’s assume each state has a flat payroll tax rate, but they are different rates. Each state sheet needs to use its own rate to calculate payroll tax. This is an example; it isn’t the best way to do this.

Each sheet has an input cell B2 for the rate.

Normally you type the range name in the Name Box (top left of Figure 13) and press Enter to create a name. When you want to create a sheet-based name you must prefix the name with the sheet name followed by the ! symbol – see Figure 13 as an example for NSW sheet.

Figure 13.

When copying formulas that use these names between sheets you have to be careful as there can be conflicts, and warning dialogs will display. Once created in each sheet, then using the PRT_Rate name will use the rate for that sheet.

In general I do not recommend using sheet level range names. All range names should be at the workbook level, which avoids or reduces conflicts and confusion.

You can see the scope of these names in the Name Manger (Formulas tab) in Figure 14. Normally the scope is Workbook.

Figure 14.

If you have a sheet with a space in its name, you must use an apostrophe before and after the name. See Figure 15 for an example for the sheet name Sth Aust.

Figure 15.


Like what you're reading? Enter your email to receive the fortnightly INTHEBLACK e-newsletter.

Recommended for You

READ MORE

Opening the vault on Excel, part 3


READ MORE

Opening the vault on Excel, part 4


READ MORE

Opening the Vault on Excel, part 5


November 2019
November 2019

Read the November 2019 issue of INTHEBLACK magazine.

Each month we select the must-reads from the current issue of INTHEBLACK. Read more now.

CONTENTS