Use GETPIVOTDATA to extract data from a PivotTable. In cell D$1$ on the Analysis worksheet, display the total annual sales for Ambulatory Care of TX$,$ Inc.

Select cell D$1.$

Type $=$

Click the PivotTable worksheet tab.

Click cell B$4.$

Press Enter.

Use consolidate to create a summary of the sales data by region. The summary will be located on the Analysis worksheet.

Remain on the Analysis worksheet and select cell A$4.$

On the Data tab, in the Data Tools group, click the Consolidate button.

Verify that Sum is selected in the Function box.

If there are any references in the All references box, click each and then click the $-$ button to remove them.

Click in the Reference box, and then click the Sales Data worksheet tab. Click and drag to select cells C$3$:E$67.$

Click both the Top row and Left column check boxes.

Click OK$.$

On the Analysis worksheet, delete cells B$4$:B$12,$ allowing the other cells to shift left.

Sort the sales data alphabetically by region and then by last name.

Go to the Sales Data worksheet, and click any cell in the data set.

On the Data tab, in the Sort & Filter group, click the Sort button.

In the Sort dialog, expand the Sort by$,$ Column list, and select Region.

Click the $+$ button to add a level.

Expand the Then by$,$ Column list, and select Last Name.

Click OK$.$

Add subtotals to the data to calculate the total commission earned for each sales associate.

On the Data tab, in the Outline group, click the Subtotal button.

Expand the At each change in list, and select Last Name.

Verify that Sum is selected in the Use function box.

Verify that there is a checkmark next to Commission Earned in the Add subtotal to box.

Click OK$.$

Copy the subtotal data to the Analysis worksheet.

On the Sales Data worksheet, click the outline level $2$ button to collapse the list so only the total commission earned for each sales associate is visible. This will make it easier to copy the data.

Select cells A$3$:G$77$ and copy them to the Clipboard. Do not include the Grand Total row $($row $78).$

Go to the Analysis worksheet and paste the copied data beginning in cell A$15.$

Notice that when you paste the subtotaled data, Excel removes the subtotal grouping, but keeps the subtotal rows and formulas. You can use the Outline command to re$-$create groups based on the subtotal formulas.

Click any cell in the data range.

On the Data tab, in the Outline group, click the Group button arrow, and select Auto Outline.

Click the outline level $1$ button to collapse the list so only the total commission earned for each sales associate is visible.

On the Commissions worksheet, create a new conditional formatting rule to apply an icon set to the values in the Rating column.

Go to the Commissions worksheet and select cells H$3$:H$66.$

On the Home tab, in the Styles group, click the Conditional Formatting button. Click New Rule...

The New Formatting Rule dialog opens.

In the Style box, expand the list, and select Icon Sets.

Expand the Icons list, and select Three Stars.

Click the Show Icon Only checkbox to hide the values in the selected cells.

In the Display section, expand the first Type list and select Number.

In the first Value box type $9500$ to display a completely filled gold star when the cell value is greater than or equal to $9,500.$

Expand the second Type list and select Number.

In the second Value box, type $5000$ to display a partially filled gold star when the cell value is less than $9,500$ and greater than or equal to $5,000.$

Click OK$.$

Sort the sales data so cells with the completely filled gold star icon appear first.

Scroll down and find a cell with a completely filled gold star.

Right$-$click a cell with a completely filled gold star, point to Sort, and select Put Selected Formatting Icon On Top.

On the Sales Data Filter worksheet, delete the conditional formatting rule that applies a font format to cells where the value is above average.

Go to the Sales Data Filter worksheet.

On the Home tab, in the Styles group, click the Conditional Formatting button. Click Manage Rules... to open the Conditional Formatting Rules Manager.

If necessary, expand the Show formatting rules for list and select This Worksheet.

Select the Above Average rule and click the $-$ button to delete the rule.

Click OK$.$

Use the Advanced Filter feature to find sales greater than $$120,000$ for George Anderson or Mia Zhu.

Remain on the Sales Data Filter worksheet, and insert four new rows above row $3,$ so the column label row is now row $7$ and there are five blank rows between the title and the column labels. If necessary, clear all formatting from the new rows.

Copy the range A$7$:G$7($the column labels$)$ and paste in A$3$:G$3.$

Enter the filter criteria in A$4$:G$5$ as follows:

In A$4,$ type: George

In B$4,$ type: Anderson

In E$4,$ type: $>120000$

In A$5,$ type: Mia

In B$5,$ type: Zhu

In E$5,$ type: $>120000$

Click any cell in the data set.

On the Data tab, in the Sort & Filter group, click the Advanced button.

Verify that