You have built an excel model for Sales Forecast using various logics with fairly complicated formulas. So, to avoid all this hassle we have written this in-depth article covering most of the reasons for excel formulas not working.
The original research was conducted by Journal Genome Biology found that there is an increasing trend year on year in terms of a number of errors. You can download complete research materials from SourceForge website Open Source for further reading.
Consider using INDEX/MATCH instead
The above research paper may not be directly linked to formulas not working in excel, but there is a point here:. Having said that, we need to be little careful while using show formulas option , otherwise, you might end up with something like below. Also read: 51 best excel tips and tricks. Although both the names look similar to our naked eye, there is a blank space in the second name.
Similar to leading spaces, nonprintable characters are also one of the reasons for excel formulas not working.
Make sure all your numbers are formatted as numbers instead of text. As per excel calculation rules, your formulas should not reference result cells as a range.
What is a VLOOKUP?
If you do so, circular reference errors would show up. For example, in our below Expense Budget table, we need to add some plug adjustment number to match with the targets.
VLOOKUP or SUM Not Working? Convert Text to Date? (Excel Text to Columns)
So, we have calculated the differential amount in row 13, and now we just need to put them in row 9. If you do so, your link formula will not work instead, you can see zero and possibly you could also see Blue colored arrows pointing cell references. Oh, hang on..
Excel Formula Training
Here is a quick tutorial on cell references. With the help of absolute referencing , you can fix your formula range so that you can copy from one cell to other. Certainly, this is one of the main reason for excel formula not working when copied from one cell to another cell. For example, in our above table, we have used absolute cell referencing to make it easy for us to copy formulas.
Problem: The exact match is not found
Instead of using partial freeze, we have used F4 shortcut key and fixed entire range. Unfortunately, there are no quick fixes for this type of errors.
You have to do it manually by analyzing all the formulas. In our above example, we have added Vlookup formula in cell F3 to find out corresponding revenue amount. If you observe closely, the Revenue amount 25k for customer id is incorrect.
It should have been 65k instead.
There are plenty of Excel Tips and Tricks articles lined up, join our free newsletter to get them directly in your inbox. Just think about it.
Second , you put extra effort and fix all the formulas. And how to fix them. Tips: In general, numbers will always be aligned to right side of the cell but in our above example they are not Sometimes you can see little green tip on top left corner, that indicates something wrong with that cell.