Excel guided project 4 3

Excel 2013 Chapter 4 Importing, Creating Tables, Sorting and Filtering, and Using Conditional Formatting Last Updated: 2/4/15 Page 1

USING MICROSOFT EXCEL 2013 Guided Project 4-3

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

Skills Covered in This Project

 Create an Excel table.

 Apply a table style.

 Create an Advanced Filter.

 Apply Conditional Formatting.

 Copy, move, and name a worksheet.

 Insert a row and set column widths.

 Import a comma-separated values text file.

 Sort data.

 Use the Subtotal command.

 Expand and collapse groups in an outline.

 Use Goal Seek.

 Create a PivotTable.

 Create a PivotChart.

 Unlock cells.

 Protect a worksheet with a password.

1. Open the ClemensonForecasts-04 start file. Click the Enable Editing button.

2. 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.

3. Copy the Past&Projected sheet and name the copied sheet Adv Filter. Move the Adv Filter sheet to the right

of the Past&Projected sheet.

4. 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.

5. Apply a table style.

a. Click a cell within the table.

b. Click the More button [Table Tools Design tab, Table Styles group].

c. Select Table Style Medium 15.

6. Create a criteria range for an Advanced Filter. a. Select cells A4:E4 and copy and paste them to cell G4.

b. Type Criteria Range in cell G3 and set the font to Cambria 16 pt.

c. Adjust column K width to show the complete label.

d. Type Extract Range in cell G8 and set the same font and size.

e. Copy the labels from cells G4:K4 to cell G9.

7. Create an Advanced Filter.

a. In cell G5, type >12/31/16.

b. In cell H5, type mri.

Step 1

Download start file

Excel 2013 Chapter 4 Importing, Creating Tables, Sorting and Filtering, and Using Conditional Formatting Last Updated: 2/4/15 Page 2

USING MICROSOFT EXCEL 2013 Guided Project 4-3

c. In cell H6, type ct scan. These criteria will find records dated 2017 or later for MRIs and records for CT scans

from any year (Figure 4-93).

d. Click a cell in the table.

e. Click the Advanced button [Data tab, Sort & Filter group].

f. Select the Copy to another location radio button.

g. Verify that the List range is cells A4:E60. If the range

is incorrect, click and drag to select the range. The

list range includes the header row.

h. Click in the Criteria range box and select cells

G4:K6.

i. Click in the Copy to box and select cells G9:K9.

j. Click OK (Figure 4-94). The CT scan records are

from all years; the MRI records are only those from

2017 and later.

8. Apply Conditional Formatting.

a. Select cells I10:I25.

b. Click the Conditional Formatting button [Home

tab, Styles group].

c. Choose Highlight Cells Rules and Greater Than.

d. Type 751 and choose Green Fill with Dark Green

Text.

e. Click OK.

9. Import a comma-separated values text file.

a. Click the Expense Info sheet tab.

b. Select cell A3.

c. Click the From Text button [Data tab, Get External Data group].

d. Find and select the ClemensonExpenseData-04.csv file in the Import Text File window.

e. Click Import.

f. Select the Delimited button in the first Wizard window. Excel recognizes that this file separates the data

into columns with a comma.

g. Click Next.

h. Click to remove the check mark for Tab and click to place a check mark for Comma.

i. Click Finish and click OK.

j. Insert two rows at row 3.

k. Type Representative in cell A4, Date in cell B4, and Amount in cell C4.

l. AutoFit column C.

4-93 Advanced Filter results

4-94 Advanced Filter results

Excel 2013 Chapter 4 Importing, Creating Tables, Sorting and Filtering, and Using Conditional Formatting Last Updated: 2/4/15 Page 3

USING MICROSOFT EXCEL 2013 Guided Project 4-3

10. Sort data.

a. Click cell A5.

b. Click the Sort & Filter button [Home tab, Editing group].

c. Choose Sort A to Z.

11. Use the Subtotal command.

a. Click cell A5.

b. Click the Subtotal button [Data tab, Outline group].

c. Click the At each change in arrow and choose Representative.

d. Click the Use function arrow and choose Average.

e. Check the Amount box in the Add

subtotal to area.

f. Click OK.

g. Format the values in column C as

Currency with no decimal places.

12. Use Goal Seek.

a. Type Target March Expense in cell E4.

b. Click cell C8.

c. Click the What-If Analysis button [Data

tab, Data Tools group] and choose

Goal Seek.

d. In the To value box, type 600. This value

sets a lower expense for Mary Jo.

e. In the By changing cell box, click cell

C7.

f. Click OK.

g. Note the target expense and click Cancel.

h. Type $600 in cell F5.

13. Collapse outline groups.

a. Click the collapse symbol (-) for row 12.

b. Click the collapse symbol (-) for each of these rows: 16, 20, 24 (Figure 4-95).

14. Create a PivotTable.

a. Click the Past&Projected sheet tab.

b. Select cells A4:E60. You must select the

range to display the Quick Analysis tool.

c. Click the Quick Analysis tool and

choose Tables.

d. Point at several PivotTable options to see

the Live Preview.

e. Choose the option that shows a sum of

each field (Figure 4-96).

f. Rename the sheet PivotTable.

4-96 Suggest PivotTable choices from the Quick Analysis tool

4-95 Expanded and collapsed groups after the Subtotal Command

Excel 2013 Chapter 4 Importing, Creating Tables, Sorting and Filtering, and Using Conditional Formatting Last Updated: 2/4/15 Page 4

USING MICROSOFT EXCEL 2013 Guided Project 4-3

15. Add a PivotChart to the PivotTable worksheet.

a. Click a cell within the PivotTable.

b. Click the PivotChart button [PivotTable Tool Analyze tab, Tools group].

c. Click Line in the type list, and choose Line with Markers PivotChart.

d. Click OK.

e. Position the chart to the right of the PivotTable.

f. In the PivotChart Fields pane, click to remove the check mark for # of Employees (Figure 4-97). The

PivotTable and PivotChart are linked so that field is removed from the table and the chart. The task pane

toggles between the PivotTable and PivotChart fields panes based on which element is active.

16. Remove the Locked property.

a. Click the Past&Projected sheet tab.

b. Select cells C5:E60 and right-click any cell in the range.

c. Select Format Cells from the menu.

d. Click the Protection tab.

e. Clear the check mark for Locked.

f. Click OK.

17. Protect a worksheet with a password.

a. Click the Protect Sheet button [Review tab, Changes group].

b. Click to place check marks for Select locked cells and Select unlocked cells if necessary.

c. Type the password: 321.

d. Click OK.

e. Retype the password: 321.

f. Click OK. The cells for which the Locked property was disabled can be edited.

18. Save and close the workbook.

19. Upload and save your project file.

20. Submit project for grading.

4-97 PivotChart and its PivotTable

Step 2

Upload & Save

Step 3

Grade my Project