In last week’s tip we explained how, by using the LARGE function, you can extract the greatest value from a given range of values. For example, a Debtors Clerk can extract the top five largest outstanding balances from a range of outstanding balances to a separate worksheet or a new set of rows. This week, we share an alternative to that; highlighting your top five largest debtors, their account number and outstanding balance from a data set. To do this, we use the LARGE function which will return the top five largest values in the data set and Conditional Formatting which will highlight the data based on set criteria.
Note: Download the sample workbook to practice this exercise
Applies To: Microsoft Excel 2003, 2007, 2010 and 2013
To illustrate this example we use the screen shot below for our example.
1. Select cells A3 to C17.
2. Under the Home Tab, select Conditional Formatting, then select New Rule as in the screen shot below.
3. A pop up will appear resembling the screen shot below
4. Select ‘Use a formula to determine which cells to format’ as above.
5. Type in the following formula
=$C3>=LARGE($C$3:$C$17,5).
This can be narrated as follows:
- $C3 is a mixed cell reference(only the column is fixed)
- Various rows of data can be selected because the row number is not fixed(absolute cell referencing)
- $C$3:$C$17 refers to the data range for the balances
- 5 implies that the top five balances will be highlighted
- Select the Format button and choose the Format color
6. Select OK.
7. The rows with the top five values will be highlighted as below.
