FIND VALUES IN A DATA SET

Find and Highlight the Largest Values in a Data Set

*Collection*

With below illustration you will able to Easily Find and Highlight the Largest Values in a Data 

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.

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.