Using Named Ranges in Formulas
Duration: 10 to 20 minutes.
In this exercise, you will practice naming cells and will use named cells in a formula.
- Open Using Names.xlsx from your Excel2013.2/Exercises folder.
- Name cell B1 "QuarterlyIncome".
- Use the Create from Selection command to name cells in the range A3:E8.
- Using only names in your formulas, answer the questions in column A of the worksheet.
- To name cell B1:
- Select cell B1.
- Type "QuarterlyIncome" in the Name Box and press Enter:.
- To name cells A3:E8:
- Select cells A3:E8.
- On the FORMULAS tab, in the Defined Names group, click the Create from Selection command:
- In the Create Names from Selection dialog box, check Top row and Left column and click OK:
- To answer the questions in column A of the worksheet:
How much was spent on Clothes and Fun Stuff in January? Type "=Jan Clothes+Jan Fun_Stuff".
How much was spent on Groceries in Jan and Mar? Type "=Jan Groceries+Mar Groceries".
What % of Quarterly Income did Electric account for from Jan to Mar? Type "=Total Electric/QuarterlyIncome".
Hint: If step 3 isn't working for you, make sure that you named cell B1 "QuarterlyIncome" with no space and that you did not include a space within "QuarterlyIncome" in the formula for B13.