Highlight top 3 values

This tutorial shows how to highlight the top 3 numbers in a range using Excel and VBA

EXCEL METHOD 1. Highlight top 3 values

EXCEL

Select range > Home tab > Style group > Conditional Formatting > New Rules > Format only top or bottom ranked values > Select Top > Enter 3 > Format > Fill tab > Select color > OK > OK
1. Select the range in which you want to highlight the top 3 numbers.
Note: In this example we are selecting a range of cells from B3 to B9. 		Select range of cells - Highlight highest value
2. Select the Home tab. Select Home tab - Excel 2016
3. Click on Conditional Formatting in the Style group.
4. Click on New Rules. 		Select Conditional Formatting in Style group and click New Rules
5. Select Format only top or bottom ranked values.
6. Select the Top option from the drop down menu.
7. Enter a value of 3 in the input box menu.
Note: By default this value in 10.
8. Click on the Format button. 		Select options to highlight top 3 number
9. Click on the Fill tab and select the color that you want to highlight the cells that contain the highest 3 value.
10. Click on OK. 		Select the Fill tab, select color and click OK
11. Click OK in the New Formatting Rule dialog box. Click OK in New Formatting Rule - top 3
12. This image show the result of these steps
Note: In this example the top 3 numbers in the selected range (B3:B9) are 700, 500 and 450 which are captured in cells B7, B3 and B6, respectively, and now highlighted in the color that we selected. 		Highlighted top 3

VBA METHOD 1. Highlight top 3 values using VBA

VBA

Sub Highlight_top_3_values()
'declare variables

Dim ws As Worksheet
Dim ColorRng As Range
Dim ColorCell As Range

Set ws = Worksheets("Sheet1")
Set ColorRng = ws.Range("B3:B9")
Topn = 3
'highlight the cells with top 3 numbers
For x = 1 To Topn
For Each ColorCell In ColorRng

If ColorCell.Value = Application.WorksheetFunction.Large(ColorRng, x) Then
ColorCell.Interior.Color = RGB(220, 230, 248)
End If

Next
Next x

End Sub

NOTES
Note 1: This VBA code will highlight the cells that contains the 3 largest numbers in range B3 to B9 of "Sheet1".

RELATED TOPICS
Related Topic Description Related Topic and Description
Highlight cells with unique values How to highlight cells that contain unique values in a selected range
Highlight highest value How to highlight the highest number in a range
Highlight lowest value How to highlight the lowest number in a range