Excel 2016 Chapter 4 Formatting, Organizing, and Getting Data Last Updated: 9/18/18 Page 1

USING MICROSOFT EXCEL 2016 Guided Project 4-3

Guided Project 4-3 Clemenson Imaging analyzes expense reports from field representatives as well as patient and image data. To complete the worksheets, you format data as a table and build an advanced filter. You import a comma-separated text file (.csv) and use the Subtotal command. Finally, you display data in a PivotTable.

Skills Covered in This Project • Format data as an Excel table. • Build an Advanced Filter. • Apply conditional formatting to filtered results. • Sort data by multiple columns.

• Import a comma-separated text file. • Use the Subtotal command. • Export data via the Clipboard. • Create a PivotTable.

This image appears when a project instruction has changed to accommodate an update to Microsoft Office 365. If the instruction does not match your version of Office, try using the alternate instruction instead.

IMPORTANT: Download the resource file(s) needed for this project from the Resources link. Be sure to extract the file after downloading the resource(s) zipped folder. Please visit SIMnet Instant Help for step-by-step instructions.

1. Open the ClemensonImaging-04 start file. Click the Enable Editing button. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor.

2. Copy the Past&Projected sheet before the Criteria sheet and name the copied sheet Adv Filter. 3. Create and format an Excel table.

a. Select cells A4:E60 on the Adv Filter sheet. b. Click the Quick Analysis tool and choose Tables. c. Click Table.

4. Apply a table style. a. Click cell A5 in the table. b. Click the More button [Table Tools Design tab, Table Styles group]. c. Select White, Table Style Medium 15.

Select Table Style Medium 15. 5. Create an output range for an Advanced Filter.

a. Select cells A4:E4 and copy and paste them to cell G4. b. Type Extract Range in cell G3 and set the font to Cambria 16 pt. c. Adjust column widths to show the labels.

6. Create an Advanced Filter. a. Click the Criteria sheet tab. b. Type >12/31/16 in cell A3 to find records after 2016. c. Type mri in cell B3. d. Type >12/31/14

in cell A4 to find records after 2014.

e. Type ct scan in cell B4. This criteria will find records dated 2017 or later for MRIs and records for CT scans after 2014 (Figure 4-90).

Step 1 Download start file

Excel 2016 Chapter 4 Formatting, Organizing, and Getting Data Last Updated: 9/18/18 Page 2

USING MICROSOFT EXCEL 2016 Guided Project 4-3

f. Click cell A5 on the Adv Filter sheet. g. Click the Advanced button [Data tab, Sort & Filter group]. h. Select the Copy to another location radio button. i. Verify that the List range is cells A4:E60. If the range is incorrect, click and drag to select the range

including the header row. j. Click the Criteria range box and select cells A2:B4 on the Criteria sheet. k. Click the Copy to box and select cells G4:K4 on Adv Filter sheet. l. Click OK in the Advanced Filter dialog box.

In some versions of Office, the above instructions to create an Advanced Filter may not work. If you experience this issue, complete instructions 6. a-e as listed below, then continue with instruction 6.f-l above. 6. Create an Advanced Filter.

a. Click the Criteria sheet tab. b. Type >Dec 2016 in cell A3 to find records after 2016. c. Type mri in cell B3. d. Type >Dec 2014 in cell A4 to find records after 2014. e. Type ct scan in cell B4. This criteria will find records dated 2017 or later for MRIs and records for

CT scans after 2014.

7. Sort data in the output range on the Adv Filter sheet. a. Right-click cell H5, choose Sort, and select Sort A to Z. b. Right-click cell G5, choose Sort, and

select Sort Oldest to Newest. This sorts the results by date, earliest date first.

8. Apply conditional formatting and borders. a. Select cells I5:I14. b. Click the Conditional Formatting

button [Home tab, Styles group]. c. Choose Highlight Cells Rules and

Greater Than. d. Type 750 and choose Green Fill with

Dark Green Text. e. Click OK. f. Select cells G5:K14 and apply All

Borders (Figure 4-91). g. Press Ctrl+Home.

9. Import a comma-separated values text file. a. Click the Expense Info sheet tab. b. Select cell A5. c. Click the From Text/CSV button [Data tab, Get & Transform Data group]. d. Find and select the ClemensonImaging-04.csv file, downloaded from the Resources link, in the

Import Data window. e. Click Import. f. In the Get Data Editor, click the Load arrow and select Load To…

Download Resources

Excel 2016 Chapter 4 Formatting, Organizing, and Getting Data Last Updated: 9/18/18 Page 3

USING MICROSOFT EXCEL 2016 Guided Project 4-3

g. Select Existing worksheet. Verify that cell A5 displays as the destination (Figure 4-92).

h. Click OK to import the data. i. Cut and paste the labels in row 4

to replace the labels in row 5. Delete row 4 to move the table up one row.

j. Select cell A4 and click the Convert to Range button [Table Design tab, Tools group].

k. Click OK to remove the query definition.

l. Select columns A:D and size each column to 12.11 (90 pixels) wide.

m. Deselect the columns.

9. Import a comma- separated values text file. a. Click the Expense Info sheet tab. b. Select cell A5. c. Click the From Text button [Data tab, Get External Data group]. d. Find and select the ClemensonImaging-04.csv file (downloaded from the Resources link) in

the Import Text File window. e. Click Import. f. Select the Delimited button in the first Wizard window and click Next. g. Deselect the Tab box and select the Comma box. h. Click Next. i. Click the date column in the Data preview area in the third Wizard window. j. Click the Date radio button in the Column data format group. Click Finish. k. Deselect the Add this data to the Data Model box in the Import Data dialog box, and click

OK. l. Select columns A:D and size each column to 12.11 (90 pixels) wide. m. Deselect the columns.

10. Use the Subtotal command. a. Close the Queries & Connections pane and click cell A5. b. Click the Sort A to Z button [Data tab, Sort & Filter group] to sort by last name. c. Click the Subtotal button [Data tab, Outline group]. d. Verify that Last Name is selected for At each change in. e. Click the Use function arrow and choose Average. f. Check the Amount box in the Add subtotal to area. g. Click OK. h. Format the values in column D as Currency with zero decimal places.

Download Resources

Excel 2016 Chapter 4 Formatting, Organizing, and Getting Data Last Updated: 9/18/18 Page 4

USING MICROSOFT EXCEL 2016 Guided Project 4-3

11. Collapse outline groups. a. Click the collapse symbol (-) for Allen in

row 8. b. Click the collapse symbol (-) for

McAllister (Figure 4-93). 12. Create a PivotTable.

a. Click the Past&Projected sheet tab. b. Select cells A4:E60. c. Click the Quick Analysis tool and

choose Tables. d. Point to each PivotTable option to see

the Live Preview. e. Choose the option that shows a sum of

the employees, the total hours, and the patients (Figure 4-94).

f. Rename the sheet PivotTable. g. Drag the Month field in the Rows area in

the PivotTable Fields pane out of the pane and into the worksheet.

13. Format a PivotTable. a. Click cell C4. b. Click the Field Settings button

[PivotTable Tools Analyze tab, Active Field group].

c. Click Number Format. d. Choose Number as

the Category. e. Select the Use 1000

Separator (,) box and set zero decimal places.

f. Click OK to close each dialog box.

g. Apply the same number format for the “Sum of Patients” field.

h. Click the More button [PivotTable Tools Design tab, PivotTable Styles group].

i. Select Dark Gray, Pivot Style Dark 9.

Select Pivot Style Dark 9.

Excel 2016 Chapter 4 Formatting, Organizing, and Getting Data Last Updated: 9/18/18 Page 5

USING MICROSOFT EXCEL 2016 Guided Project 4-3

j. Select the Banded Rows box [PivotTable Tools Design tab, PivotTable Styles Options group]. k. Select the Banded Columns box (Figure 4-95).

14. Create and format a PivotChart. a. Click the PivotChart

button [PivotTable Tools Analyze tab, Tools group].

b. Select Bar as the chart type and Stacked Bar as the subtype.

c. Click OK. d. Position the chart object

so that its top-left corner is at cell A12.

e. Drag the bottom-right selection handle to reach cell J30. f. Click the More button [PivotChart Tools Design tab, Chart Styles group] and choose Style 8. g. Click the Change Colors button [PivotChart Tools Design tab, Chart Styles group]. h. Select the Monochromatic Palette 1 Blue-Gray gradient, dark to light in the Monochromatic

group.

Select the Monochromatic Palette 1 in the Monochromatic group. Excel 2013 users, select Color 5 in the Monochromatic group.

i. Deselect the # of Employees box in the PivotChart Fields pane (Figure 4-96).

4-96 PivotChart object j. Click cell A1.

Excel 2016 Chapter 4 Formatting, Organizing, and Getting Data Last Updated: 9/18/18 Page 6

USING MICROSOFT EXCEL 2016 Guided Project 4-3

15. Export data using the Clipboard. a. Click the Adv Filter sheet tab. b. Select cells G4:K14 and click the Copy button [Home tab, Clipboard group]. c. Open the ClemensonImaging-04 Word document downloaded from the Resources link. d. Press Ctrl+End to position the insertion point. e. Click the arrow with the Paste button [Home tab, Clipboard group] and choose Paste Special. f. Select Microsoft Excel Worksheet Object in the Paste Special dialog box. g. Select the Paste link radio button in the Paste Special dialog box and click OK.

IMPORTANT: If you receive an error message similar to “Excel cannot obtain the data for the {00020830- 0000-C000-000000000046} link.”, link.”, make sure that you have extracted the ClemensonImaging-04 Word document from the resources zip folder and open the workbook from the extracted folder, save your Excel workbook, and then try again.

h. Click the Center button [Home tab, Paragraph group]. i. Save the Word document as [your initials] Excel 4-3 in your usual folder (Figure 4-97). You will not

be uploading this file to SIMnet. j. Close Word.

16. Save and close the ClemensonImaging-04 Excel workbook.

17. Upload and save the ClemensonImaging-04 Excel workbook.

18. Submit project for grading.

Download Resources

Step 2: Upload & Save

Step 3: Grade my Project