This is a common approach to store numbers such as phone numbers as text to retain the leading zeros. When an apostrophe (‘) is entered before typing in Excel, that tells Excel to store the content as text. An Apostrophe is Entered Storing the Formula as Text Check if there is a space and if so delete it. This is difficult to notice so can go unrecognised, however it will prevent the formula from calculating.ĭouble click the cell, or edit it in the Formula Bar. When typing the formula be sure not to enter a space before the equals. Then re-calculate the formula in the cell by double clicking on the cell and pressing Enter. Change the format to General using the list provided. To check this click on the cell and check the Number group of the Home tab. These will not calculate whilst in this format. Formula Not Calculating as Cell is Formatted as TextĪnother common reasons is accidentally formatting the cells containing formulas as text.

So if you are not aware of this setting, it could still be a reason for the formula not calculating. This setting can be changed by macros, or by other workbooks that you may have opened first. If this is set to manual, the formulas will not update unless you press the Calculate Now or Calculate Sheet buttons.Ĭhange it to Automatic and the formulas will start working. This is the most likely problem.Ĭlick the Formulas tab and then the Calculation Options button. See their site for resources they have developed for teaching data analytics in introductory accounting.The first thing that you should check is that the calculation options are not set to manual.

Ohio Jennifer Cainas, CPA, DBA, is an instructor of accountancy at the University of South Florida in Tampa and Tracie Miller-Nobles, CPA, is an associate professor of accounting at Austin Community College in Austin, Texas. Wendy Tietz, CPA, CGMA, Ph.D., is a professor of accounting at Kent State University in Kent.

Otherwise, Excel will return an #N/A error value.Įxcel will also return an #N/A error value if you use FORMULATEXT on a cell that doesn’t contain a formula, or if the formula can’t be displayed due to worksheet protection. First, note that you can use it to reference a cell in another worksheet or workbook, but the workbook must be open. There are a couple of important items to be aware of when using the FORMULATEXT.

Excel is showing formula not result