MS Excel — Two Awesome Conditional Formatting Tips
Use of the INDIRECT and SEARCH functions can turbocharge your Conditional formatting in Excel!
INTRODUCTION
Conditional formatting is a great way to highlight items for the spreadsheet user. But, setting the conditions up can be tricky.
It’s even more confusing when you introduce variables into the formatting (so that user can define what to format).
But, persevere and the payoff is significant!
Two of my favorite techniques when working with lists of data are to highlight entire rows in tables when specific values are found in certain selected table columns (e.g., highlight all rows where company market cap exceeds a certain value).
HOW?
I use two Excel functions which make this happen — INDIRECT and SEARCH, based on scenario.
INDIRECT
“ The Excel INDIRECT function returns a valid reference from a given text string. Use INDIRECT when you need to convert a reference assembled as text into a proper reference.”
SEARCH
“ The Excel SEARCH function returns the location of one text string inside another. SEARCH returns the position of the first character of find_text inside within_text. Unlike FIND, SEARCH allows wildcards, and is not case-sensitive.”
To see how these functions work with conditional formatting, you can check out the Slideshare below:
The application of the SEARCH function can be seen in the video below:
Give it a try and let me know your thoughts!
What conditional formatting tips or tricks do you use?
