Ch 4 Guided Project 4-3

Clemenson Imaging tracks the number of patients and equipment usage. To complete the worksheets, you format data as a table and build an advanced filter. You import a comma-separated values file (.csv) and use the Subtotal command. Finally, you display data in a PivotTable and PivotChart.

[Student Learning Outcomes 4.1, 4.2, 4.3, 4.4, 4.5, 4.6, 4.8]

File Needed: Clemenson-04.xlsx (Available from the Start File link.) and ClemensonCSV-04.csv (Available from the Resources link.)

Completed Project File Name: [your name]-Clemenson-04.xlsx

Skills Covered in This Project

• Format data as an Excel table.
• Resize and name an Excel table.
• Build an Advanced Filter.
• Sort data by multiple columns.
• Apply conditional formatting to filtered results.
• Import a comma-separated values file.
• Convert a table to a range.
• Use the Subtotal command.
• Create a PivotTable.
• Change the data source for a PivotTable.
• Create a PivotChart.

IMPORTANT: If you are a Canadian user, be sure to verify that your browser and Microsoft Office use the same country settings. See here for a Help topic on how to change your settings.

Steps to complete This Project

Mark the steps as checked when you complete them.

1. Open the Clemenson-04 start file. If the workbook opens in Protected View, click the Enable Editing button so you can modify it. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.
2. Copy the Patients&Equip sheet, place the copy before the Criteria sheet, and name the copied sheet Adv Filter.
3. Create and resize an Excel table.
   1. Select cell A5 on the Adv Filter sheet and click the Format as Table button [Home tab, Styles group].
   2. Choose White, Table Style Light 1.
   3. Confirm that the range is $A$4:$D$60 and that the My table has headers box is selected.
   4. Create the table and select cell D59. Rows 61-73 were not included in the table format. The source data is from multiple files and not all rows were recognized.
   5. Point to the Table Resize handle at the bottom right corner of cell D60 to display a resize pointer (Figure 4-10Figure 4-106 Resize the table to reach row 73
   6. Drag the pointer to reach cell D73.
   7. Select cell A5 and click the Table Name box [Table Design tab, Properties group].
   8. Name the table tblFilter
4. Create an output range for an Advanced Filter.
   1. Select cells A4:D4 and copy and paste them to cell G4.
   2. Type Extract Range in cell G3 and set the font to 14 pt. italic.
   3. Adjust column widths for columns G:J so that each label displays on a single line.
5. Create an Advanced Filter.
   1. Click the Criteria sheet tab. Notice that the Month/Year label displays twice in row 2.
   2. Type >12/31/19 in cell A3 to find records after 2019.
   3. Type <1/1/21 in cell B3 to create an AND filter. This criteria will extract records from 2020. It finds records in which the date is greater than (after) 12/31/19 and before 1/1/21 (Figure 4-107).Figure 4-107 Criteria for Advanced Filter
   4. Click cell A5 on the Adv Filter sheet and click the Advanced button [Data tab, Sort & Filter group].
   5. Select the Copy to another location radio button.
   6. Verify that the List range is cells $A$4:$D$73. If the range is incorrect, click and drag to select the range including the header row.
   7. Click the Criteria range box and select cells A2:B3 on the Criteria sheet.
   8. Click the Copy to box and select cells G4:J4 on the Adv Filter sheet.
   9. Click OK in the Advanced Filter dialog box.
6. Sort data in the output range.
   1. Select cell G5 and click the Sort button [Data tab, Sort & Filter group].
   2. Choose Image Type for the first level.
   3. Sort by Cell Values in A to Z order.
   4. Add Month/Year as the next level.
   5. Sort by Cell Values and Oldest to Newest.
   6. Click OK.
   7. AutoFit column H.
7. Apply conditional formatting and borders.
   1. Select cells I5:I16 and click the Conditional Formatting button [Home tab, Styles group].
   2. Choose Highlight Cells Rules and Greater Than.
   3. Format cells that are greater than 100 with Green Fill with Dark Green Text.
   4. Select the extract range starting at cell G4 and apply All Borders (Figure 4-108).Figure 4-108 Advanced Filter results
   5. Press Ctrl+Home.
8. Import a comma-separated values file.
   1. Click the Expense Info sheet tab.
   2. Select cell A4.
   3. Click the From Text/CSV button [Data tab, Get &Transform Data group].
   4. Find and select the ClemensonCSV-04.csv file downloaded from the Resources link in the Import Data window.
   5. Click Import.
   6. Verify that Comma is selected as the Delimiter in the preview window.
   7. Click the Load arrow and select Load To.
   8. Select Existing worksheet.
   9. Verify that cell $A$4 displays as the destination and that Table is how the data will be imported.
   10. Click OK to import the text data.
9. Convert an Excel table to a range.
   1. Cut and paste (or drag and drop) the labels in row 3 to replace the labels in row 4.
   2. Select cell A5 and click the Convert to Range button [Table Design tab, Tools group].
   3. Click OK to remove the query definition and create a normal data range.
   4. Select cells A4:D34 and clear the formats [Home tab, Editing group].
   5. Select cells A4:D34, if necessary, and change the font size to 11 pt.
   6. Center align the labels in row 4 and apply bold format.
   7. Format the dates in column C with the Short Date format.
   8. Set the column width for columns A:D to 13. Deselect the columns.
   9. Close the Queries & Connections pane.
10. Calculate average expenses by last name.
    1. Click cell A5 and sort by last name in ascending order.
    2. Click the Subtotal button [Data tab, Outline group].
    3. Complete the dialog box to show the average amount for each Last Name.
    4. Format the values in column D as Currency with zero decimal places.
    5. Collapse the outline to display only the averages for each last name (Figure 4-109).Figure 4-109 Subtotals displayed for group
11. Create a PivotTable.
    1. Click the Patients&Equip sheet tab.
    2. Select cell A5 and click the Recommended PivotTables button [Insert tab, Tables group].
    3. Choose the option for a sum of patients by image type. IMPORTANT NOTE: To ensure accurate grading of the following steps, make sure that your PivotTable begins in cell A3 on the new worksheet.
    4. Rename the sheet PivotTable.
12. Edit the data source for a PivotTable.
    1. Select cell A3 if necessary and click the Change Data Source button [PivotTable Analyze tab, Data group].
    2. Click the Table/Range box and edit the entry to show $D$73 instead of $D$60. Because the source data was assembled from multiple sources, Excel has missed several rows (Figure 4-110).Figure 4-110 Edit the data source to include all rows
    3. Click OK.
13. Format a PivotTable.
    1. Click cell B4 and open the Value Field Settings dialog box.
    2. Click Number Format.
    3. Choose Number as the Category, set zero decimal places, and display a comma as the 1000 Separator.
    4. Click the More button [Design tab, PivotTable Styles group].
    5. Select Dark Gray, Pivot Style Dark 9.
    6. Display Banded Rows.
    7. Display Banded Columns (Figure 4-111).Figure 4-111 PivotTable with format changes
       and updated data source
14. Create and style a PivotChart.
    1. Click the PivotChart button [PivotTable Analyze tab, Tools group].
    2. Select Bar as the chart type and Clustered Bar as the subtype.
    3. Click OK.
    4. Position the chart object so that its top-left corner is at cell A12.
    5. Drag the bottom-right selection handle to reach cell J35.
    6. Click the More button [PivotChart Design tab, Chart Styles group] and choose Style 7.
15. Sort data in a PivotTable.
    1. Click cell B4 in the PivotTable. Notice that the data is sorted by the labels in column A and that the bars show the bottom label as the top bar.
    2. Sort the data in descending order (largest to smallest). The bars now display the largest bar at the bottom of the chart.
    3. Click cell A1.
16. Save and close the Excel workbook (Figure 4-112).Figure 4-112 Excel 4-3 completed
17. Upload and save your project file.
18. Submit file for grading.