Why do my excel formulas show as text
“Oh My God”excel formulas not working in my report.
Sounds familiar right?
If so,don’t worry, you are just one among many excel users who face this problem very often.
Just think about it.
Youhave built an excel model for Sales Forecast using various logics with fairly complicated formulas.
[‘IF’ formulas with 64 conditions –just kidding]
And when you try to use that model, you see some of your formulas either not working or looks broken.
Basically, two things can happen here:
Oneyou might want to say something loudly [I refrain from using that word].
Second, you put extra effort and fix all the formulas.
So, to avoid all this hassle we have written this in-depth article covering most of the reasons for excel formulas not working.
And how to fix them.
Excel formulas not working: Why & How to fix them
According to Washington post article on excel errors,“The Australian researchers found that roughly 1 in 5 of these papers included errors in their gene lists that were due to Excel automatically converting gene names to things like calendar dates or random numbers.”
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.
The above research paper may not be directly linked to formulas not working in excel, but there is a point here:
Very well built & tested software like MS Excel can go wrong in terms understanding our datawhat about you & me?
So, here are our top 10 reasons[let the party begain]
#1 Calculation Options [mostlikey reasons for Excel formulas not updating]
Changing Calculation option to ‘manual‘ is a best practice but you need to be very careful while using.
By default, Calculation option will be set toautomaticin excel.
But there are instances where you might have turned that to ‘manual‘ to speed up the process.
If you turn this option ‘ON‘ then your formulas will not be updatedautomatically, this in turn increase spreadsheet processing speed.
Once you were done, you need to click onSaveorRefresh workbookto recalculate. If you haven’t done that then you will end up with incorrect results.
How to fix this issue:
Head over to Formulas → Calculation → Click on Calculate now/Calculate Sheet option.
You can alternatively use the F9 shortcut key to refresh the workbook.
Here is our video guide on excel calculation options.
- Make a habit of pressing Ctl+S [it will force calculation]
- Do not use this option for every workbook, unless if you have huge file with lot of formulas
- This option is workbook specific thus you need to turn on/off in every workbook that you want to use
#2 Excel formula not calculating just showing formula [show formulas]
Show formulas is an option in excel to display all the workbook formulas with a single click.
This option is very useful to find out all the formulas in a worksheet.
Having said that, we need to be little careful while usingshow formulas option, otherwise, you might end up with something like below.
Can you see Today formula is being displayed as a formula instead of showing actual date?
Same goes with Randbetween formulas as well.
How to fix this issue:
It’s very simple:
Head over to Formulas → Under formula Auditing → Click on “Show Formulas” (With the first click formulas will show up and Second click they will hide behind cells and start working)
[Please refer above screenshot]
#3 Extra Space(s) is an extra headache
You have been extracting data using CSV (comma separated values) format, then you’re data mostly exposed to unwanted spaces.
Do you know extra spaces cause a lot of problems while using Vlookup formula?
Sometimes it’s quite hard to identify these spaces.
For example, let’s say if you want to do Vlookup using employee name as a base, so your employee name looks like below
Have you noticed?
Although both the names look similar to our naked eye, there is a blank space in the second name.
This would not meet exact match criteria in Vlookup formula thus results in#N/Aerror.
How to fix this issue:
The extra space(s) issue can be fixed usingTrim formula.
In general, Trim formula will find out more than one spaces between words and it will delete.
- It’s always good idea to useTrim formulabefore Vlookup in both the tables (lookup & data table)
- The trim function will ignore blank spaces between letters (it will only work with words)
#4 Get rid of Nonprintable/Hidden characters
Similar to leading spaces, nonprintable characters are also one of the reasons for excel formulas not working.
In case, if you have data with these characters, it’s better to take them off before working further.
How to fix this issue:
Similar to trim, you can use the CLEAN formula to delete these characters.
[please refer below example]
#5 Excel formatting – don’t try to compare Apples with Oranges
It’s very important to have correct formatting before using formulas like Vlookup, Hlookup, and Match & Index.
Numbers formatted as a text is one of the common reason for formulas not working in excel.
Although they look like numbers but they are not.
For example, on the left-hand side, you can see Customer ID looks similar to numbers.
But, if you refer above formatting section, you could see that they have been formatted as text.
This, in turn, will trigger #N/A error while adding Vlookup formula.
The worst part is, it’s a bit difficult to identify.
How to fix this issue:
Make sure all your numbers are formatted as numbers instead of text. It’s worth to re-format lookup column before using Vlookup formula.
- 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
#6 Circular references (Errors due to an endless circle)
In simple terms, your formula range is in an endless loop or it’s in a circle.
As per excel calculation rules, your formulas should not reference result cells as a range. If you do so,circular reference errorswould show up.
Personally, I’ve struggled many times with these type of errors.
For example, in our below Expense Budget table, we need to add some plug (adjustment number) to match with the targets.
So, we have calculated the differential amount in row 13, and now we just need to put them in row 9.
One simple way is by linking row 13 cells in row 9.
If you do so, your link formula will not work instead, you can seezeroand possibly you could also seeBlue colored arrowspointing cell references.
[Please refer below]
In general, it should work.
But technically, it will be an error because our formula cell references are in a loop.
How to fix this issue:
First, we need to identify error cells.
Go toFormulas tab→ click onError Checkingdrop-down arrow → place cursor onCell references →now you will see error range(s)
Second, now you know the circular references range, go to each error cell and fix the formulas.
#7 Using double quotes incorrectly – human error
We often usedouble quoteswhile writing formulas.
But, it’s very important to understand when to use and when to not.
For example, to join two numbers we can use a formula like below.
= 1234 & 5678 = 12345678
Alternatively, to join two text strings you can’t do the same instead, you need to specify text within double Quotes.
=John&Smith: Wrong way of using[you willmost likelysee #NAME error]
=” John “&” Smith ” = This the correct way.
Same goes with conditional formulas like IF, AND, OR. It’s very important to specify text within double quotes.
Otherwise, this is one of the reasons for excel formulas not working.
#8 BODMAS – basic rule for every calculation
Do you know every calculation in excel is based on ‘BODMAS’ rule?
Oh, hang on.. what is BODMAS rule?
It’s a mathematical rule to determine an order of operations.
Ok, let’s simplify further.
Can you guess what is the answer for the below?
Is that 40, congratulations you are wrong.
Can you try same calculation in excel?
You will most likely see 30, indeed that’s the correct answer… but how?
According to BODMAS rule, the calculation will take place based on a sequence i.e → B- Brackets, O- Order, D- Division, M- Multiplication, A- Adition & S- subtraction.
So, the sequence of the above formula is → 10*2 = 20 → 20+10 =30.
Your formulas can go wrong if you don’t consider BODMAS.
How to fix this issue:
It’s very simple, you just need to change sequence by using parentheses like below.
So, anything in brackets will be calculated first and then it will go to next operation.
#9 Incorrect use of “absolute” referencing
Everyone who uses Excel mostly know what is absolute referencing.
If not,please stop right hereand learn about it. Here is a quick tutorial on cell references.
With the help ofabsolute referencing, you can fix your formula range so that you can copy from one cell to other.
That’s indeed a very useful feature. That being said, if you don’t know how to use it then your formulas will not work as they intend to be.
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.
But, we did a mistake.
Instead of using partial freeze, we have used F4 shortcut key and fixed entire range.
That’s indeed resulted in incorrect results.
So, the correct way should have been using absolute reference for only row’s like this =SUM(D$3:D$6).
How to fix this issue:
Unfortunately, there are no quick fixes for this type of errors. You have to do it manually by analyzing all the formulas.
But, the best way I found so far is by cross-checking formulas & results as soon as I use absolute & relative referencing.
Please let me know your workarounds at this point.
- Do not use F4 shortcut key for partial freeze
- Try to include a dollar sign ($) manually for partial range
#10 Incorrect formula arguments
Incorrect formula arguments can have a negative impact on formula results.
Just to give you some perspective, let’s understand belowvlookup syntaxespecially the last one[range lookup].
Range lookup argument has two options to select.
- True – Approximate match
- False – Exact match
Personally, I feel using“Approximate match”option is like committing suicide.
BecauseApproximate matchwill never return accurate results.
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 6378993 is incorrect. It should have been 65k instead.
So, what went wrong?
As I mentioned before, I’ve used‘Approximate match’underrange lookupargument.
The“match”is another formula where you have an option to select similar arguments.
How to fix this issue:
Never ever use ‘Approximate match’in your vlookup formulas.
Now It’s Your Turn
Do you think you have some other reasons for excel formulas not working?
Or, maybe you have a question to ask.
Either way, please comment below I’ll respond as soon as possible.
There are plenty of Excel Tips and Tricks articles lined up, join our free newsletter to get them directly in your inbox
[Don’t worry, we hate spam & we respect your privicy].
Excel shows formula but not result
Have you entered a formula, but Excel is not calculating a result?
Every once in a while, you might find Excel behaving in a bizarre or unexpected way. One example is when you accidentally trigger the scroll lock feature. Another example is when one or more formulas suddenly stops working. Instead of a result, you see only a formula, as in the screen below:
The VLOOKUP formula is correct, why no result?
This can be very confusing, and you might think you’ve somehow broken your spreadsheet. However, it’s likely a simple problem. With a little troubleshooting, you can get things working again.
There are two main reasons you might see a formula instead of a result:
- You accidentally enabled Show Formulas
- Excel thinks your formula is text
I’ll walk through each case with some examples.
Show Formulas is enabled
Excel has a feature called Show Formulas that toggles the display of formula results and actual formulas. Show Formulas is mean to give you a quick way to see all formulas in a worksheet. However, if you accidentally trigger this mode, it can be quite disorienting. With Show Formulas enabled, columns are widened, and every formula in a worksheet is displayed with no results anywhere in sight, as shown in the screens below.
Show Formulas disabled (normal mode)
Show Formulas enabled
To check if Show Formulas is turned on, visit the Formula tab in the ribbon and check the Show Formulas button:
Show Formulas enabled – just click to disable
The reason Show Formulas can be accidentally enabled is because it has the keyboard shortcut (Control `) that a user might unknowingly type. Try Control ` in a worksheet to see how it works. You’ll see you can quickly toggles all formulas on and off.
Show Formulas toggles the display ofeveryformula in a worksheet. If you are having trouble with asingleformula, the problem isn’t Show Formulas. Instead, Excel probably thinks the formula is text. Read on for more information.
Excel thinks your formula is text
If Excel thinks a formula is just text, and not an actual formula, it will simply display the text without trying to evaluate it as a formula. There several situations that might cause this behavior.
No equal sign
First, you may have forgotten the equal sign. All formulas in Excel must begin with an equal sign (=). If you leave this out, Excel will simply treat the formula as text:
Broken formula example – no equal sign (=)
Space before equal sign
A subtle variation of this problem can occur if there is one or more spaces before the equal sign. A single space can be hard to spot, but it breaks the rule that all formulas must start with an equal sign, so it will break the formula as shown below:
Formula wrapped in quotes
Finally, make sure the formula is not wrapped in quotes. Sometimes, when people mention a formula online, they will use quotes, like this:
In Excel, quotes are used to signify text, so the formula will not be evaluated, as seen below:
Note: you are free to use quotesinsideformulas. In this case, the formula aboverequiresquotes around criteria.
In all of the examples above, just edit the formula so it begins with an equal sign and all should be well:
For reference, here is the working formula:
Cell format set to Text
Finally, every once in a while, you might see a formula that is well-formed in every way, but somehow does not display a result. If you run into a formula like this, check to see if the cell format is set to Text.
If so, set the format to General, or another suitable number format. You may need to enter cell edit mode (click into the formula bar, or use F2, then enter) to get Excel to recognize the format change. Excel should then evaluate as a formula.
Tip – Save formula in progress as text
Although a broken formula is never fun, you can sometimes use the "formula as text problem" to your advantage, as a way to save work in progress on a tricky formula. Normally, if you try to enter a formula in an unfinished state, Excel will throw an error, stopping you from entering the formula. However, if you add a single apostrophe before the equal sign Excel will treat the formula as text and let you enter without complaint. The single quote reminds you that the formula has been intentionally converted to text:
Later, you can then come back later to work on the formula again, starting where you left off. See #17 in this list for more info.
Excel Formulas are not working, not updating & calculating (Quick and Easy fixes!)
Excel formulas are not updating
The value returned by Excel’s formula doesn’t update automatically – the cell with the formula continues to show the old value even after changing the values of the dependent cells.
The problem is most likely caused by accidentally changing the calculation setting fromAutomatictoManual. To fix this we just have to set theCalculationoption back toAutomatic.
To change the Calculation option we have to go through these simple steps
- InExcel 2010–2016, click on File >> Options >> Formulas >> Calculation options section >> Under Workbook Calculation SelectAutomatic
- InExcel 2007, click on the Office button >> Excel Options >> Formulas >> Workbook Calculations here selectAutomatic
- InExcel2003, click on Tools >> Options >> Calculation >> Calculation selectAutomatic
If you are using new ribbon interface you can go to the Formulas tab > Calculation group, click on Calculation Options and from the drop-down menu selectAutomatic.
Make excel recalculate automatic
" data-medium-file="https://i2.wp.com/skillamplifier.com/wp-content/uploads/2017/11/Calculation-options-Automatic.png?fit=300%2C59&ssl=1" data-large-file="https://i2.wp.com/skillamplifier.com/wp-content/uploads/2017/11/Calculation-options-Automatic.png?fit=640%2C126&ssl=1" gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" data-lazy-type="image" data-lazy-src="https://i2.wp.com/skillamplifier.com/wp-content/uploads/2017/11/Calculation-options-Automatic.png?resize=640%2C126&ssl=1" alt="Excel formulas update automatically" width="640" height="126" data-lazy-srcset="https://i2.wp.com/skillamplifier.com/wp-content/uploads/2017/11/Calculation-options-Automatic.png?w=1298&ssl=1 1298w, https://i2.wp.com/skillamplifier.com/wp-content/uploads/2017/11/Calculation-options-Automatic.png?resize=300%2C59&ssl=1 300w, https://i2.wp.com/skillamplifier.com/wp-content/uploads/2017/11/Calculation-options-Automatic.png?resize=768%2C151&ssl=1 768w, https://i2.wp.com/skillamplifier.com/wp-content/uploads/2017/11/Calculation-options-Automatic.png?resize=1024%2C201&ssl=1 1024w, https://i2.wp.com/skillamplifier.com/wp-content/uploads/2017/11/Calculation-options-Automatic.png?resize=880%2C173&ssl=1 880w, https://i2.wp.com/skillamplifier.com/wp-content/uploads/2017/11/Calculation-options-Automatic.png?resize=450%2C88&ssl=1 450w" data-lazy-sizes="(max-width: 640px) 100vw, 640px" data-recalc-dims="1" />
Force excel to recalculate formulas
" data-medium-file="https://i0.wp.com/skillamplifier.com/wp-content/uploads/2017/11/Update-formulas.gif?fit=300%2C191&ssl=1" data-large-file="https://i0.wp.com/skillamplifier.com/wp-content/uploads/2017/11/Update-formulas.gif?fit=600%2C381&ssl=1" gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" data-lazy-type="image" data-lazy-src="https://i0.wp.com/skillamplifier.com/wp-content/uploads/2017/11/Update-formulas.gif?resize=640%2C406&ssl=1" alt="Make excel updating formulas" width="640" height="406" data-recalc-dims="1" />
Force Excel Formulas to Recalculate!
Ok then if you have really good reason to keep your calculation method toManualat least how can you refresh all formulas? To make excel formulas recalculate just hitCalculatebutton on the ribbon!
If you like using shortcuts you can try one of those:
- To recalculate thewhole workbookpressF9or go toFormulastab on the ribbon and underCalculation grouphitCalculate Now
- To run recalculation only onActive SheetpressShift + F9or go toFormulastab on the ribbon and underCalculation GrouphitCalculate Sheet
- If you want to recalculate all the sheets in all open workbooks pressCtrl+Alt+F9
If you need to recalculate onlyOne Formulaonone sheetjust enter the editing mode by eitherdouble click on the cellorpressing F2, then hitEnter!
Excel shows the Formula, not the Result
There are three main reasons why this may happen to you, we will discuss all three of them!
Formula is entered as Text
Frequently when we enter formulas in excel don’t think of the Number Format. However, sometimes we may enter a formula that has been formatted as Text, to check this just select the cell with the formula and look at theNumber Formatbox on theHomeTab.
Excel showing formula not the result
" data-medium-file="https://i2.wp.com/skillamplifier.com/wp-content/uploads/2017/11/Formula-as-text.jpg?fit=300%2C118&ssl=1" data-large-file="https://i2.wp.com/skillamplifier.com/wp-content/uploads/2017/11/Formula-as-text.jpg?fit=640%2C251&ssl=1" gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" data-lazy-type="image" data-lazy-src="https://i2.wp.com/skillamplifier.com/wp-content/uploads/2017/11/Formula-as-text.jpg?resize=640%2C251&ssl=1" alt="Excel is not updating formulas" width="640" height="251" data-lazy-srcset="https://i2.wp.com/skillamplifier.com/wp-content/uploads/2017/11/Formula-as-text.jpg?w=794&ssl=1 794w, https://i2.wp.com/skillamplifier.com/wp-content/uploads/2017/11/Formula-as-text.jpg?resize=300%2C118&ssl=1 300w, https://i2.wp.com/skillamplifier.com/wp-content/uploads/2017/11/Formula-as-text.jpg?resize=768%2C302&ssl=1 768w, https://i2.wp.com/skillamplifier.com/wp-content/uploads/2017/11/Formula-as-text.jpg?resize=450%2C177&ssl=1 450w" data-lazy-sizes="(max-width: 640px) 100vw, 640px" data-recalc-dims="1" />
If that is the reason just change theNumber FormattoGeneraland hitEnter!
Show Formulas is turned ON
The second reason why you see your formula instead of the result may be theShow Formulasoption. To turn that option OFF you just have to go toFormulastab and underFormula Auditingturn offShow Formulasbutton.
There is a space before your Formula
The last case in which you can have cells showing formulas instead of results is when you have entered aspaceorapostrophebefore theFormula(Equal sign).
In that case Excel formats the cell asTextand you won’t see the result. To fix this issue, just delete the leading space or apostrophe from the cell!
Excel is not updating the results
" data-medium-file="https://i0.wp.com/skillamplifier.com/wp-content/uploads/2017/11/Space-before-the-formula.jpg?fit=300%2C152&ssl=1" data-large-file="https://i0.wp.com/skillamplifier.com/wp-content/uploads/2017/11/Space-before-the-formula.jpg?fit=341%2C173&ssl=1" gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" data-lazy-type="image" data-lazy-src="https://i0.wp.com/skillamplifier.com/wp-content/uploads/2017/11/Space-before-the-formula.jpg?resize=341%2C173&ssl=1" alt="Excel is showing formulas instead of result" width="341" height="173" data-lazy-srcset="https://i0.wp.com/skillamplifier.com/wp-content/uploads/2017/11/Space-before-the-formula.jpg?w=341&ssl=1 341w, https://i0.wp.com/skillamplifier.com/wp-content/uploads/2017/11/Space-before-the-formula.jpg?resize=300%2C152&ssl=1 300w" data-lazy-sizes="(max-width: 341px) 100vw, 341px" data-recalc-dims="1" />
If your formulas are not still not working make sure you check our post about8 Most common mistakes when using Excel Formulas.
Microsoft Office Tips and Tricks – Computergaga Blog
Microsoft Office Tips and Tutorials » 5 Reasons Why your Excel Formula is Not Calculating
5 Reasons Why your Excel Formula is Not Calculating
When your Excel formulas are not calculating, or not updating, it can be very frustrating. Your formulas are the driving force for your spreadsheet.
There are 5 reasons for your Excel formula not calculating are many. In this tutorial we explain these scenarios.
Watch the Video
1. Calculation Options is Set to Manual
The first thing that you should check is that the calculation options are not set to manual. This is the most likely problem.
Click theFormulastab and then theCalculation Optionsbutton.
If this is set to manual, the formulas will not update unless you press theCalculate NoworCalculate Sheetbuttons.
Change it toAutomaticand the formulas will start working.
This setting can be changed by macros, or by other workbooks that you may have opened first. So if you are not aware of this setting, it could still be a reason for the formula not calculating.
2. The Cell is Formatted as Text
Another common reasons is accidentally formatting the cells containing formulas as text. These will not calculate whilst in this format.
To check this; click on the cell and check the Number group of theHometab.
If it displaysText. Change the format toGeneralusing the list provided.
Then re-calculate the formula in the cell by double clicking on the cell and pressing Enter.
3. A Space is Entered Before the Equals
When typing the formula be sure not to enter a space before the equals. This is difficult to notice so can go unrecognised, however it will prevent the formula from calculating.
Double click the cell, or edit it in the Formula Bar. Check if there is a space and if so delete it. The formula will update.
4. An Apostrophe is Entered Storing the Formula as Text
When an apostrophe (‘) is entered before typing in Excel, that tells Excel to store the content as text. This is a common approach to store numbers such as phone numbers as text to retain the leading zeros.
This however could be the reason why your formula is not calculating.
The apostrophe will not be visible in the cell on the spreadsheet, but you can see it in the Formula Bar.
Double click the cell, or edit it in the Formula Bar and delete the apostrophe.
5. The Show Formulas button is Turned On
The final reason could be that theShow Formulasbutton on theFormulastab is turned on. This can easily be done accidentally, or possibly by someone else using this workbook previously.
This button is used when auditing formulas. It shows the formula instead of the formula result, stopping them from calculating. This can be helpful when troubleshooting formula problems.
Simply click theShow Formulasbutton again to turn it off and the formula will be working.