Excel 2016

Problem: You are working for a home furnishings store. They want you to create a workbook that is created from a company template. You are then to import data provided in various files, consolidate the data, and insert and format a picture SmartArt displaying a list of the stores main departments.

Instructions: Perform the following tasks:

1. Start Excel. Open the Excel Chapter 7 - Lab Test A - Template file from the Data Files provided in the location specified by your instructor. Save the template as a workbook using the file name, Lab Test A - Excel Chapter 7.

2. Add three additional worksheets after Sheet1, and then paste the Sheet1 contents to the three empty sheets.

3. From left to right, rename the sheet tabs North, South, Central, and Consolidated. Change the subtitle on all four tabs from branch to the name of the corresponding tab. Change the title style for each title area in the first three sheets to Blue, Accent 1 for North, Light Green for South, and Purple for Central. Color all four tabs to be similar to their title areas.

4. On the North worksheet, select cell B4. Import the comma-delimited text file, Excel Chapter 7 - Lab Test A - Data 1, from the Data Files. In the Text Import Wizard - Step 2 of 3 dialog box, click the Comma check box to select it and then click Tab to clear the check box; otherwise accept the default settings. In the Import Data dialog box, click the Properties button. In the External Data Range Properties dialog box, click ‘Adjust the column width’ to clear the check box. Make sure ‘Overwrite existing cells with new data, clear unused cells is selected’. Import the text data to cell B4 of the existing worksheet.

5. On the South worksheet, select cell B4. Import the Access database file, Excel Chapter 7 - Lab Test A - Data 2, from the Data Files. Choose to view the data as a table, and insert the data starting in cell B4 in the existing workbook. Accept all of the default settings to import the data. Right-click any cell in the table, point to Table, and then click Convert to Range. Click the OK button to permanently remove the connection to the query. Copy and paste the values from cell range B9:E10 to B4:E5. Delete rows 8-10. If necessary, adjust column widths to display all the data.

6. Start Microsoft Word, and then open the Word file, Excel Chapter 7 - Lab Test A - Data 3, from the Data Files. Copy all of the data in the table except for the first row. Switch to Excel. Select the Central worksheet. Select cell A13, and then using the Paste Special command, paste the data as text into the Central worksheet. Close Word. Copy cell range B13:D16. Select cell B4, and then using the Paste Special command, paste the transpose of the data. Delete rows 13-16. Adjust the column widths as necessary to display all of the data.

7. On the Consolidated worksheet, use the SUM function, 3-D references, and copy and paste capabilities of Excel to total the corresponding cells on the three branch sheets. First, compute the sum in cell B4 and then compute the sum in cell B5. Copy the range B4:B5 to the range C4:E5. The Consolidated sheet should resemble Figure E7A – 1.

8. On the Consolidated worksheet, insert a SmartArt graphic using the Picture type, and select the Picture Frame layout type (column 1, row 3) in the layout area in the Choose a SmartArt Graphics dialog box. In the first strip, insert a furniture image from Office.com Clip Art. For the text, enter Furniture. In the second strip, insert an electronics picture from Office.com Clip Art, and enter the text Electronics. In the third strip, insert a kitchen picture from Office.com Clip Art, and enter the text Kitchen. Add a fourth strip. In the fourth strip, insert a Bath picture from Office.com Clip Art, and enter the text Bath.

9. Apply the Polished SmartArt Style to the SmartArt. Position the SmartArt so that it is centered below the data on the worksheet. It should resemble Figure E7A – 1.

10. Change the document properties as specified by your instructor. Change the worksheet header with your name, course number, and other information as specified by your instructor.

11. Save the changes to the workbook.

12. Submit the results according to instructions provided by your instructor.

Figure E7A – 1

Page 1