Formulas shown in Excel, instead of their results

This content is 11 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Every month or so, I have to create a report which is extracted from SharePoint to Excel, and then a couple of extra columns are added with information required by a colleague. The additional columns simply present existing information in a different format but, for some reason, after pasting in these formulae (which are just nested IF statements combined with some string handling functions), the formula is shown instead of the calculated result:

=IF(LEFT(L2,1)=”A”,”Application”,(IF(LEFT(L2,1)=”B”,”Business”,(IF(LEFT(L2,1)=”I”,”Infrastructure”,”Error”)))))
=LEFT(L2,1)&”:”&RIGHT(LEFT(L2,SEARCH(“>”,L2,30)-2),LEN(LEFT(L2,SEARCH(“>”,L2,30)-2))-SEARCH(“>”,LEFT(L2,SEARCH(“>”,L2,30)-2)))

Last month, whilst troubleshooting the issue, I stumbled across a great blog post from Excel MVP, Purna Duggirala (aka Chandoo), entitled “Excel Formulas are not working?!? What to do when all you see is the formula, not result“. Today, I needed to find it again, so I thought I’d blog it this time!

Chandoo’s post lists a number of causes for this issue – mine was a simple issue of cell formatting – after pasting my formula into a new column, the cell is formatted as Text. ¬†Switching the cell format to General, and editing the formula (no changes required, just F2 to edit, then Enter to commit), results in a calculation. Even better, Excel 2010 auto-fills and calculates the following rows for me!

The blog at Chandoo.org (tagline, “Become awesome at Excel”!) is packed with Excel advice and is definitely one to remember next time I’m having problems (or just want to do something a bit different in a spreadsheet).

One thought on “Formulas shown in Excel, instead of their results

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.