In this post, I will show you how apply Excel conditional formatting to text strings by assigning values to text:
We will use custom number formatting to assign values to a predefined set of text strings (e.g. the value -1= “FAIL”).
Once we’ve assigned the custom number formatting, we can then apply Excel conditional formatting to the range of values.
Applying Excel conditional formatting to text strings allows us to create dashboards that can provide us with the ability to visually see how well our business is performing.
Green lights mean we are performing well, yellow lights let us know that we need to monitor or be cautious about our performance, and red means we are under performing.
This technique can be applied dynamically to any table using other formulas such as match and index look ups to prevent us from manually having to enter these values.
How to Apply Conditional Formatting to Text Strings Using Excel Custom Number Formatting
My Challenge for You
How can you use the match formula to look up text string (i.e. pass, on hold, fail) values from another table and display and apply the conditional formatting in this post? Meaning, if table 2 has our PO number with a status but no conditional formatting, how can we bring that over to table 1 and get that text string status to display with our conditional formatting.
Feel free to leave a comment with your answer. There’s nothing like a little challenge!
DOWNLOAD INSTRUCTIONS!The Excel file is indeed free. When you click the 'GET IT FREE' button be sure to enter in $0 for the value and this will remove the request for your credit card information. All you'll need is your email address!
Can you explain step 9 a little more in detail? I am trying to make it so when I type OK it reads it as a 1 or 100 and when I type LATE is reads it as a 0.
Nice job Brad. One of our readers did a similar thing using text strings but using the cell fill color to enhance his project planning sheets
Cheers
Phil
Yeah definitely a great way to do it as well. Thanks for the comment Philip!
Brad
Can you explain step 9 a little more in detail? I am trying to make it so when I type OK it reads it as a 1 or 100 and when I type LATE is reads it as a 0.
Thanks!