How To Highlight the Closest Value to A Given Number in Excel

How To Highlight the Closest Value to A Given Number in Excel In this Intermediate excel tutorial, I’ll explain how you can find the closest value from a list to given number in excel. Suppose you care creating a cutoff score and you need to find score closest to 65. From an unordered list its quite difficult. But now we can not only find the closest number but also highlight it. We are going to use conditional formatting to solve this and here is how you should do it. Step 1: Select the number list from which you need to find the closest number. Step 2: Under the “Home Tab” click on “Conditional Formatting” and then “New Rule” Step 3: Now select “Use a formula to determine which cells to format” Step 4: In the below box write the formula =ABS(A2-$C$2)=MIN(ABS($A$2:$A$26-$C$2)) Step 5: Now click on “Format” and select “Fill” now select the highlight color. Step 6: Now click ok Twice and Done. From now on whenever you write a number in cell C2 it will find the closest number from the list in cell A2 to A26. Now lets breakdown the formula a bit so that you can customize it to your needs. First, we used ABS function and then Min Function. Abs function converts a number to its absolute value. It also converts negative number to positive number keeping the positive number intact. To learn about Min Function Check this Tutorial:    • How to Use MIN Function in Excel   To learn all about excel functions please check:    • Excel Function Explained   Now in the formula Cell A2 is the first cell of our number list. A26 is the last cell of our number list. C2 is the cell where we provide our random number. Change these cell reference according to your need. This is how we find and highlight the closest number from a list to a given number. Thanks for watching. ---------------------------------------------------------------------------------------- Support the channel with as low as $5   / excel10tutorial   ---------------------------------------------------------------------------------------- Please subscribe to #excel10tutorial https://goo.gl/uL8fqQ Here goes the most recent video of the channel: https://bit.ly/2UngIwS Playlists: Advance Excel Tutorial: https://goo.gl/ExYy7v Excel Tutorial for Beginners: https://goo.gl/UDrDcA Excel Case: https://goo.gl/xiP3tv Combine Workbook & Worksheets: https://bit.ly/2Tpf7DB All About Comments in Excel: https://bit.ly/excelcomments Excel VBA Programming Course: http://bit.ly/excelvbacourse Social media: Facebook:   / excel10tutorial   Twitter:   / excel10tutorial   Blogger: https://excel10tutorial.blogspot.com Tumblr:   / excel10tutorial   Instagram:   / excel_10_tutorial   Hubpages: https://hubpages.com/@excel10tutorial Quora: https://bit.ly/3bxB8JG Website: https://msexceltutorial.com/ Become a member and enjoy exclusive perks while supporting the channel you love!    / @excel10tutorial