Excel Tip: 10 Quick Range Name Tricks

Excel Tip: 10 Quick Range Name Tricks

Didn't find your answer?

For more useful spreadsheet tricks from AccountingWEB's US managing editor, look in the Expert Guides section for 'Gail Perry's Excel Almanac'.

If you've ever used a range name in your Excel worksheet, you already know what a time saver this device is. Here are some suggested uses for range names that may speed up the time you spend on your worksheet even more. Don't worry if you don't know how to name a range - that's covered in the first tip!

1. Name a range in a hurry by first selecting (highlighting) the range, then clicking in the Name Box in the upper left corner of your Excel screen, and typing the name for the selected range. Press Enter when finished to save the name.

2. Go to a range quickly by clicking on the down arrow in the Name Box at the upper left corner of your Excel screen (which displays a list of all range names in the worksheet) and clicking on the name of the range to which you want to go. The entire range will be highlighted and the upper left cell of the range will be active.

3. Use a range name in a formula. Instead of typing a range of cell references, =SUM(C3:C249), for example, name that range, then the next time you need to refer to the range in a formula, simply type =SUM(expenses). Not only is the name easier for you to remember than the cell references, but also the formula will make more sense to an observer who didn't help create the worksheet.

4. Use a range name to provide an absolute reference. Range names are always absolute. So if you copy the formula, =C12*expenses, down one cell, the new formula will read, =C13*expenses, and expenses will be defined in the same manner for each formula.

5. Move a range by highlighting the range (see trick #2), then selecting Edit, Cut (or click the little scissors on the toolbar). Click once in a single cell of a new location, then choose Edit, Paste (or click the little clipboard button), and the entire range will relocate.

6. Copy a range by highlighting the range (see trick #2), then selecting Edit, Copy (or click the copy button on the toolbar). Click once in a single cell of a new location, then choose Edit, Paste (or click the little clipboard button), and the entire range will be copied.

7. Select a range for formatting by quickly highlighting the range (see trick #2). Once the range is highlighted, any formatting gesture you make, such as applying a shaded background or changing the number format to remove decimal places, will apply to the entire range.

8. Show sum of a range by selecting the range, then looking down in the Status bar at the bottom of the screen. On the right side of the bar will appear a sum of the numbers in the selected cells.

9. Print a range by first naming the range, then selecting it (see trick #2), then choosing File, Print from the menu, and choosing the Selection option from the Print what area of the Print window that appears.

10. Remove a range name from your worksheet by choosing Insert, Name, Define from the menu. Click on the name you want to remove from the list provided, then click the Delete button.

Replies (4)

Please login or register to join the discussion.

aw_logo_2019
By Accounting WEB
28th Oct 2000 18:54

other uses gor range names
The range name can also help when using lookups or Pivot tables, enter the range name instead of the cell references

Thanks (0)
Replying to imbs:
avatar
By TXWizard
18th Sep 2015 22:25

Use Labeled Cells to Build Well Documented Formulas Fast

AccountingWEB wrote:
The range name can also help when using lookups or Pivot tables, enter the range name instead of the cell references

When I lay out a series of calculations to show, step by step, how I arrived at my answer, I label each cell that will be either an input or an intermediate result before I write any formulas. This has two objectives.

It forces me to plan my work and identify shortcuts where rearranging the order in which intermediate values are calculated can reduce the total number of steps.Using the tool for generating range names from adjacent labels, the formulas practically write themselves, and, in any event, can be written by entering parts of label names, allowing IntelliSense to finish them.

For example

Income StatementGross Sales$$$$Cost of Goods Sold$$$$Gross Profit=Gross_Sales-Cost_of_Goods_Sold

With help from IntelliSense, entering the formula for Gross Profit might be as few as four keystrokes (=G-C). Moreover, the formula documents itself. In the same manner, the above can be easily extended to create a complete income statement.

      

 

Thanks (0)
avatar
By nig24
26th Oct 2000 10:36

Number 8
The status bar will not always show the sum. If you right click on the status bar you can choose what it will show from the following options:- Average, Count, Count Num, Max, Min and of course Sum.

Thanks (0)
avatar
By User deleted
26th Oct 2000 14:21

Use a range name in a formula quicker
When entering a formula use the F3 key & then select the range name, instead of typing it.

Thanks (0)