A. Select the data range and insert a table.
B. Use Advanced Formatting to set up the following conditional formatting rules for the field Percent of Asking Price.
Greater than or equal to 100 % = GREEN 95 % to 99.9 % = YELLOW Less than 95 % = RED
C. Use Percent of Asking Price as the Sort Key, and sort by color with green first, then yellow, and then red
Step 6. Create a Pivot Table
Now that you have sorted, subtotaled, filtered, and conditionally formatted the data, you have a few more questions to answer. You want to see average percentage of selling price by agent and by city. This will allow you to see whether there are any patterns based on the city or based on the salesperson. To do this kind of comparison, you decide to create a pivot table.
A. Open the Sales Data worksheet. Select the Sales Data range( A3: H33). From the Formulas tab, select Define Name from the Defined Names group. Name the range of cells SalesData.
B. Create a pivot table based on the named range, SalesData. Create the pivot table in a new worksheet.
C. Select Selling Agent as your Row Labels and City as your Column Labels. Select Percent of Sales as your Values. Change the Summary type for Percent of Sales to average, the Number Format to percent, and the Name to Average Percent of Asking Price. Apply Data Bars Conditional Formatting to the percentages. Hide the Field Headers.