Using Formulas and Functions in Microsoft Excel 2013
- 4/15/2013
- 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
Creating a formula that references values in an Excel table
In previous versions of Excel, it was a challenge to create named ranges that included an entire column in a data list. Suppose that you created a named range that encompassed the existing cells in a data column, such as the cell range A3:A44; if you added data to cell A45, you would need to change the cells in the named range’s definition. Yes, there is a complicated way to create a dynamic named range in Excel 2003 and earlier versions, but you don’t have to worry about it in Excel 2013. All you need to do is create an Excel table (as shown in "Creating an Excel table" on page 52) and select the headers of the columns that contain the data that you want to summarize in your formulas.
Create a formula with an Excel table reference
Click the cell in which you want to create the formula.
Type =, followed by the function to include in the formula and a left parenthesis; for example, =SUM( would be a valid way to start.
Type the name of the Excel table.
Type a left square bracket.
Click the name of the table column.
Type a right square bracket, a right parenthesis, and press Enter.