You determine that one measure of how well the company " gets your price " is to set a scale . Excellent performance is at or above the asking price . Good performance is between 95 % and 100 %. Below 95 % is average performance . You will create conditional formatting rules to display graphically the performance level for each sale . Make a copy of the original sales data chart . Name the new sheet Conditional Format .
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 .