Can I share the excel sheet somehow? Guided Project 10-3

These instructions are only compatible with the Microsoft Windows operating system.

Steps to complete This Project

Mark the steps as checked when you complete them.

Courtyard Medical Plaza has two datasets with dental insurance information. You get these tables in Power Query to append one to the other. You replace values then create a PivotTable with a PivotChart.

[Student Learning Outcomes 10.1, 10.2, 10.3, 10.4, 10.6]

File Needed: EX2021-GuidedProject-10-3.xlsx (Available from the Start File link.) and CourtyardMedical-10.xlsx (Available from the Resources link.)

Completed Project File Name: [your name]-CourtyardMedical-10.xlsx

Skills Covered in This Project

Get data from a workbook in Power Query.

Append queries.

Replace values in a query.

Create and customize a PivotTable.

Insert a PivotChart.

Insert a slicer in a PivotTable.

1. Open the EX2021-GuidedProject-10-3 start file. Click the Enable Editing button. The file will be renamed automatically to include your name.

2. Get Excel data in Power Query.

a. Click the Get Data button [Data tab, Get & Transform Data group] and select Launch Power Query Editor.

B. Expand the Navigator Pane if it is collapsed.

C. Click the New Source button [Home tab, New Query group].

D. Select File and then choose Excel or Excel Workbook.

Navigate to the folder with the CourtyardMedical-10 workbook.

Select the file name and click Import. The Navigator window displays sheet and range names in the workbook.

Select 2021. The tabbed icon to the left of the name indicates that this is a sheet in the workbook, and the first three rows display main label information.

Select Data2021. The table icon displays to the left of the name, and the data previews for importing.

Click OK. The data displays in a Power Query window.

Click the New Source button [Home tab, New Query group] and get the Data2022 table from the CourtyardMedical-10 workbook.

3. Append queries.

Select Data2021 in the Navigator Pane. The IDs range from 0001 to 0031.

Click the arrow for the Append Queries button [Home tab, Combine group] and select Append Queries as New.

Confirm that the Two tables button is selected and that Data2021 displays as the First table.

Click the drop-down list for Second table and choose Data2022 (Figure 10-86).

Click OK. The new query is named Append1.

Figure 10-86 Append dialog box

Replace values in Power Query.

Select the ID column. Note that the IDs now extend to 0060.

Click the Replace Values button [Home tab, Transform group]. You will replace OP with DS for Dental Services.

Type OP in the Value to Find box.

Type DS in the Replace With box and click OK.

Load data to Excel.

Click the Close & Load button [Home tab, Close group]. The two source queries and the appended query are loaded to separate worksheets.

If your worksheets are not automatically named, rename them as Append1, Data2022, and Data2021.

Select cell A2 on the Append1 sheet.

Create a PivotTable.

Select the Summarize with PivotTable button [Table Design tab, Tools group].

Confirm the table/range Append1, that New Worksheet is selected, and click OK.

Close the Queries & Connections pane if it displays.

Name the worksheet tab PivotTable.

Expand the PivotTable Fields pane. There is no All button because the PivotTable was not added to the data model.

Place fields in the PivotTable.

Select the Service Code field name in the PivotTable Fields pane. Label fields display in the Rows area.

Select the Billed field name. Numeric fields display in the Values area.

Point to Billed in the Choose fields to add to report area and drag the field name to the Values area to show the field twice in the PivotTable (Figure 10-87).

Figure 10-87 Fields placed

Edit value field settings.

Select cell B3 and click the Field Settings button [PivotTable Analyze tab, Active Field group].

Type Total Billed as the Custom Name.

Click Number Format and choose Currency with 0 (zero) decimal places.

Right-click cell C3, select Summarize Values By, and choose Average.

Click the Field Settings button [PivotTable Analyze tab, Active Field group] and type Average Billed as the Custom Name.

Format the field as Currency with 0 (zero) decimal places.

Refresh data and format the PivotTable report.

Select cell A7 in the PivotTable. This code is an outlier; it should have five characters.

Click the Append1 worksheet tab and select cell C61.

Type D0120 and press Enter.

Return to the PivotTable sheet. The data is not automatically updated.

Click the Refresh button [PivotTable Analyze tab, Data group]. The change displays and the rows re-sort.

Click any cell in the PivotTable and apply the Light Blue, Pivot Style Medium 6 style.

Select the Banded Rows and Banded Columns boxes [PivotTable Tools Design tab, PivotTable Style Options group].

Select cells A3:C3 and apply bold.

Select cell A3 and type Service Code.

AutoFit all columns.

Select cell A1 and type Courtyard Medical Dental Services.

Type Billings by Service Code in cell A2.

Format both labels as 14 pt.

Insert a row at row 3 (Figure 10-88).

Figure 10-88 Completed PivotTable

Create a PivotChart.

Select a cell in the PivotTable and click the PivotChart button [PivotTable Analyze tab, Tools group].

Choose Combo and Clustered Column - Line as the subtype and click OK (Figure 10-89).

Position and size the chart object to span cells E3:N25.

Apply a Black, Text 1 outline that is pt. thick.

Turn off display of the Field Buttons [PivotChart Analyze tab].

Select the Average Billed line in the chart.

Apply a Black, Text 1 outline that is 3 pt. wide (thick).

Figure 10-89 Insert Chart dialog box

Insert a slicer.

Click a cell in the PivotTable and click the Insert Slicer button [PivotTable Analyze tab, Filter group].

Select the Insurance box and click OK.

Position and size the slicer to span cells B12:C20.

Format the slicer with Light Blue, Slicer Style Dark 5.

Click CompDent in the slicer to filter the PivotTable and PivotChart (Figure 10-90).

Select cell A1.

Figure 10-90 Excel 10-3 completed

Save and close the workbook.

Upload and save your project file.

Submit project for grading.