How to highlight the closest value in a list to a given number in Excel?
Supposing, you have a list of numbers, now, you may want to highlight the closest or several closest values based on a given number as following screenshot shown. Here, this article may help you to solve this task with ease.
To highlight the closest value based on the given number, please do as follows:
1. Select the number list that you want to highlight, and then click Home > Conditional Formatting > New Rule, see screenshot:
2. In the New Formatting Rule dialog box, do the following operations:
(1.) Click Use a formula to determine which cells to format under the Select a Rule Type list box;
(2.) In the Format values where this formula is true text box, please enter this formula: =ABS(A2-$D$2)=MIN(ABS($A$2:$A$15-$D$2)) (A2 is the first cell in your data list, D2 is the given number you will compare, A2:A15 is the number list you want to highlight the closest value from.)
3. Then click Format button to go the Format Cells dialog box, under the Fill tab, choose one color you like, see screenshot:
4. And then click OK > OK to close the dialogs, the closest value to the specific number has been highlighted at once, see screenshot:
Tips: If you want to highlight the closest 3 values to the given values, you can apply this formula in the Conditional Formatting, =ISNUMBER(MATCH(ABS($D$2-A2),SMALL(ABS($D$2-$A$2:$A$15),ROW($1:$3)),0)), see screenshot:
Note: In the above formula: A2 is the first cell in your data list, D2 is the given number you will compare, A2:A15 is the number list you want to highlight the closest value from, $1:$3 indicates that the closest three values will be highlighted. You can change them to your need.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 1 years agoWhat if data is over column and not in a row?
- To post as a guest, your comment is unpublished.· 1 years agoThis is useful, but how would I do the following please. Text is in Col A, the number range is in Col B: when the average has been found using the above method and is thus highlighted, I need the corresponding cell in Col A to also highlight.
- To post as a guest, your comment is unpublished.· 2 years agoHi! I am very thankfull to u for this as this solved my problem for a presentation to a client. However I am looking for something more for my future presentations. like when u say highlight 3 closest(n) values i want to highlight them as: 1) the most closest with Green. 2) the least closest in red and the 3) value which is between most and least closest in yellow. Can u please help me to achieve this. As per the example my d2 value is live updating every minute so i want to know where exactly my d2 value lies on scale of a range(eg. Number list). This will add a brand finish to my presentations. Point to note i am using excel 2007 as many of my clients are still using the same. Just to avoid any compatibility issues i avoid latest versions. Thanks in advance and many regards. :)
- To post as a guest, your comment is unpublished.· 2 years agoAn excellent resource for highlighting the closest value. Since the logic is same , won't it work in Google Sheets? Anyway it is not working in Google Sheets. Would be obliged if you could please clarify!