Back to lesson
1. Imagine that you have a data set of customers for a business and you want to identify the top 30% of spenders (the 'star' customers). Which is the best method to indicate this visually?
Highlight the total spend column for the customers and apply data bars to it.
Highlight the total spend column for the customers and apply a star icon set to it.
Write a formula to identify if each customer is in the top 30% of spenders.
2. Suppose cells D10:D50 contain a range of numbers. How do you quickly color all values over $5,000?
Set up a Greater Than 5000 conditional formatting rule for D10:D50.
Write the formula =IF(D10>5000,”Over”,””) in E10 and copy it down the table.
Sort the data in D10:D50 and then color it.
3. Suppose you want to identify the top 25% and bottom 25% of values in a column of data. Which method is the best?
Sort the data in ascending order and use a formula to determine which cells fit into each quartile.
Set up a Highlight Cells Between conditional formatting rule.
Set up a Top 10% and Bottom 10% conditional formatting rule, changing the % to 25% for each one.
Back to the top
© 2017 GoSkills Ltd.
Skills for career advancement