- By Curtis Frye
- Understanding Formulas and Cell References in Excel
- Creating Simple Cell Formulas
- Assigning Names to Groups of Cells
- Using Names in Formulas
- Creating a Formula That References Values in an Excel Table
- Creating Formulas That Reference Cells in Other Workbooks
- Summing a Group of Cells Without Using a Formula
- Creating a Summary Formula
- Summing with Subtotals and Grand Totals
- Exploring the Excel Function Library
- Using the IF Function
- Checking Formula References
- Debugging Your Formulas
Using Names in Formulas
When you define a named range, you create a shortcut that you can use to refer to a group of cells. A great way to use named ranges is in formulas. Instead of entering the references of every cell you want to use in your calculation, you can type the name of the range. When you reference named ranges in formulas, your formulas are shorter and easier to understand. Rather than seeing a series of cell references you need to examine, you and your colleagues can rely on the named ranges to understand the goal of a calculation.
Excel 2010 further streamlines formula creation with Formula AutoComplete. Remember that when you start typing a value into a cell, Excel examines the previous values in that column and offers to let you complete the entry by pressing Tab or Enter. Now, when you start typing a named range’s name into a formula, Excel recognizes that you might be entering a named range and displays a list of named ranges (as well as built-in functions) available in the active workbook. All you have to do is click the named range you want, and it’s included in the formula immediately.
Create a Formula with a Named Range
Click the cell in which you want to enter a formula.
Type = followed by the formula you want. When you want to use a range that has a name, start typing the name instead of the cell address.
Click the named range in the Formula AutoComplete list that appears.