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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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.· 8 months agoWhat if data is over column and not in a row?
To post as a guest, your comment is unpublished.· 8 months 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.· 1 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!