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.
- Launch Internet Explorer and navigate to the https://intranet.contoso.com web site.
- Hover over the Gears Sales History spread sheet in the Shared Documents list view.
Select the down arrow and click Edit in Microsoft Excel.
Figure 1
Edit in Microsoft Excel
- Go to the File Menu in Excel 2010.
- Select Options.
- Select Customize Ribbon from the left pane.
- Select All Commands from the Choose Command From drop down.
Locate Name Manager Command.
Select Insert from the left pane
- Click New Group button to add a new group to the Insert tab.
- Rename the New Group to Name Manager.
Click Add to add the Name Manager.
- Click OK to close Excel Options Dialog.
- Select the Item Information sheet.
- Select the cell containing Big Gear in the Name column.
Click the Name Manager button on the Insert Ribbon.
Click the New button to open the New Name dialog.
Figure 6
New Manager dialog
- Set the Name to “BigGear_Discount”. You cannot use spaces in the name.
Select the Discount cell for Big Gear in the Refers to property.
- Repeat steps 2-4 for each item in the
- Click the New button to open the New Name dialog
- Name the New Name “BigGear_ActualSalesAndMargin”
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
- Click OK.
- Repeat steps 8-11 for each item in the spreadsheet using the actual item name prepended to _ActualSalesAndMargin.
- Click Close to close the Name Manager.
Save and close the workbook.