I created a custom report and then exported the results to Excel. This report looked and acted just like it should in Excel except for one peculiar issue when creating a formula. The formula appeared as text and would not calculate.
It looked like a regular formula, but wouldn’t display the results. No matter what I did, I could not get the formula to display the results, it would only display the formula text.
This spreadsheet was a merged CSV file would only show the formulas, but would not calculate any of them. What the developers did as a work around was to add an Excel VBA macro to the download so that the end user could get the formulas to calculate and display the results instead of the formulas. However, this VBA workaround wouldn’t work with some of our staff that didn’t have a current version of Excel.
For the Salesforce.com Exported Reports to an Excel Spreadsheet, I noticed that the problem only occurred when I created a formula that was inserted into the exported reported area. If I put the formula on the outside of the exported report area, it would work just fine.
Nothing seemed to fix the problem. Things I tried that would NOT fix the problem were:
1) Recalculating the Spreadsheet did not work
2) Changing the format of the cells from Text to Number would not work.
3) Copy and pasting the text data into another spreadsheet also did not work.
I was pulling my hair out. I had never seen this problem before. Then, I found a couple of fixes to this problem. You should see this problem and fixes in action with the video tutorial below. You may run into this in many of the exportable text or csv type reports from many systems or databases.
So when you are presented with this type of problem, you can fix this issue in several ways:
Fix 1) Do a Find & Replace in the Excel spreadsheet. Find the “=” (equals sign) and Replace it with an “=” (equals) sign. (RECOMMENDED)
This harmless find and replace will for the formulas to work. How weird is that? I find that this is the best and easiest way to fix this issue.
Fix 2) There is another way to also fix this issue when working with CSV or Text files in Excel. This fix involves using the Text-to-Columns feature. Check out how this works with this step by step and video tutorial here:
Essentially, after you create your formulas, you can highlight the range that needs to be fixed and perform the Text to Columns function from the Excel Data Ribbon in the Data Tools group.
However, you will want to pick Fixed Width choice and only have one column that will be split at the very end of your data (thus no splits will occur). Alternately, you can choose the Delimited option, and Tab Delimited will most likely work or choose the Other category and pick something that does not exist in the spreadsheet like the pipe character “|” (above the enter key) or the tilde character (above the single left quote key).
Fix 3) There is one other way that you can fix this problem. Copy and paste the data into a new spreadsheet/Excel workbook and Paste Special as Values.
In some cases this may not work for if your download/merge file has special formatting that needs to be maintained.
Fix 4) Re-Editing the formula after it has been entered in the spreadsheet and then pressing enter or clickig on the check box in the formula bar.
Hopefully you never run into this problem, but now you know how to work around it.