Conditional Formatting Not Applying Unless I Modify Cell Content with F2

I have a big spreadsheet fed by a data model in Excel 2016.  Several columns are numeric values and the end user wanted conditional formatting to apply colors to certain value ranges (green for scores 51-100, yellow for 41-50, and red for scores 40 and below).

When I created my first conditional formatting rule for the scores 51 and above, it highlighted everything.  Only when I updated a cell with F2 did the correct condition apply.  Naturally, I didn’t want to modify over 300 cells in at least 4 columns, especially if my model updated with new data.

After a couple of hours of searching the online world using a multitude of combinations of the words in the subject above, I discovered the problem.

Even though the numbers appeared visually as numbers and even though I set my cell formatting for those columns to numbers, the underlying model had stored them as text.

I returned to my data model and sure enough, the data type for my numeric columns were set to text.  I changed them to whole numbers, refreshed my query, and the condition applied properly.

 

Leave a Reply

Your email address will not be published. Required fields are marked *