I’ve got a workbook set up in Excel 365 (2016) to Get External Data into columns 1-8. I have the first two columns configured do some formatting based on G1=1. The formatting works fine if I type a 1 in the appropriate column. However, I have the data connection writing data to column G, and the import from the external data source does NOT trigger the conditional formatting. If I find a cell that retrieved a 1 from the data connection and re-type the 1, the formatting is triggered. Explicitly formatting column G as number does not help. Is there a way to conditionally format based on data retrieved via an external data source?
As the Original Poster found out, the culprit is usually an analysis check on data types.
When importing data, if left in it’s default settings, the data will be imported as text. A number is a number, text or otherwise, but it’s not until you have to perform calculations that you might encounter errors in data types.
“In computer science and computer programming, a data type or simply type is a classification of data which tells the compiler or interpreter how the programmer intends to use the data. Most programming languages support various types of data, for example: real, integer or Boolean.”
To add to the explanation above, a type of data can also be: Character, String, or ‘literal’.
In Excel it’s important to also remember that if you change a number’s format from text to number, you’ll have to refresh that cell. One way to refresh a cell after the format has changed is to select the cell, press F2, then enter.
Conclusion: Often in the computer world we might look at a number and assume it’s identity. A computer may read a number in many different ways: 1, “1”, ” 1″.
Troubleshooting: Verify the cell’s format and checking for extra characters (i.e. spaces). Also remember to Refresh the cell if the format is changed. This is not in any way a natural process of reasoning unless you have been exposed to computer science studies and even if you are, these conclusions are easily overlooked and often are.