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.
Write a formula to identify if each customer is in the top 30% of spenders.
Highlight the total spend column for the customers and apply a star icon set to it.
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.
Sort the data in D10:D50 and then color it.
Write the formula =IF(D10>5000,”Over”,””) in E10 and copy it down the table.
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