- 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
- Changing links to different workbooks
- Analyzing data by using the Quick Analysis lens
- 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 that 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 that you need to examine, you and your colleagues can rely on the named ranges to understand the goal of a calculation.
Excel 2013 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 that 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 that 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.