How to Fix Excel 2019 Formulas Not Working Properly | Step-by-Step Guide
![](/images/site-logo.png)
How to Fix Excel Formulas Not Working Properly | Step-by-Step Guide
Summary: Excel formulas sometimes fail to function correctly and even return an error. This article explains what you might be doing wrong that prevents Excel formulas from working properly and solutions to resolve the issue. If your formulas have disappeared from the Excel spreadsheet and you are having trouble recovering them, you can use an Excel repair tool to recover the formulas.
When working with Excel formulas, situations may arise when the formula doesn’t calculate or update automatically. Or, you may receive errors by clicking on a formula.
Problems Causing the ‘Excel Formulas not Working Properly’ Issue and Solutions
Let’s check out the possible reasons that cause Excel formulas to work properly and solutions to resolve the issue.
Problem 1 – Switching Automatic to Manual Calculation Mode
Automatic and manual are the two modes of calculation in Microsoft Excel.
By default, Excel is set to automatic calculation mode. Everything is recalculated automatically when any changes are made in a worksheet in this mode. You may switch from automatic to manual mode to disable the recalculation of formulas, particularly when working with a large Excel file with too many formulas.
Excel will not calculate automatically when set to manual calculation mode. And this may make you think that the Excel formula is not working properly.
Solution – Change Calculation Mode from Manual to Automatic
To do so, perform these steps:
- Click on the column with problematic formulas.
- Go to the Formulas tab, click the Calculation Options drop-down, and select Automatic.
Problem 2 – Missing or Mismatched Parentheses
It’s easy to miss or incorrectly place parentheses or include extra parentheses in a complex formula. If a parenthesis is missing or mismatched and you click Enter after entering a formula, Excel displays a message window suggesting to fix the issue (refer to the screenshot below).
Clicking ‘Yes’ might help fix the issue. But Excel might not fix the parentheses properly, as it tends to add the missing parentheses at the end of a formula which won’t always be the case.
Solution – Check for Visual Cues When Typing or Editing a Formula with Parentheses
When typing a formula or editing one, Excel provides visual cues to determine if there’s an issue with the parentheses inserted in a formula. Checking for these visual cues can help you fix missing/mismatched parentheses.
- Excel helps identify parenthesis pairs by highlighting them in different colors. For instance, the pair of parenthesis outside is black.
- Excel does not make the opening parentheses bold. So, if you’ve inserted the last closing parentheses in a formula, you can determine if your parentheses are mismatched.
- Excel helps identify parentheses pairs by highlighting and formatting them with the same color once you cross over them.
Problem 3 – Formatting Cells in an Excel Formula
When adding a number in an Excel formula, don’t add any decimal separator or special characters like $ or €. You may use a comma to separate a function’s argument in an Excel formula or use a currency sign like $ or € as part of cell references. Formatting the numbers may prevent the formula from functioning correctly.
Solution – Use Format Cells Option for Formatting
Use Format Cells instead of using a comma or currency signs for formatting a number in the formula. For instance, rather than entering a value of $10,000 in your formula, insert 10000, and click the ‘Ctrl+1’ keys together to open the Format Cells dialog box.
Problem 4 – Formatting Numbers as Text
Numbers are displayed as left-aligned in a sheet in a worksheet, and text formatted numbers are right-aligned in cells. Excel considers numbers formatted as text to be text strings. Thus, it leaves those numbers out of calculations. As a result, a formula won’t work as intended. For example, in the following screenshot, you can see that the SUM formula works correctly for normal numbers. But, when the SUM formula is applied to numbers formatted as text, the formula doesn’t return the correct value.
Sometimes, you may also see an apostrophe in the cells or green triangles in the top-left corner of all the cells when numbers in those cells are formatted as Text.
Solution – Do Not Format Numbers as Text
To fix the issue, do the following:
- Select the cells with numbers stored as text, right-click on them, and click Format Cells.
- From the Format Cells window, click on Number and then press OK.
Problem 5 – Double Quotes to Enclose Numbers
Avoid enclosing numbers in a formula in double-quotes, as the numbers are interpreted as a string value.
Meaning if you enter a formula like =IF(A1>B1, “1”), Excel will consider the output one as a string and not a number. So, you won’t be able to use 1’s in calculations.
Solution – Don’t Enclose Numbers in Double Quotes
Remove any double quotes around a number in your formula unless you want that number to be treated as text. For example, you can write the formula mentioned above as “1” =IF(A1>B1, 1).
Problem 6 – Extra Space at Beginning of the Formula
When entering a formula, you may end up adding an extra space before the equal (=) sign. You may also add an apostrophe (‘) in the formula at times. As a result, the calculation won’t be performed and may return an error. This usually happens when you use a formula copied from the web.
Solution – Remove Extra Space from the Formula
The fix to this issue is pretty simple. You need to look for extra space before the equal sign and remove it. Also, ensure there is an additional apostrophe added in the formula.
Other Things to Consider to Fix the ‘Excel Formulas not Working Properly’ Issue
- If your Excel formula is not showing the result as intended, see this blog .
- When you refer to other worksheets with spaces or any non-alphabetical character in their names, enclose the names in ‘single quotation marks’. For example, an external 5reference to cell A2 in a sheet named Data enclose the name in single quotes: ‘Data’!A1.
- You may see the formula instead of the result if you have accidentally clicked the ‘Show Formulas’ option. So, click on the problematic cell, click on the Formula tab, and then click Show Formulas.
- If you’re getting an error “Excel found a problem with one or more formula references in this worksheet”, find solutions to fix the error here .
Conclusion
This blog discussed some problems you might make causing an Excel formula to stop working properly. Read about these common problems and solutions to fix them. If a problem doesn’t apply in your case, move to the next one. If you cannot retrieve formulas in your Excel sheet, using an Excel file repair tool like Stellar Repair for Excel can help you restore all the formulas. It does so by repairing the Excel file (XLS/XLSX) and recovering all the components, including formulas.
Also read:
- How to Repair corrupt MP4 and MOV files of Ace 2V?
- How to recover deleted pictures from Y27 5G.
- 2 Ways to Transfer Text Messages from Tecno Camon 20 Premier 5G to iPhone 15/14/13/12/11/X/8/ | Dr.fone
- How To Recover Data From Lost or Stolen iPhone SE In Easy Steps | Stellar
- How to sign .rtf file document electronically
- How to get back lost contacts from Vivo Y36i.
- How to install and update hardware device drivers manually on Windows 11
- How to Repair a Damaged video file of Vivo V29 using Video Repair Utility on Windows?
- How to install device drivers manually in Windows 11/10
- How to recover deleted contacts from Motorola Razr 40 Ultra.
- How to Restore Deleted Infinix Hot 30 5G Pictures An Easy Method Explained.
- How To Recover Whatsapp Chat History From iPhone 13 Pro Max | Stellar
- How to Downgrade iPhone 7 Plus to the Previous iOS/iPadOS Version? | Dr.fone
- How to Reset iPhone 12 Pro Without Apple Password? | Stellar
- How to Retrieve deleted photos on Narzo 60 5G
- How to get back lost photos from Samsung Galaxy A54 5G.
- 5 Easy Ways to Copy Contacts from Itel P55+ to iPhone 14 and 15 | Dr.fone
- How to Fix the Unable to Record Macro Error in Excel 2013?
- How to Rescue Lost Messages from Vivo S17
- How To Restore Missing Pictures Files from Oppo Reno 9A.
- How to recover old videos from your Honor Magic 6 Lite
- How to Nubia Red Magic 8S Pro Get Deleted Phone Number Back with Ease and Safety
- How To Restore Missing Contacts Files from Asus ROG Phone 8 Pro.
- How to Remove MDM from iPhone 14 without password?
- How to restore wiped music on Motorola
- How to Fix iPhone XR Stuck at attempting data recovery Loop | Stellar
- How to Retrieve deleted photos on Y28 5G
- How To Restore Missing Pictures Files from V30 Lite 5G.
- How To Restore Missing Music Files from Reno 10 Pro 5G
- How to get back lost photos from HTC U23.
- How to sign a .xlsx file document online
- How to play an MP4 on Galaxy A54 5G?
- How to recover old messages from your Honor Play 8T
- How To Recover Whatsapp Chat History From iPhone 12 Pro | Stellar
- How to restore wiped call history on Infinix Hot 30 5G?
- How To Recover Lost Data on iPhone 6s Plus? | Dr.fone
- How to recover lost data from Note 30 5G?
- How to rescue lost call logs from Moto G14
- How To Install the Latest iOS/iPadOS Beta Version on iPhone 14 Pro? | Dr.fone
- How to Itel P40+ Get Deleted Pictures Back with Ease and Safety?
- How To Restore Missing Contacts Files from Motorola Moto G34 5G.
- How To Recover iPhone SE (2022) Data From iCloud? | Dr.fone
- How To Restore Missing Messages Files from Tecno Pop 8
- How To Free Up iPhone 12 Space | Dr.fone
- How to retrieve erased call logs from HTC U23?
- How to Repair Broken video files of Xiaomi Redmi Note 12 4G on Mac?
- How to play MOV files on Redmi A2+ ?
- How to recover old messages from your Lava Storm 5G
- How to recover old videos from your Realme Note 50
- How to sign .xltx by digital signature
- New Windows 11 Video Editing Essentials Top Tools and Features
- How to Flash Dead Infinix Note 30 VIP Racing Edition Safely | Dr.fone
- How to Detect and Stop mSpy from Spying on Your Oppo Reno 8T 5G | Dr.fone
- New Whats the Best 10 Photo Animator Templates That Bring Your Images to Life for 2024
- New 2024 Approved Extract Audio From MP4 The Best MP4 to MP3 Converters This Year
- In 2024, Catch or Beat Sleeping Snorlax on Pokemon Go For Gionee F3 Pro | Dr.fone
- How to Remove a Previously Synced Google Account from Your Samsung Galaxy S23 Tactical Edition
- In 2024, The Ultimate Guide to Soundcloud MP3 Conversion
- New How to Splice Videos Together on iPhone for 2024
- How to Transfer Data from Itel P40 to Any iOS Devices | Dr.fone
- How To Do Vivo T2x 5G Screen Sharing | Dr.fone
- In 2024, How to use Snapchat Location Spoofer to Protect Your Privacy On Samsung Galaxy A34 5G? | Dr.fone
- Cellular Network Not Available for Voice Calls On Motorola Edge 40 | Dr.fone
- How to Bypass iPhone SE Passcode Easily Video Inside | Dr.fone
- The way to get back lost music from Honor V Purse
- How to Sign Out of Apple ID On Apple iPhone XS Max without Password?
- Is your Infinix Zero 30 5G working too slow? Heres how you can hard reset it | Dr.fone
- Why Your WhatsApp Live Location is Not Updating and How to Fix on your Realme GT 5 Pro | Dr.fone
- Planning to Use a Pokemon Go Joystick on Honor 80 Pro Straight Screen Edition? | Dr.fone
- In 2024, 5 Ways to Transfer Music from Apple iPhone 12 Pro to Android | Dr.fone
- All You Need To Know About Mega Greninja For Realme Narzo 60 5G | Dr.fone
- For People Wanting to Mock GPS on Infinix Hot 40 Devices | Dr.fone
- Latest way to get Shiny Meltan Box in Pokémon Go Mystery Box On Samsung Galaxy S23 Ultra | Dr.fone
- How to Transfer Data from Apple iPhone 14 to ZTE Phones | Dr.fone
- Downloading SamFw FRP Tool 3.0 for Itel A60
- For People Wanting to Mock GPS on Vivo Y78 5G Devices | Dr.fone
- How Can I Use a Fake GPS Without Mock Location On Vivo T2x 5G? | Dr.fone
- 10 Fake GPS Location Apps on Android Of your Nokia C210 | Dr.fone
- In 2024, 9 Best Phone Monitoring Apps for Huawei Nova Y71 | Dr.fone
- In 2024, How to Change your OnePlus Open Location on Twitter | Dr.fone
- New In 2024, Live Deepfakes Made Easy How to Download and Make Your Own Live Model
- Full Guide to Funcall Voice Changer and Its Alternatives
- How to Remove an AirTag from Your Apple ID Account From Apple iPhone 14 Plus?
- Title: How to Fix Excel 2019 Formulas Not Working Properly | Step-by-Step Guide
- Author: Nova
- Created at : 2024-05-19 18:32:11
- Updated at : 2024-05-21 02:34:23
- Link: https://blog-min.techidaily.com/how-to-fix-excel-2019-formulas-not-working-properly-step-by-step-guide-by-stellar-guide/
- License: This work is licensed under CC BY-NC-SA 4.0.