1. Highlight top values
Conditional formatting can also be a great tool to show the top or bottom values in a spreadsheet. Maybe you want to highlight the bottom five values of item weights in a quality control log or maybe you want to highlight the top 10 production amounts for all of the shifts during a month to award an incentive bonus.
2. LARGE function
Let's start with an explanation of a new function that we will be using in the lesson to help us out. The function's name is LARGE and it returns the n largest values from a given range.
Later, we will be using the SMALL function, which returns the n smallest values from a given range. As you could guess, we'll be using this to highlight the smallest values.
3. Sample data
The LARGE function, when used by itself, returns just one value. Let's apply it to this table of data to see what it does.
4. LARGE example
The LARGE function has looked through all of the production quantities and returned the third largest one, which is 1,473.
You may be wondering how this could be used to highlight multiple amounts. Well, let's see!
5. Applying LARGE
This spreadsheet contains the same data that we were looking at in the previous slide. The custom formula box is filled in with an adaption of the LARGE function. We're going to take our time on this one and break the formula down piece by piece.
6. LARGE explained
This is the formula that was typed into the custom formula box on the previous slide.
The first part of the formula is the value that tells the formatting to look at just that one cell. In the next row, the cell will increment down to C3.
The next important part of the formula is the greater than and equal to signs. The LARGE function is returning the third largest value so we need to use the greater than and equals signs so that it picks all amounts greater than or equal to the number you choose. If you use three, it will return three.
The last important aspect is that we are using dollar signs to fix the range that the LARGE function is evaluating. We previously had a similar issue with the COUNTIF function, which we solved by referencing the entire column using a C colon C syntax. You can reference your range either way, depending on your situation.
7. SMALL
It wouldn't be fair if we didn't show SMALL as well, which is very similar. In this example, the second smallest value is 1,238.
8. Let's try it out!
You finished the primer on highlighting the smallest or largest values in a data set. Hold on to your chair, it's going to be a wild ride. Let's try it out!