I have an Excel 2016 spreadsheet that uses a CSV file as a data source. I would like to use conditional formatting and formulas to visualize that data more easily.

The problem is that formulas and conditional formatting act as if the CSV-imported cells were empty. The formulas and conditional formatting rules I use work as expected on "normal" cells.

Here is an example:

As shown, cell `I6`

contains the formula `=G4+G5`

. `I7`

contains `=SUM(G1:G10)`

. I set column `G`

's type to Accounting, which automatically changed `I6`

and `I7`

to Accounting too. And yet the formulas return errors.

This kind of defeats the point of importing data. How can I apply formulas and formatting rules to imported data without copying it by hand every time the source is modified?