Logo
HOW TO
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
 
 
Windows 7

Microsoft Excel 2010 : Using Formulas - Using Names To Simplify References, Inserting Formulas into Tables

12/19/2012 4:27:14 PM

1. Using Names To Simplify References

It can be difficult to remember what cell you have a specific entry in, such as a tax rate, when you’re writing a formula. And if the cell you need to reference is on another sheet, you have to be very careful writing out the reference properly, or you must use the mouse to go to the sheet and select the cell.

It would be much simpler if you could just use the word TaxRate in your formula—and you can, by applying a Name to the cell. After a name is applied to a cell, any references to the cell or range can be done by using the name instead of the cell address. For example, where you once had =B2*$H$1, you could now have =B2*TaxRate, assuming H1 was the cell containing the tax rate.

The Web App won’t allow you to create new names, but you can use existing ones.


There are only a few limitations to remember when creating a name:

  • The name must be one word. You can use an underscore (_), backslash (\) or period (.) as spacers.

  • The name cannot be a word that might also be a cell address. This was a real problem when people converted workbooks from legacy Excel because some names, such as TAX2009, weren’t cell addresses then, but caused problems when you opened a workbook in 2007 or 2010. So name carefully!

  • The name cannot include any invalid characters, such as ? ! or -. The only valid special characters are the underscore (_),backslash (\) and period (.).

  • Names are not case sensitive. Excel will see “sales” and “Sales” as the same name.

  • You should not use any of the reserved words in Excel. These are Print_Area, Print_Titles, Criteria, Database, and Extract.


Applying and Using a Name in a Formula

To apply a name to a cell and then use the name in a formula, follow these steps:

1.
Select the cell or range you want to apply the name to.

2.
In the name field, type in the name, as shown in Figure 1.

Figure 1. After you select a cell or range, you can type a name for it in the Name field. Column F shows the name used in the formulas in column E.

If the name already exists in the workbook, typing it in will select the cell it refers to.


3.
Press Enter for Excel to accept the name.

4.
Go to the cell containing the formula that should reference the name.

5.
Replace the cell or range address with the name you just created, or type in a new formula from scratch using the name where you would use the cell or range address.

If you can’t remember the name assigned to a range, you can look it up by clicking the drop-down in the name field or by selecting Formulas, Defined Names, Use in Formula, which opens up a drop-down of available names. You can also go to Formulas, Defined Names, Name Manager, which not only lists the defined names, but shows the range they apply to.

Inserting Formulas into Tables

When your data has been defined as a Table, Excel will automatically copy new formulas down the rest of the cells in the column.

Entering a Formula in a Table

To add a new calculated column to a table, follow these steps:

1.
Select the first data cell in the column adjacent to the rightmost column of the table. This would be cell I2 in Figure 2.



Figure 2. Type a formula in the first cell of a table column and Excel will copy it down the rest of the column.

2.
Enter your formula for the selected cell.

3.
Press Enter and Excel copies the formula down the column.

After entering the formula, a lightning bolt drop-down appears by the cell. If you don’t want the automated formula copied, select Undo Calculated Column or Stop Automatically Creating Calculated Columns.

Other -----------------
- Microsoft Excel 2010 : Using Formulas - Copying a Formula, Formula Operators
- Microsoft Word 2010 : Proofing a Document - Finding and Replacing Text
- Microsoft Word 2010 : Checking Your Spelling and Grammar
- Microsoft Visio 2010 : Finding and Managing Shapes (part 3)
- Microsoft Visio 2010 : Finding and Managing Shapes (part 2) - Searching for Shapes
- Microsoft Visio 2010 : Finding and Managing Shapes (part 1) - Navigating the Shapes Window, Opening Other Stencils
- Managing Windows 7 : Adding or Deleting User Accounts
- Managing Windows 7 : Changing Indexing Options, Managing Your Credentials
- Microsoft Project 2010 : Comparing Costs to Your Budget (part 3) - Associate Resources with Their Budget Type, Compare Budget Resource Values
- Microsoft Project 2010 : Comparing Costs to Your Budget (part 2) - Enter Budget Cost and Work Values
 
 
REVIEW
- First look: Apple Watch

- 10 Amazing Tools You Should Be Using with Dropbox
 
VIDEO TUTORIAL
- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 1)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 2)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 3)
 
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS
Popular keywords
HOW TO Swimlane in Visio Visio sort key Pen and Touch Creating groups in Windows Server Raid in Windows Server Exchange 2010 maintenance Exchange server mail enabled groups Debugging Tools Collaborating
Top 10
- Microsoft Excel : How to Use the VLookUp Function
- Fix and Tweak Graphics and Video (part 3) : How to Fix : My Screen Is Sluggish - Adjust Hardware Acceleration
- Fix and Tweak Graphics and Video (part 2) : How to Fix : Text on My Screen Is Too Small
- Fix and Tweak Graphics and Video (part 1) : How to Fix : Adjust the Resolution
- Windows Phone 8 Apps : Camera (part 4) - Adjusting Video Settings, Using the Video Light
- Windows Phone 8 Apps : Camera (part 3) - Using the Front Camera, Activating Video Mode
- Windows Phone 8 Apps : Camera (part 2) - Controlling the Camera’s Flash, Changing the Camera’s Behavior with Lenses
- Windows Phone 8 Apps : Camera (part 1) - Adjusting Photo Settings
- MDT's Client Wizard : Package Properties
- MDT's Client Wizard : Driver Properties
 
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
2015 Camaro