Excel: Naming Cells and Ranges Feb25 '04

This article assumes a basic to intermediate knowledge of Microsoft Excel 97 or later.

Excel, as known by millions, is a top–notch spreadsheet program that can do anything from performing simple calculations to developing extensive databases. Excel’s power lies within the users intentions, meaning there isn’t much Excel can’t do.

Many Excel users are familiar with cell references. After all, without cell references, it is very hard to interact with your spreadsheet. Cell references allow users to pinpoint a cell’s exact location, and use the value within that cell in a formula, or in another location.

You can select specific cells, or cell ranges. There are two ways to perform these selections:

A typical cell range looks something like this:

B2:B13

 A typical cell range

This simple cell reference selects all the cells that occupy the range B2 to B13. Of course, in order to do anything with that range, you have to use it in a formula:

=SUM(B2:B13)

 A formula entered in a cell

The formula above calculates the sum of all values located within cells B2 to B13. But this much you already know.

Excel has a useful feature, which allows you to name a cell or cell range, and use the name in a formula, rather than the cell numbers.

What benefit does this have, you wonder? This has enormous benefits and possibilities.

Often times dealing with cell numbers can be confusing. It’s much easier to remember names than numbers. Among the many benefits are:

For example:

= B2 - B13
# Vague and confusing

= Income - Taxes
# This is more understandable and intuitive

In order to use cell names, you must go to Insert » Name » Define (or press Ctrl+F3). A dialog box pops up, where you can define names that correspond to specific cells or cell ranges.

The Define Name dialog box

Type your custom name in the top box, and in the Refers to: box, you can enter the exact cell address, or cell range.

Now, when you want to reference that value, all you have to do is type in the name! This can be extended further to include entire formulas. Essentially, you can put whatever you want in the Refers to: box, and whenever you call that name in your worksheet, the value you entered will be used.

This is quite fun, and helps you understand the powers that Excel can perform. Play around with it, and see what you come up with. And remember – this is only the tip of the iceberg!

Categories: Software , Tutorials

Add Feedback (view all)

Leave feedback

Feedback

Input format: The editor controls below will assist with Markdown syntax.

Status

Sub-status

Your info

Exactly what I was looking for. Thank you! ... Read more.

matthom is published and produced by Matt Thommes - an independent publishing enthusiast, mobile blogger, content creator, informative writer, web developer from Chicago. Never one to conform, Matt intends to promote the effect the web has on our lives, in an effort to intensify, instruct, and clarify all that is happening around us.

Contact Matt

Similar Entries

Stats

424 unique visits since August 2008

Syndicate

Advertisements