Enhance your Excel skills by mastering conditional formatting techniques like color scales and icon sets to visually distinguish data. Learn to utilize percentile calculations effectively for clearer, data-driven decision-making.
Key Insights
- Apply color scales in Excel by selecting data and using conditional formatting from the Home tab, providing visual gradients that represent cell values clearly.
- Use icon sets to visually categorize numerical data, and customize their distribution by changing the conditional formatting rules from percentage to percentile for equal grouping.
- Calculate exact percentile thresholds using Excel's percentile function to determine precise breakpoints for categories, such as identifying salary thresholds at $105,000 (67th percentile) and $84,000 (33rd percentile).
Note: These materials offer prospective students a preview of how our classes are structured. Students enrolled in this course will receive access to the full set of materials, including video lectures, project-based assignments, and instructor feedback.
In this short section, we're going to take a look at how to work with conditional formatting using color scales. Now we have been working with color scales all along, but we're going to take a look at applying a color gradient to format cells based on their value using color scales again. So these are pretty simple exercises.
If I click the plus sign next to row 11, there's additional information here about how to apply the green, yellow, red color scale and what it actually does. So let's just sort this information in any kind of order. I'm going to sort it alphabetically.
I'll go to the data tab and I'll choose A to C. Now I'm going to select all the values and I'm going to apply color scales. I'm going to go to the home tab, choose conditional formatting, and I'll choose the first type of color scale. This is how the information is going to look.
If I want this to be sorted in descending order, then I'll head over and click on any value in the sales column, go to the data tab, and sort this in descending order by clicking C to A. And maybe that is the look that I'm going for. It's almost as if there's a sunset right below some green pastures or something. So that is the first exercise.
Let's take a look at this exercise here. We want to apply the color scale for the salaries below. Now I'm going to sort the data in ascending order initially.
Then I'm going to select all the values. I can press CTRL shift down arrow, and I'm going to go to the home tab, go to conditional formatting, and choose the first color scale. And there we go.
Not bad at all. Now this is sorted in ascending order and this is how it looks. Maybe I change my mind and I want to sort this based on descending order.
So I'm going to do that now. I'm going to head over to the data tab and click the C to A. Now it's the opposite order. I want to add something additional to this.
It's not part of the exercise, but it would be nice to see some icon sets here. So I'm going to select all the values. I'm going to head over to the home tab.
I'll go to conditional formatting, and from conditional formatting I'll choose icon sets. And I like this one with the check mark and the exclamation mark and the X. Now this is how the information looks. One of the things you'll notice is that there's not an equal distribution of green check marks, yellow exclamation marks, and the X's.
And this is a setting that can be changed from conditional formatting. So I'm going to go back to conditional formatting and I'll choose manage rules. Any of the rules that I've applied to whatever I've currently selected are listed right here.
So I'm not interested in changing the color. I want to change how the icons are distributed. So I'll click edit rule.
And this is the problem. This information is sorted based on percent, not percentile. If I choose percentile, then that will have a more equal distribution.
So I'm going to leave everything exactly the way it is, but I'm just going to choose percentile for the type. I'll click okay. I can apply it to see if I like it before I click the final okay.
So I'll click apply. Looks good. And I'll click okay.
And now the information is evenly distributed between green, yellow, and red. Something I can do additionally, this is the last thing I'll do. What determines when the information is broken up into the different sections? Well, it's based on percentiles that are 67% and like 33%.
So I can actually calculate the value of the 67 percentile, the part where the threshold switches from the yellow exclamation mark to the green check mark. So let's say I want to calculate the value for the 67% percentile. I'll type equal.
I'll type percentile. And I'll choose include. And what I want to do is choose the array.
I'll go here and select the entire array. And then I want to type the percentage. I'll choose 67.
So that's a.67. I'll put in a closed parenthesis. And I'll press ENTER. That is $105,130.
I'll go over here. Change this to currency. Decrease the decimals.
And that is actually the threshold from when we go from a yellow exclamation mark to a green check mark. Now I'm going to go down here and calculate for the 33% or 34%. So I'm just moving my selection box near the area where I'll find the change in the values.
I'll type equal percentile. I'll choose include. I'll select everything.
I'll enter a comma. And let's see. Let's do 33.
So.33. Closed parenthesis. When I press ENTER, it's going to show me the 33 percentile for that range. $84,000.
And that is exactly where the shift happens. That's where the threshold goes from red X's to yellow exclamation marks. I'm going to go over here.
I'll click on the format painter and apply the same format for the numbers. So I'll just put in a bottom border here and a bottom border there. So this is the use of the percentile function to show the threshold for the three breakdowns of the three sections percentiles for this data.
So that is working with color scales and icon sets. Thank you for watching.