Assessment

$4$

Summarize Data in a PivotTable and a PivotChart

Open BillingSummary $4$Q$.$xlsx and save it with the name $4-$BillingSummary$4$Q$.$

Create a PivotTable in a new worksheet as follows:

a$.$ Display the range named Fourth $Q$ and then insert a PivotTable in a new worksheet.

b$.$ Add the Attorney LName field as rows.

c$.$ Add the Area field as columns.

d$.$ Sum the Fees Due field.

Apply the Light Blue, Pivot Style Medium $9$ style $($second column, second row in the Medium section$)$ to the PivotTable.

Apply the Comma format with no digits after the decimal point to the values. Change the width of columns B through H to $13$ characters. Remove the Autofit column widths on update check mark in the PivotTable Options dialog box. Rightalign the Martinez, O'Donovan, Sullivan, Williams, and Grand Total labels in column A$.$

Name the worksheet Pivot Table and then do the following:

a$.$ In cell AI$,$ type Associate Billing Summary. Change the font to $14-$point Arial and apply bold formatting. Merge and center the text across the Pivot Table.

b$.$ In cell A$2,$ type October $-$ December $2024.$ Change the font to $14-$point Arial and apply bold formatting. Merge and center the text across the PivotTable.

c$.$ Change the page layout to Landscape orientation and then preview the Pivot Table.

Drill down cell H$5.$ Autofit column C$.$ Name the worksheet Martinez.

Create a PivotChart from the PivotTable using the $3$D Stacked Column chart type and move the chart to its own sheet named PivotChart.

Apply Style $7($seventh style in the Chart Styles gallery$)$ to the PivotChart.

Filter the PivotChart by the attorney name Martinez.

Preview the PivotChart.

Save and then close $4-$BillingSummary $4Q.$xlsx$.$

$146$

Excel Level $2|$ Unit $1$

Chapter $4$

Surmarizing and Consolidating Data