In sometimes, after you rank the values, you may want to color the top three ranking in different colors as below screenshot shown, how can you solve it? In this article, I introduce the method to quickly solve it in Excel.
To color by ranking, you can apply the Conditional Formatting utility.
1. Rank the data first, select a blank cell which you will place the ranking at, type this formula =RANK(B2,$B$2:$B$10), and then drag fill handle over the cells to use this formula.
2. Then keep the formula cells selected and click Home > ConditionalFormatting > New Rules to create a new rule to format the cells.
3. In the New Formatting Rule dialog, select Use a formula to determine which cells to format from the Select a Rule Type list, and type =$C2=1 into the Format values where this formula is true box, and click Format to enable Format Cells dialog, and under Fill tab, choose one color to fill cells. See screenshot:
4. Click OK > OK, and the top 1 has been filled with color.
5. Repeat step 3 and step 4 to color the top 2 and top 3 or top n as you need.
Format values where this formula is true text box, format top 2 use this =$C2=2;
Format values where this formula is true text box, format top 2 use this =$C2=3
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel
2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features
30-day free trial. 60-day money back guarantee.