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.