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 .