TEACHING EXCEL FOR DATA ANALYSIS: HANDS ON 14 Using range names in formulas

 Using the features that you learned about in this topic, you will complete a small sales worksheet.

1. Click on the link below to open the Microsoft Excel exercise document you can use to complete this exercise.

https://github.com/alcadelina/Excel-

2. To create the first range name, use your cursor to select cells B4:B6:

3. Next, type “Prices” inside the Name Box. Press Enter:

4. The selected range now has “Prices” as a range name:

5. Now, let’s try another method to create another range name. First, use your cursor to select cells C4:C6:

6. Next, click Formulas → Define Name:

7. The New Name dialog box is now displayed. Ensure that “Quantity” appears inside the Name text box and that the Scope drop-down menu is set to Workbook. Click OK:

8. The selected range now has “Quantity” as a range name:

9. You have one more range name to create. Use your cursor to select cells D3:D6:

10. Click Formulas → Create from Selection:

11. In the Create Names from Selection dialog box, ensure that the “Top row” checkbox is selected and click OK:

12. Next, you need to create a formula that will calculate the cost of the items (Quantity*Prices). Select cell D4:

13. Click inside the Formula Bar and type “=”:

14. Next, type “Prices” followed by an asterisk:

Note that because Prices is a range name, its text will appear blue in the Formula Bar and blue shading will appear around that range of data on the worksheet.

15. Still inside the Formula Bar, type “Q” and then double-click the Quantity result from the small menu that appears:

16. The Quantity name now appears within the Formula Bar in red text, with its associated range shaded in red in the worksheet:

17. Press Enter to apply the formula. You will see the results appear in cells D4 through D6:

(You may receive a “Formula Spilled” alert, indicating that the formula returned multiple values, so they were spilled into the neighboring blank cells. Because each of the named ranges contains more than one value, Excel must predict the correct calculation for each value.)

18. Save the current workbook as Activity 1-1 Complete and then close Microsoft 365 Excel to complete the exercise.

19. Now, you can check out an example of a completed document in the link below:

https://github.com/alcadelina/Excel-