Excel Conditional Formatting: Highlight Top 5 Cell Times with Different Colors

Excel's Conditional Formatting feature is an essential tool for data analysis and visualization. It allows users to apply different formats to cells based on specific conditions. In this article, we will cover how to use Excel's Conditional Formatting to highlight the top 5 cells in a column with different colors, based on the time they were last modified, using the Large and Small functions.

Prerequisites

Before we begin, make sure you have the following prerequisites:

Microsoft Excel 2010 or later
A dataset with a column containing the last modified time for each cell

Steps to Highlight Top 5 Cells with Different Colors

Here are the steps to highlight the top 5 cells in a column with different colors based on their last modified time:

Step 1: Select the Data Range

First, select the range of cells that you want to apply the conditional formatting to. In this example, we will use column A with the last modified times.

Step 2: Use the Conditional Formatting Feature

Click on the "Home" tab in the Excel ribbon, then click on "Conditional Formatting" in the "Styles" group.

Step 3: Choose the "Highlight Cells Rules" Option

In the dropdown menu that appears, choose "Highlight Cells Rules" and then "Top 1 Items." This will highlight the cell with the highest value in the selected range.

Step 4: Modify the Conditional Formatting Rule

To highlight the top 5 cells instead of just the top one, we need to modify the Conditional Formatting rule. Right-click on the cell with the current formatting and choose "Conditional Formatting Rules Manager."

Step 5: Add New Rule

Click on "New Rule" and choose "Format Cells that are Top 5 Items." In the "Format" tab, choose the formatting you want for the second-highest cell. Repeat this process for the third, fourth, and fifth cells, using different colors.

Step 6: Use Large and Small Functions

To apply the Conditional Formatting rule based on the last modified time, we need to use the Large and Small functions. Add the following formula to a new column (let's call it column B) next to the column with the last modified times:

=IF(LARGE(A2:,ROW(A2:))=A2,IF(SMALL(A2:,ROW(A2:))=A2,,),)

Replace with the formatting for the second-highest cell, with the formatting for the third-highest cell, and with the formatting for all other cells.

Step 7: Apply the Formatting to the New Column

Select the range of cells in column B that contains the formulas, and apply the Conditional Formatting rule as before.

In this article, we covered how to use Excel's Conditional Formatting feature to highlight the top 5 cells in a column with different colors based on their last modified time. We used the Large and Small functions to apply the formatting to a new column, and then applied the Conditional Formatting rule to that column.