Excel – How to save xls{,x} as csv file so that formulas show as text

microsoft excel

I have a large spreadsheet in which many of the cells contain formulas.

How can I produce a delimited-text version of this spreadsheet such that all formulas appear as the text of their source code?

For example, for a spreadsheet in which column A is

14
59
27

cell B1 is set to =SUM(A:A), and all remaining cells are empty, if we did "Save As" CSV, the resulting *.csv file would look like this (last two cells of second column are empty):

14,100
59,
27,

In contrast, what I want would look this:

14,=SUM(A:A)
59,
27,

In fact, the non-formula values are unimportant (except as placeholders), so the alternative below would be fine too. Here the only non-empty cells are those corresponding to the formula-containing cells in the original sheet; in this case, the leading '=' can can be omitted from the non-empty cells:

,SUM(A:A)
,
,

Thanks!

PS. I'm VBA-illiterate, so I'd like to avoid it if at all possible.

Best Answer

I just tested it and it's a lot easier than I thought to get output like the 2nd case you specified.

Do the following:

  1. Input all your data/formulas
  2. Select the Show Formulas button (via Formulas > Formula Auditing in Excel 2007)
  3. Save As filetype CSV(MS-DOS)
  4. Open resultant file in Notepad to verify.

I'll play around with it for a bit more to see if I can get it to pare down the formulas like you mentioned in case 3.

Related Question