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.
This blog is also available on YouTube.
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.
Last reviewed: December 2021.
Excel community
This article is brought to you by the Excel Community where you can find additional 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.
Getting to grips with Gen AI
ICAEW's Finance in a Digital World is a suite of elearning modules to support members in understanding digital technologies and their impact on finance, including generative AI. Completing modules can count towards verifiable CPD hours.
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.
