How to highlight the Min and the Max with color in Excel? And what happens when you update your values? Do the min and the max will change? The conditional formatting tool of Excel does exactly this job.
Highlight the Min and the Max with color in Excel
- Create a conditional formatting rule to highlight the maximum
Select the option to highlight the 10 highest items and change the settings
- Create a conditional formatting rule to highlight the minimum
Select the option to highlight the 10 lowest items and change the settings
What is conditional formatting?
In Excel, if you want your colors to be displayed according to a ruler, you must use the conditional formatting tool.
You can find conditional formatting options on the Home ribbon.
The tool is extremely powerful and allows you to elaborate formatting like
- Add progress bars to your cells
- Add icons to your cells
How to add a color for the maximum?
In this document, we have the sales of the week. How to highlight the maximum of the sales?
- Select the range of cells containing the values where to apply color for the maximum; here H4:H11
- Enable the Conditional Formatting menu > Top/Bottom Rules > Top 10 Items.
- In the dialog box, replace 10 with 1 and choose the format Green.
- When you press OK, the maximum value has now a specific color instantly 😀👍
Change the color for the minimum.
The method is the same except for step 2. There, you must select the option Bottom 10 items.
Again, in the dialog box change the default value 10 by 1 and select the Red format.
What is the impact of new values on the color of the minimum and maximum?
Let's add the values of Saturday to our table. The Total of the week has changed for each employee.
- Without any action from our side, Excel changes the color of the minimum and maximum automatically.
- This is the very principle of conditional formatting, coloring according to a ruler.
Customize color
You can customize the format by selecting the Custom menu from the drop-down menu.
And then change the color and font settings.
