Computer Science Homework Help

You are to create the consolidated workbook and SmartArt graphic for Vegas Tourism shown in Figure 7–121

.Perform the following tasks:

1.Open a File Explorer window and double-click the file named SC_EX_7-6.xlsx from the Data Files. 

2.Add a second sheet to the workbook, named SmartArt Graphic. Color the tab red. 

3.To import a text file:a.Make the Vegas Tourism worksheet active, and then select cell A5. Use the Data tab to import the text file named, Support_EX_7_AirlineA.csv, from the Data Files.b.When Excel displays the dialog box, click the Load arrow and then click Load To.c.When Excel displays the Import Data dialog box, click the Existing Worksheet option button to select it. Click OK (Import Data dialog box).d.Click the new table and then use the Table Tools Design tab to remove the banded rows and the header row. If Excel displays a dialog box, referencing the header row, click Yes. Close the Queries & Connections pane.e.Select cells A5:E5. Right-click the selection and then click Delete on the shortcut menu. When Excel displays the Delete dialog box, click the ‘Shift cells up’ option button, if necessary. Click OK (Delete dialog box).f.Use a separate area of the worksheet to trim the extra spaces from cells B5:B8. Copy the trimmed values back to the range. Delete the data you no longer need.

4.To import an Access table:a.Select cell A9. Import the Access file named, Support_EX_7_AirlineB.accdb, from the Data Files. b.When Excel displays the dialog box, click the Las Vegas Flights February table. Notice that the table has no airline column. Click the Load arrow and then click Load To.c.When Excel displays the Import Data dialog box, click the Existing Worksheet option button and then choose to import into cell B9, as the data does not contain the airline designation. Click OK (Import Data dialog box).d.Remove the banded rows and the header row. If Excel displays a dialog box, referencing the header row, click Yes. Close the Queries & Connections pane. Delete the resulting empty row.e.Type   B   in cell A9. Copy cell A9 to cells A10:A12.

5.To paste data from Word:a.Select cell A20. Start Word and open the file named, Support_EX_7_AirlineC.docx. In the Word table, select all of the data in columns 2 through 5 and copy it. Close Word.b.Return to Excel and use the Paste Special command to paste the data as Text (Paste Special dialog box) in cell A20.c.Copy the Excel range A20:D23. Click cell A13 and transpose the data while pasting it.d.Delete the original imported data in cells A20:D23.e.Cut the data in cells B13:D16 and paste it to cell C13 to move it one column to the right.f.Select cells A13:A16. Click the ‘Text to Columns’ button (Data tab | Data Tools group). In the Convert Text to Columns Wizard dialog box, choose the Fixed Width option button, and then click Finish.

6.To import Web data:a.Select cell A17. Click the From Web button (Data tab | Get & Transform Data group). In the URL box, enter the location of the HTML data file, such as c:usersusernamedocumentscis 101data filesSupport_EX_7_AirlineD.html, and then press enter.b.When Excel displays the Navigator dialog box, click the name of the table, February Flights to Vegas – Top Four Cities, and click the Web View tab. Click the Edit button (Navigator dialog box) to open the Power Query Editor Window. c.Remove the Airport column. Click the ‘Close & Load’ arrow (Power Query Editor Home tab | Close group) and then click the ‘Close & Load To’ command to close the Power Query Editor Window.d.When Excel displays the Import Data dialog box, if necessary, click the Existing worksheet option button, and then click OK. e.Remove the banded rows and the header row. If Excel displays a dialog box, referencing the header row, click Yes.f.Delete the blank row. Close the Queries & Connections pane.

7.Use the fill handle to replicate cell F4 to F5:F20.

8.Copy the formatting from C4:F4 to C5:F20.

9.Delete row 4.

10.Select cells F4:F19. Click the Quick Analysis button and create a total in cell F20. In cell A20, type the word,   Total.

11.Adjust column widths as necessary. If directed by your instructor, insert your name and course number in cell A21.

12.Go to the SmartArt Graphic sheet. Click the Gridlines check box (View tab | Show group) to turn off gridlines.

13.Click the ‘Insert a SmartArt Graphic’ button (Insert tab | Illustrations group). Click List in the left pane (Choose a SmartArt Graphic dialog box) and then click Picture Strips in the middle pane. Click the OK button to insert the graphic.

14.Click the Add Shape button (SmartArt Tools Design tab | Create Graphic group).

15.One at a time, using the Text Pane, replace the word, Text, with the words, Denver, Newark, Chicago, and Los Angeles, respectively.

16.Change the SmartArt style to Brick Scene.

17.One at a time, click the picture icon in each part of the graphic, and search the web for a graphic related to the city. Make sure you review the license to ensure you can comply with any copyright restrictions.

18.Resize the graphic to fill the area D1:L24..