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.