Tech News Today
  • Hardware
    • Motherboards
    • CPUs
    • Graphic Cards
    • RAM
    • SSDs
    • Computer Cases
    • Monitors
    • Peripherals
    • Power Supply Unit
    • PC Builds
    • Computer Tips
  • Software
  • Operating System
    • Windows
    • Mac
    • Linux
  • Gaming
  • Mobile
  • Console
  • More
    • Internet
    • Networking
    • Security
    • Buyer’s Guide
    • Gadgets
    • Laptops
    • Reviews
    • How To
    • News
Facebook Twitter Instagram
Tech News Today
  • Hardware
    • Motherboards
    • CPUs
    • Graphic Cards
    • RAM
    • SSDs
    • Computer Cases
    • Monitors
    • Peripherals
    • Power Supply Unit
    • PC Builds
    • Computer Tips
  • Software
  • Operating System
    • Windows
    • Mac
    • Linux
  • Gaming
  • Mobile
  • Console
  • More
    • Internet
    • Networking
    • Security
    • Buyer’s Guide
    • Gadgets
    • Laptops
    • Reviews
    • How To
    • News
Tech News Today
Home»Software»Excel»Excel Formula Not Working? Here’s How to Fix it

Excel Formula Not Working? Here’s How to Fix it

Asmi DhakalBy Asmi DhakalJanuary 1, 2023
excel formula not working

Excel offers over 300 library functions. Although using these functions has made calculations easier, using them correctly can sometimes be tricky. Even the most experienced users can sometimes run into Excel formulas not working. You may be encountering one of these situations in your Excel program if your formula isn’t working:

  • A pound (#) error code
  • Excel displaying wrong results
  • Excel displays the formula, but no results
  • “There’s a problem with this formula..” error message
  • “..Links to one or more external source..” error message

The fixes to Excel formulas not working depending on which of these issues you’re dealing with. Besides human errors, Excel may incorrectly calculate formulas if there is an issue with your Office app. In such cases, you must reinstall MS Office to fix the problem with its program files.

Table of Contents

  • Solve Excel Error Message
    • #NAME?
    • #VALUE!
    • #REF!
    • ###
    • #DIV/0!
    • #NULL
    • #N/A
    • #NUM
  • Solve Excel Not Calculating Formula
    • Disable Show Formula
    • Enable Automatic Calculation
    • Format Cell to General 
  • Check Arguments
  • Check References
  • Use Less Nested Functions
  • Check for Syntax Error

Solve Excel Error Message

Errors in Excel

Excel has a total of eight error messages. Excel displays each of these messages for different purposes.

#NAME?

If you encountered the #NAME? error, there is an issue with the function you’ve used in the formula. Check if you’ve correctly entered the function in your formula.

#VALUE!

Excel displays the #VALUE! error when there is an issue with the cell format. Recheck the cell format of the referred cells from your formula. If they’re under a different cell format, change it from the Home tab.

#REF!

You encounter the #REF! error when Excel has a problem locating the reference from your formula. Check if you’ve properly referenced your cell. If you’ve used absolute referencing, see if you’ve moved your data to a different location.

###

Excel displays this error when your cell content does not have enough room. Select the column with the error code and expand the column to display the full value.

#DIV/0!

If you run into this error, Excel alerts you that your data was divided by 0. Any value divided by 0 is infinite, and Excel cannot process it. To fix this error, change your data where it is not divided by 0.

#NULL

You probably have entered an incorrect syntax if you entered the #NULL error. Check if you’ve properly used the separators, including comma (,) and colon (:). 

#N/A

Check if the cell you’ve referred to in the Excel formula is removed. Excel mostly displays the #N/A error when Excel cannot locate the referred cell.

#NUM

Excel uses the #NUM error to alert you that your number has non-numeric content. If you’ve entered a comma (,) or a special symbol such as the dollar ($) sign, remove these values to solve this error message.

Solve Excel Not Calculating Formula

If Excel only shows formulas but not results, there are three possibilities you could explore. You may either have the Show Formula or Manual calculation options enabled. 

Excel will also fail to calculate if the cell you’ve entered in your formula is formatted as text. If you have entered ` before data, Excel will read your data as text. If you do not intend to use it as a text, remove the ` symbol from your data.

Disable Show Formula

  1. Launch MS Excel to open your document.
  2. Head to Formulas from the menubar.
  3. If the Show Formula option is selected, click it to deselect it.
    Excel Show Formula

Enable Automatic Calculation

  1. Open your spreadsheet.
  2. Select Formulas from the menubar.
  3. Click Calculation Options.
  4. From the fly-out menu, choose Automatic.
    Automatic Calculation Options

Format Cell to General 

  1. In your workbook, select the cell or cell range with the formula.
  2. On the Home tab, select the fly-out menu in the Numbers section.
  3. Choose General.
    General cell format

Check Arguments

Except for PI and TODAY, Excel formulas need arguments to calculate the results. If you haven’t entered your arguments, Excel formulas will not work. Excel will present a problem with this formula error message in such cases. If you’ve entered your arguments, you should double-check them to ensure you have entered them correctly.

If your formula looks right, select a different list separator from the Excel options. For example, you may use a comma (,), but you’ve selected a semi-colon (;) as your list separator. You can change this configuration from advanced Excel settings.

You can configure list separators by thousands and decimals from Excel settings.

  1. Launch Excel.
  2. Head to Options from the sidebar.
    Excel Options
  3. Select Advanced from the left panel.
  4. Scroll down to the Editing options section and deselect Use system separators.
  5. Enter the appropriate symbol for Thousands and Decimal separators.
    List separator Excel

Check References

Excel allows you to reference another worksheet or cell in its grid. If you have made such references, check if they’re correct. Additionally, Excel formulas with references will not work if you’ve moved or deleted the reference.

Circular Referencing Excel

Make sure you haven’t created a Circular reference in your sheet. A circular reference is when you refer to the same cell you’re entering your formula in. This creates an infinite loop Excel cannot calculate.

Use Less Nested Functions

Excel only supports a total of 64 functions in a single formula. If you have nested more than 64 functions in a formula, break the formula down into smaller formulas. Not only will it bypass the restriction, but you will also leave less room for error.

Check for Syntax Error

Use Correct Syntax

Usually, there are four symbols in an Excel formula; the equals to, comma, parentheses, and the double quotation sign. If you do not use these symbols correctly in your formula, Excel will fail to calculate a result.

Formulas in Excel must start with an equal to (=) sign. Excel will only read your data as a formula once you start it with the equals sign.

Similarly, references are usually separated by a comma sign (,) in a formula. If you do not use a separator, Excel will read your references as a single entity and may not work.

Arguments in a formula are written inside Parentheses (). Especially in nested functions, ensure you have entered an equal amount of open and closed parenthesis.

Only use the double quotation mark (“ ”) to add text to your formula. Excel will not recognize the syntax if you’ve entered your value in single quotes (‘ ’). Additionally, ensure you do not enter a numeric value you intend to manipulate inside double quotes. Excel will read your numeric value as text in the formula. This will produce a different result than you expected.

If you’ve used operators such as Ampersand (&), Multiply (*), and Divide (/) make sure you’ve used the correct symbols. If you use a different symbol, you will get a different result.

fix
Asmi Dhakal
  • Twitter
  • LinkedIn

Asmi Dhakal is a Tech Writer specializing in Microsoft Office-based programs at TechNewsToday. She aims to use her experience to help others learn their way around its programs. Asmi’s interest in Office products grew when she first entered the marketing industry. While she initially struggled to understand their application, she eventually understood her way around them. In her articles, she exhibits a strong understanding of related issues and attempts to push only relevant fixes. Over time, she has grown a particular fondness for MS Excel and spends her free time learning Visual Basic Analysis (VBA). She’s always on the lookout for productivity tools that can get the job done quicker in Excel. Her approach to related problems is very humane, helping new users not only fix the issue but learn more about its nature. Asmi also heavily indulges in personal writing. She is a disciplined journal writer and has maintained a regular journal for 8 years! When her schedule allows, she works to rehabilitate street animals. You can contact her at asmi@technewstoday.com

Related Posts

check mark in excel

How to Put Check Mark in Excel

January 20, 2023
best excel shortcuts

Best 52 Excel Shortcuts Only Experts Know

January 19, 2023
How to Find and edit Hyperlinks on Excel

How to Find and Edit Hyperlinks on Excel

January 23, 2023
how to find unique values in excel

How to Find Unique Values in Excel

January 15, 2023
how to find links in excel

How to Find and Edit External Links in Excel

January 13, 2023
reference isn't valid excel

Reference Isn’t Valid Excel Error – 6 Ways to Fix it

January 6, 2023
Add A Comment

Leave A Reply Cancel Reply

Latest Posts
usb a vs usb c

USB A Vs USB C – What’s the Difference?

January 31, 2023
how long does a cmos battery last

How Long Does a CMOS Battery Last

January 25, 2023
thunderbolt vs usb c

Thunderbolt Vs USB C: What’s the Difference

January 25, 2023
You may also like
Razer Mouse Not Working

Razer Mouse Not Working? Here’re 9 Ways to Fix It

February 3, 2023
how to make cursor bigger

How to Make Cursor Bigger

February 3, 2023
surface-screen-flickering

Why is My Surface Screen Flickering? 6 Ways to Fix It

February 3, 2023
Recommended
Cookie Clicker Garden Guide

Cookie Clicker Garden Guide to Unlocking Every Seed

September 26, 2021
monitor no signal

Computer Turns On But Monitor Says No Signal (9 Ways To Fix)

November 10, 2022
Facebook Twitter Pinterest
  • Home
  • About Us
  • Our Team
  • Editorial Guidelines
  • Privacy Policy
  • Affiliate Disclosure
© 2023 TechNewsToday.

Type above and press Enter to search. Press Esc to cancel.