|
|
|
Home > Excel Tutorials > How to use Names How to use Names in spreadsheetsFormulas can be hard to follow (because cell references such as A1 and B6 are rather cryptic). Fortunately there's an easy way to make your formulas more readable. You can give cells a name and then use that name in your formulas. If you do this, your spreadsheets will be easier to read and less error prone.In this article we'll look at a simple example. We'll create a simple spreadsheet to calculate a company's profit. Our formulas will use names instead of cell references. We want to calculate net profit, which is profit less tax. Profit is income less outgo. Our spreadsheet might look like this:  Fig. 1 - Spreadsheet Example Cell C10 contains the formula "=C6-C8". The formula in cell C6 is "=C3-C4". These formulas are correct but they're not very readable. It's much easier to understand something like "=Profit-Tax" rather than "=C6-C8". You might not think this is a big deal. And you'd be right if your spreadsheets are trivial like this one. But if you write complex spreadsheets then readability is very important. Let's give cell C3 a name. We'll call it Income. To do this: 1. Select cell C3 2. Go to the Insert menu and click on Name 3. Select Define on the pop up menu The Define Name dialog box will open. Type Income into the top text box so that it looks like this:  Fig. 2 - Defined Name Dialog Box Click on OK to finish. Then look at the Name Box (see below). Notice that it says Income instead of C3. This tells you that you can refer to C3 by this name in your formulas. You can also type the name directly into Excel's Name Box in the worksheet (circled in Figure 3).  Fig. 3 - Name Box Repeat this process for cells C4, C6 and C8 (naming them Outgo, Profit and Tax). Now we can use names in our formulas. We can replace "=C6-C8" with "=Profit-Tax" and "=C3-C4" with "=Income-Outgo":  Fig. 4 - Spreadsheet with Defined Names This picture shows how we've changed the formulas. We haven't changed the result; net profit is still 6,000. But names are a lot easier to follow than cell references. It only takes a few minutes to give cells a name. But this is time well spent. Your spreadsheets will be easier to check and to read. They'll be easier to maintain too. You'll appreciate this when you come back to spreadsheets that you've written months or years ago. Anyone who uses or checks your spreadsheets will thank you too. Why not get into the habit of using names? Don't wait, do it today. |
Other Tutorials
How do I find the last used cell in a column? (Part 1)
How do I find the last used cell in a column? (Part 2) How to Display Formulas in an Excel Worksheet Using Excel to Solve Simultaneous Linear Equations How to use Conditional Formatting How to Manually Install an Excel Add-in How to use Goal Seek Useful Worksheet Functions |
||||||||||||||||
UltraSleuth
