Hello all and welcome back to the Excel Tip of the Week - it's our birthday! Today the TOTW is one year old. I hope you've enjoyed the posts to date and I am looking forward to another great year of tips, tricks, and ideas. Because we've now covered so much, from now every so often we will take a week to revisit a popular older topic. This will let the TOTW continue to teach core Excel topics without becoming stale. Anyhow, for this week, we have a Creator post in which we will be taking a look at a few causes of a common Excel problem: formulas not calculating properly. Possible cause 1: Cells are formatted as text Description: A cell contains what looks like a formula, but the formula doesn’t calculate. Instead the text of the formula itself appears in the cell directly. See example below. Cause: The cell is formatted as Text, which causes Excel to ignore any formulas. This could be directly due to the Text format, or is particularly common when importing data from a CSV or Notepad file. Fix: Change the format of the cell(s) to General or some other format. However, the formulas still won’t start working until you force Excel to reconsider the content. You can do this in one of two ways. Method 1: Jump into the affected cell (mouse click or F2) and then immediately exit again. This is quicker for a single formula but if there are many formulas it will be time-consuming and you should use Method 2 instead. Method 2: Use Find and Replace (Ctrl + F). Choose to replace = with = and this will cause Excel to refresh the formulas and begin calculating as normal. Possible cause 2: The workbook is set to Manual calculation Description: A selection of formulas are written correctly, but display results that don’t agree to the correct inputs or don’t make sense. Cause: The workbook has been placed in “Manual calculation” mode. This will mean that Excel doesn’t automatically update all formulas whenever the workbook is amended and needs to be manually forced to do so. This also means that if you copy and paste a formula (like the addition formula shown on the right), the result is copied instead of the correct answer until the manual recalculation prompt is used. Fix: You can either force a manual recalculation, or turn the calculation back to automatic. Note that manual calculation is usually set when the workbook is very large and unwieldy to speed up calculation times. Method 1: You can force a manual recalculation either by pressing the Calculate button in the bottom left most corner of Excel, or by pressing F9. Method 2: You can switch the workbook back to Automatic calculation from Formulas => Calculation options. These two are the cause of 99% of problems of this type. If you have a problem that isn’t solved by either method, feel free to make a comment below! Previous post TOTW index Next post This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.
My issue just started today but is affecting some of my team members as well. When I drag the formula down, the formula reads correctly, going from Concatenate(A1," ",B1) to Concatenate(A2," ",B2), however the cell would still show Nashville TN instead of going to Denver CO. The calculation options are set to automatic, and when we click into the function bar and hit enter, the information updates, but that's not the best workaround for lots of data. Any thoughts? I did reboot, no difference.
Thanks Shaheen. I was going crazy trying to figure out what I was doing wrong. Turns out I accidentally clicked the "Show Formulas" icon in the "Auditing Formula" sub-section of the Formula section. Unclick it and boom, everything is right as rain and goes back to normal
I found another possible reason. If you have activated (intentionally or otherwise) the "Show Formulas" button, then Excel will do exactly that. It will show you the formula instead of the result.
I assume you want to see the calculated result, which is how you arrived on this page (unless you actually want to see the formula). So unclick and you will see the calculated result.
ELW - do the problem cells have an unusual kind of formula in them? The shortcut you mentioned does a full calculation tree rebuild and so should kick any gremlins out but it's possible if the formulas are strange (or have inputs which are strange formulas), or use UDFs, that might also be the issue.
Alex - that does most closely resemble the manual / automatic calculation problem. Do check for that 'Calculate' sign in the bottom left of the screen. Also watch out for rounding / presentational differences - e.g. if 0.004 is entered into a percentage cell, it will show 0.4%; but a cell rounded to 2dp will show 0.00.