1

Start Excel. Download, save, and open the workbook file Exploring_e09_Grader_IR.xlsx.

2

On the Data tab, in the Get External Data group, use the From Text command to insert the text from the downloaded, comma-delimited text file Exploring_e09_Grader_IR.txt starting in cell A1 of the Sheet1 worksheet.

3

Insert a new column between the two existing columns. Separate Column A into two columns, Villa and Location, using the hyphen. Change the name of the worksheet to Beaches.

4

Adjust the width of column A to 13.00 (96 pixels). Adjust the width of column B to 10.00 (75 pixels). Adjust the width of column C to 20.00 (145 pixels). Apply bold formatting with Dark Blue, Text 2, Lighter 80% fill color to the column labels in row 1.

5

Insert a new worksheet named North Shore to the right of the Beaches worksheet. Create a Web query in cell A1 of the new worksheet to the villa table at http://media.pearsoncmg.com/ph/bp/myitlab/docs/Exp09GraderIRCD/T1Rentals.mht.

6

Apply bold formatting with Dark Blue, Text 2, Lighter 80% fill color to the column labels in row 1 of the North Shore worksheet.

7

Type LOCATION in cell H1 of the North Shore worksheet. Type STATUS in cell I1 of the North Shore worksheet. Insert a function in cell I2 that will display the status from column G with initial capitalization only. Copy the function to the range I3:I11. Adjust the width of column I to 13.00 (96 pixels). Hide column G.

8

Create a nested function in cell H2 that looks up the location code from the Beaches worksheet, looking for an exact match, and combines the code with the Identifier number in the North Shore worksheet to display the result as, for example, GBB-6632. Copy the function to the range H3:H11. Adjust the width of column H to 10.00.

9

Insert a new worksheet named South Shore to the right of the North Shore worksheet. Create a Web query in cell A1 of the new worksheet to the villa table at http://media.pearsoncmg.com/ph/bp/myitlab/docs/Exp09GraderIRCD/SSRentals.mht.

10

Apply bold formatting with Dark Blue, Text 2, Lighter 80% fill color to the column labels in row 1.

11

TypeSTATUS in cell H1 of the South Shore worksheet. Insert a function in cell H2 that will replace the word RENTED in column G with Not Available. Copy the function to the range H3:H11. Adjust the width of column H to 13.00 (96 pixels). Hide column G.

12

Insert a new worksheet namedChoices to the right of the South Shore worksheet. Create a connection to import data from the e09villas.xml file. Make sure cell A1 is selected while creating the connection.

13

Edit the XML file in Notepad to include the Callaloo villa, location GBB-6632, and Status of Available using the correct XML tags after the two existing records. Save and close the file. Refresh the XML data only on the Choices worksheet (do not refresh all connections).

14

Ensure that the worksheets are correctly named and placed in the following order in the workbook: Beaches, North Shore, South Shore, and Choices. Delete the Sheet2 and Sheet3 worksheets. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.