Publication Date: August 11, 2010  

---

If you want to send me an email...

---

   

CAUTION: Make sure you save a copy of your spreadsheet before trying these tips,
just in case you make an error or the procedure doesn't produce the results you want.

Use 'Easy-To-Remember' Names In Your Formulas (XL2003-XL2007)

Very often spreadsheets contain lots formulas that refer to the same value such as a fixed tax rate. 

Maybe you've already realized that, rather than typing that same rate into every formula, it is easier to to enter the tax rate into one specific cell and have all formulas reference that same cell. Now whenever the tax rate changes, you simply change one cell rather than all those individual formulas. Sure saves a lot of work doesn't it?

Defining Range Names:

Well, to take this idea a step further, you can name this cell and then use that 'easy-to- remember' name instead of the cell reference in your formulas. Range names are much easier to remember when creating formulas and they also make it easier for users to understand what the formulas do.
 
A range name can contain letters, numbers, and underscores, but no spaces, punctuation or special characters.
 
To create a range name, select the cell or range of cells, then, in Excel 2007, click the Formulas tab, Name Manager, New , type the name in the Name field, click OK then Close. Note that in the Excel 2007 Name Manager, you can specify whether the name will be available to be used in just the current worksheet or to the entire workbook.  In Excel 2000-2003, from the Insert menu select Name, Define and type a name in the text box.

Another even easier way to add a name to a range is to select the cell or range of cells, then type a name in the 'Name Box' (i.e. that small white space to the left of the formula bar) and press Enter.

In our example, we will assign the name 'tax_rate' to a single cell containing a tax rate that will be used throughout the worksheet. Once we have defined the name, we can use the name instead of the cell reference i.e. =ROUND(C4*tax_rate,2) in our formulas. 

Applying Range Names:

After you have assigned a name to a cell or range, all formulas that reference this range will still show the range reference. To update these formulas to show the name rather than the range reference, you will need to 'apply' the name. In Excel 2007, from the Formulas tab , click the small arrow to the right of the Define Name command in the Defined Names group. Click Apply Names and select the name(s) you want to apply and click OK. In Excel 2000-2003, from the Insert menu select Name, Apply, select the defined name and click OK.


Deleting Range Names:

To delete a range name in Excel 2007, click the Formulas tab, Name Manager, select the name(s) you want to delete, click Delete, OK then Close.

In Excel 2000-2003, to delete a range name, from the Insert menu select Name, Define, select the name(s) you want to delete and click the Delete button.

If you haven't been using range names, I hope you are beginning to see the significant benefits of creating easy-to-read and easy-to-update formulas in your worksheets.

---

What? A Keyboard Shortcut For Print Preview !? (XL2007)

Everyone uses Print Preview - a lot. If you've been using Excel for some time and if you've been getting my newsletter for some time, I would like to think you've taken my advice and created a custom toolbar(s) to hold your favourite (yes, in Canada favourite contains a U) and frequently-used Excel commands.

Also, you've probably often heard me harping on the benefits of learning a 'mouse method' and a 'keyboard method' for your frequently used commands. If you're two hands are on the keyboard, a keyboard shortcut is often quicker than reaching for your mouse. And the reverse is true for when you are in 'mouse mode'.

Rather than clicking the Print Preview button, if you've added it to your Quick Access Toolbar or going through the Office Button, Print, Print Preview (if you haven't), in Excel 2007 you can simply press CTRL+F2 to open the Print Preview window.

When I recently discovered this shortcut, I wondered if I had been overlooking this for all those years. However, as far as I can figure, it became available only with the release of Excel 2007.

Quickly Close Browser Tabs With Your Mouse

Now that just about every Internet browser is using tabbed browsing and the default setting is to open new windows in in a new tab, chances are that at any given time you've got several tabs open.

When you try to close your browser, you get a warning message asking if you want to close all tabs or just the current one. If you need to choose which tabs to close, clicking that itty bitty 'x' on each tab takes some precise mouse moves.

An easier way to close those browser tabs, is to point to the tab ( a bigger target) and click the middle mouse button or your mouse wheel. And if you start by clicking the leftmost tab you want to close, you can close most of the tabs without even moving your mouse.

---