1

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:

enter image description here

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?

  • 1
    If the imported CSV data ends up be "text" (Select cell(s), do "Format cell" or hold CTRL hit 1); then you will most likely see strange behavior like this. Check this! If that is not the case, then please provide examples to investigate! – Hannu Apr 20 at 16:00
  • @Hannu I tried changing the cells' type and still have problems. I added an example. – Elzo Apr 20 at 17:41
1

I think Hannu is right about the underlying problem. The values in column G are left justified, which indicates that Excel is treating them as text. There are many ways to convert them to numbers (see for example How to convert text to number in Excel).

  1. If on a given worksheet, importing CSV data is a one-time action, you can convert the data in-place.

    • You can do that by changing the format to a numeric one, but that isn't my preferred method because the sequence in which you do things can make a difference and you need to pay attention to that, so it sometimes isn't "reliable".

    • I prefer an explicit conversion. Copy and Paste Special, selecting Values and Add is one method.

    • Another is to use the Text to columns function. With a column containing only single values, that replaces the column with itself, but numerical strings get re-stored as numbers in the process.

  2. If that CSV data gets replaced with other CSV data, it's more efficient to use a method that you set up once and it works for whatever data is in column G. There are two approaches.

    • One is to include explicit conversion of the column G values within all of your formulas that reference those cells. That has the drawback of making all of your formulas more complicated.

    • A better solution is to add a helper column. You could use column H (or even a remote column) to hold a conversion formula so H always contains the numerical values of whatever is in G. There are a number of ways to do that.

      • You can use the VALUE function (=VALUE(G2)), or

      • do an arithmetic operation that doesn't change the numerical value (=G2+0, =G2*1, or =G2/1).

  • For other readers who land here, the reason that the CSV data was treated as text turned out to be a locale issue in this case. The wrong decimal separator can't be fixed with methods that leave the character string unaltered, like those described in this answer. You need to actually modify the string with the correct decimal separator. How to do that would be appropriate as a separate question so the solutions are searchable and not buried under a different problem. (cont'd) – fixer1234 Apr 20 at 22:18
  • In that situation, the fix will be different, but the advice on where to do it still applies (in-place vs. in formulas vs. helper column). If you don't notice that the data's decimal separator is wrong for your locale, be alerted to that possibility by the simple conversions in this answer not correcting it. – fixer1234 Apr 20 at 22:18
1

The problem was the decimal separator: it was a dot in the CSV file, but Excel expected a comma because of my regional settings.

I replaced dots with commas in the problematic column and Excel started reading it as numbers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.