It is highly unlikely, that you don’t use Excel without formulas. And if suddenly your formulas stop working or return certain errors, we can feel your pain and confusion. It is not a big issue but it can hinder your work because formulas are the most important thing when it comes to automating your Excel projects. Certain formulas don’t update or line spacing can hinder formulas or hidden characters, etc. can cause the problem. Don’t worry, all your confusions and questions will be answered in this article. We are here with some tips if Excel formula not working for you. So let’s get started.
So, basically an Excel file without formula is useless. The importance of an Excel Spreadsheet is because of its formulas and without formulas, Excel cannot perform complex calculations. So, if the formulas in your Excel File is not working and returning an error message or lookup formula not working when you are trying to use them, a few solutions to fix the problems are mentioned below.
1. Matching All Opening and Closing Parentheses in A Formula
If you are an Excel user, then you should know that the arguments of Excel functions have to be placed within parentheses. If you look at the complex formulas then you would see that you will need many sets of parentheses, ideally more than one. So, when you are creating a complex formula like that you need to make sure that you have to pair these parentheses in such a way that you will always have the right parentheses for every formula.
Microsoft Excel shows the pair of parentheses in different colors, so if you are running short of parentheses the Excel will show error messages and you will be asked to balance the pairs. Make sure you do this right and it may solve the Excel sum formula not working issue.
2. Enter All Required Arguments in An Excel Function
As you can see all of the Excel functions have more than one required arguments, some of the functions even have optional arguments which are highlighted in the brackets. As per the rule, a formula must contain all of the arguments required, if not, the Excel will show an error message.
3. Don't Nest More Than 64 Functions in A Formula
You have to remember that when you are nesting more than two Excel functions with each other, you need to know about the following limitations.
- From Microsoft Excel version 2007 to 2016 you use 64 functions only and that’s the upper limit.
- Unfortunately, in Microsoft Excel 2003, the limit is only 7.
This is a common error that many of us do unknowingly.
4. Don't Enclose Numbers in Double Quotes
There are specific ways you have to put numbers in Excel. If you put them enclosed in double quotes they will be recognized as a text string. So, just do not use any double quotes.
So, basically make sure that every time you write a formula in numerical values, just do not write them enclosed in double quotes. You can do it if you treat them only as texts. This is a very small thing but it can return certain errors like Excel sum formula not working returns 0.
5. Enter Numbers without Any Formatting
So, whenever you are using a numerical value in an Excel formula, just stop yourself from assigning currency signs such as “$” or decimal separators.
Just keep in mind that a comma is basically used to separate arguments of a function. So, whenever you are using a currency sign, it makes a cell reference and confuses the system. As stated earlier, these are small yet significant measures.
6. Make Sure That Numbers Are Not Formatted As Text Values
Numbers formatted as text values are one of the biggest reasons that your formula does not work in Excel. If you look at them, they will look like normal numbers, but Excel thinks that they are text strings and does not calculate the values.
So, whenever the numbers are received as texts they will be aligned to the left side, it happens by default. The regular numbers will be right side aligned.
There is a Number Format Box in the Home Tab, inside there is a Number Group that displays the Text format.
If a few cells with text numbers are selected on a spreadsheet, the status bar shows only the count, whereas, it normally shows the average.
In some cases, there will be an apostrophe or a green triangle visible on the top of the cells.
Keep this in mind and you may solve the issue then and there.
7. Separate Function Arguments with Proper Character
Normally, we would separate the function arguments with a comma, but this does not work every time in Microsoft Excel. The character which can be used to separate the arguments depends on the Separator List located in Regional Settings. Like, a comma is the default separator for North America but if you are in Europe then you have to use the decimal symbol.
8. Enclose Workbook and Worksheet Names in Single Quotes
When we are referring to different workbooks or worksheets, we usually use spaces or non-alphabetical characters, when we are naming them. Try to enclose the names in a single quotation. It may end the problem.
9. Include the Full Path to a Closed Workbook
Suppose you are writing a formula which will be referred as an Excel Workbook, then the Workbook name and the path of the Workbook in the external reference should be named clearly. This is very important in order to solve the formula not working issue.
Bonus Tip: How to Unlock Excel File
Protecting your work is very important and it is your job to make sure that no one can get into your important documents and tamper with it. But if you are unfortunate enough to forget the password of your Excel file and turning up every stone to find a way out then we highly recommend you to use PassFab for Excel.
PassFab for Excel is a very efficient and easy to use software that can recover Excel passwords very fast. The software is easy to download and install and it supports all of the versions of Microsoft Excel, including the latest Excel 2019. The step by step recovery process is mentioned below.
Step 1. Launch the Excel Password Recovery on desktop.
Step 2. From the main interface, choose and Click “Add” to upload the locked Excel file for which you want to get the password.
Step 3. You will find 3 decryptions methods namely: Brute-force attack (Default option), Dictionary Attack (Password dictionary), and Brute-force with Mask Attack. Choose one attack and continue with the process.
Step 4. Hit “Start” to start the decrypting process.
Step 5. Now, you will find an alert prompt consisting of your unlocking password.
Summary
It is very clear that without the formulas, Microsoft Excel is not what it is primarily designed for. If you want to create a spreadsheet only putting in the data, it is not enough! You have to apply the proper formula, and implement these techniques so that the software can calculate the data properly and submits the end result without any hindrance. This list contains the most common error fixes and will surely help you fix the issue quickly. Follow these carefully to realize what you were doing wrong in the first place. And if anyhow you get locked out of your own document then you have the option to use PassFab for Excel program. This software will solve your problem fast and open your document immediately.