Exercise 1: Modify an Existing Excel Workbook for Data Access

In this exercise you will use Excel 2010 to modify an existing Excel Workbook by adding named ranges to facilitate data access. Note: The SupportingFiles folder contains modified copy of the workbook. To skip this task overlay the existing copy of the workbook with the modified copy.

Task 1 – Adding the Name Manager Button to the Excel 2010 Ribbon

1. Launch Internet Explorer and navigate to the https://intranet.contoso.com web site.
2. Hover over the Gears Sales History spread sheet in the Shared Documents list view.

3. Select the down arrow and click Edit in Microsoft Excel.

   Figure 1

   Edit in Microsoft Excel

4. Go to the File Menu in Excel 2010.
5. Select Options.
6. Select Customize Ribbon from the left pane.
7. Select All Commands from the Choose Command From drop down.

8. Locate Name Manager Command.

   Figure 2

   Name Manager

9. Select Insert from the left pane

   Figure 3

   Insert node

10. Click New Group button to add a new group to the Insert tab.
11. Rename the New Group to Name Manager.

12. Click Add to add the Name Manager.

    Figure 4

    Add>> button

13. Click OK to close Excel Options Dialog.

Task 2 – Adding the Name Manager Button to the Excel 2010 Ribbon

1. Select the Item Information sheet.
2. Select the cell containing Big Gear in the Name column.

3. Click the Name Manager button on the Insert Ribbon.

   Figure 5

   Name Manager

4. Click the New button to open the New Name dialog.

   Figure 6

   New Manager dialog

5. Set the Name to “BigGear_Discount”. You cannot use spaces in the name.

6. Select the Discount cell for Big Gear in the Refers to property.

   Figure 7

   New Name dialog

7. Repeat steps 2-4 for each item in the
8. Click the New button to open the New Name dialog
9. Name the New Name “BigGear_ActualSalesAndMargin”

10. Select the Actual Sale Price cell and the Profit Margin cell for Big Gear for the Refers to text box.

    Figure 8

    Actual Sale Price

11. Click OK.
12. Repeat steps 8-11 for each item in the spreadsheet using the actual item name prepended to _ActualSalesAndMargin.
13. Click Close to close the Name Manager.

14. Save and close the workbook.