Apply conditional formatting
To help find outliers and trends within a weekly sales and bonus payout worksheet, you need to apply some conditional formatting.
1. Click on the link below to open the Microsoft Excel exercise document you can use to complete this exercise.
https://github.com/alcadelina/Excel-
1. To begin, open Exercise: Apply Conditional Formatting from your Exercise Files folder:
2. You first want to use color scales to highlight the sales associates that made above average in sales and those that were below average. Use your cursor to select cells D5:D14:
3. Next, click Home → Conditional Formatting → Color Scales. From the Color Scales gallery, click the first option:
4. You will immediately see that those sales associates who have made more than average in sales will be highlighted in green, while those who made below are highlighted in red. Those that were close to the average are a yellow or orange shade:
5. Next, you need to create a custom conditional formatting rule that will use an icon set to identify sales associates who met or exceeded their weekly goal, as well as those who met or exceeded the minimum sales goal requirement. Ensure that the D5:D14 range is selected:
6. Next, click Home → Conditional Formatting → New Rule:
7. The New Formatting Rule dialog box will now be displayed. Click to select the “Format all cells based on their values” rule type:
8. Within the Edit the Rule Description section, click the Format Style drop-down menu and then click the Icon Sets option:
9. The controls in the Edit the Rule Description section will change to incorporate controls that are relevant to icon sets. On the row that has the green icon, click the Type drop-down menu and then click the Number option:
10. On the same row, inside the Value text box, type “15000” (the weekly goal):
(Note that you could also reference a cell that contains the value that you need.)
11. On the row that has the yellow dot icon, click the Type drop-down menu and then click the Number option:
12. On the same row, type “10000” (the minimum weekly target) into the Value text box:
13. With the new custom conditional formatting rule configured, click OK:
14. The new rule will now be applied to the selected range of data. (You may need to resize the column so that there is room for the icon set.) In this case you will see sales associates who made or exceeded their goal, those who are slipping below the base goal, and those who have not met either goal type:
15. Save your work as Activity 1-3 Complete and then close Microsoft Excel 2016.
16. Now, you can check out an example of a completed document in the link below:
No comments:
Post a Comment