As part of our Speedy Spreadsheet Week, I have asked you to share your favorite tips & techniques for speeding up Excel. And what-a-mind-blowing response you gave. 75 of you responded with lots of valuable tips & ideas to speed-up Excel formulas, VBA & Everything else.
How to read this post?
Since this is a very large article, I suggest reading few tips at a time & practicing them. Consider bookmarking this page so that you can refer to these wonderful ideas when you are wrestling with a sluggish workbook.
Thanks to all the contributors
Many thanks to everyone who shared their tips & ideas with us. If you like the tips, please say thanks to the contributor.
Please note that I am not able to share some of the files you emailed as they contained personal / sensitive data.
Read Excel Speeding-up tips by area
This page is broken in to 3 parts, click on any link to access those tips.
Formula Speeding-up Tips
VBA / Macros Optimization Tips
Everything Else
Share your tips
Formula Speeding-up Tips
I use Formula-Calculation Options-Manual to disable calculations when setting up complex inter-relating formula pages. This stops Excel from churning through calculations every time I change a cell, saving my time. I just hit F9 to recalculate when I want to see the results.
I use
Application.ScreenUpdating = False and
Application.Calculation = xlManual
to speed up macros, and
Application.StatusBar = LoopNum
so I can see the status of my macro and estimate how long there is left to calculate. Don’t forget to switch these back at the end of the macro!
When I have complex formulas with results that won’t change, I hard-code these to save calculation time, but I keep the formula only in the first cell, or pasted in a comment.
Hi Chandoo,
In spreadsheets that have vlookups, if the source file is not going to change; I have realized that it is better to paste-special the vlookup values. This is because even a couple of vlookup slows down the file massively on account of recalculating of values.
Another step I take (this depends on the criticality of data and other factors) is to set the auto-save function in excel to an infrequent duration.
Adi
Replace sum products with count ifs or sum ifs where possible – they calculate a million times faster!!
Avoid large number of SUMIFS, instead, aggregate data into a PivotTalble, then use the Index(Match) combo to locate the sums.
I have dramatically sped up large worksheets doing this.
1. Change Calculation to Manual mode. Calculate manually only when required.
2. Delete all name ranges, unused area, unnecessary formatting.
I think some of the more basic, but highly effective tips to speed up larger workbooks are:
1.) Avoid array formulae, where possible. Everyone knows there are a million ways to skin the proverbial Excel cat. Find alternatives to array.
2.) Adjust the calculation options, if necessary. Frequent calculations = sluggishness. A word of warning, though – people need to know if calculations aren’t automatic, or it can/will cause confusion.
3.) If all else fails, copy and paste as value. If the recipients of your data don’t need the flexibility to enter new data and update values with calculations, take formulae out of the equation (no pun intended) all together.
I replaced all my SUMPRODUCT formulas with SUMIFS and calculation time went from about 50sec to instantaneous. My system is a AMD 6 processor with 8gig memory, Excel 2007.
Stay away from array formulas (unless to have calculations on Manual).
A simple, little tip/trick for speeding up calculating:
Sometimes in a workbook you have so many formulas that for effective work you have no other choice but to turn off the auto-calculating. Still you work on your workbook, writing new formulas, there is no problem if you just wrote one formula in one cell – it can be calculated by just F2-Enter combo. Problem is when you created a new formula for a whole column – you don’t have to calculate whole workbook now or “F2-enter” every cell – just select the column you want to calculate, Ctrl+H and change “=” for … “=”. It’s a known trick, still some people may not know it yet. Cheers.
PS. I don’t work on English version of Excel so my translations may not be accurate.
I use templates with formulas in them that I add data to every month, and once I paste the next months data I copy down the formulas recalculate and then copy and paste the formulas except for one line of formulas for next month. In this way my spreadsheet of 200-300k & growing lines doesnt have to recalculate all the rows everytime.
I set the Automatic Calculation option to manual make any changes in Excel and then hit F9 to calculate as and when required or set back to Automatic once I have completed any large or slow spreadsheets. Save me so much time and frustration. I would love to hear any other tips on speeding up spreadsheets.
Cut down on the use of Array formulas – particularly if they are nested in IF statements.
Speed tips for formulae
1 As you type formula after the =sign, when the prompt appears select the down arrow key and press Tab key so that the function is inserted. Then press the fx in the formula bar to bring up the prompts and start filling the blanks
2 Use f4 key for referencing
3 When using the Rept formula use “l” which is L in small caps and then type the number of times you want to rept.
4 can combine 2 rept commands by shrinking the column width than doing long formulae
5 Rept formula is a powerful tool and can used to show both negative and positive values For e.g. profit and loss A/C can be shown in rept formula
another use of rept formula is to use it for confidence interval with mean in the middle.
6 To make Vlook up to look up values in the right side: copy and paste the columns next to each other and perform vlook up. it is easy and there is no need for another formula For eg;Name and Phone number in two columns
Vlook up will look up the name and will return the phone number. If we have phone number and want the name then we need to write a match and index, Instead if you copy name and phone next to each other then for the phone number vlook up will return the name. That is easy.
I am feeling sleepy after this. More later
Nice subject!
My 2 cts.
A. Formulas
1. If you need to turn off recalc, it is time for a redesign.
2. Avoid array formulas (this includes sumproduct), instead use helper columns which have intermediate results. Easier to debug and very often much faster
3. Avoid VLOOKUP, especially on large tables, instead, use INDEX combined with MATCH, where you use a helper column for the match so you only ask Excel to search your table once for each row instead of once for each column in a row.
4. Do your summarizing with Pivot tables instead of functions
5. Be prudent with range names. Use them sparingly and limit them to constants. Formulas with range names are harder to audit because of the extra layer between your formula and the grid.
6. Visit www.decisionmodels.com, the site contains a wealth of information on recalculation in Excel.
I work with files that use a lot of data tables. In order to avoid excessive delays I will turn off the automatic setting under calculation options and select automatic except for data tables. In addition, I have noticed that excessive conditional formatting can really bog down the spreadsheet as well. Thus, I try to limit and consolidate formatting needs where I can.
Use as many array formulas as possible on the staging worksheet. That way the Excel or UDF functions are called as few times as possible.
The way I speed up my workbooks is by pasting values (instead of keeping the formulas) once the data is no longer going to be updated. For example, I have files that track activity that has happened each quarter. The sheets often have 35,000 rows of data and formulas in each of the 10 columns (for each row). As soon as the quarter is over, I paste the values over the formulas since things won’t be changing any longer.
Perform paste down macros for all calculations. These use dynamic named ranges to select a row of formulas, then paste them in against a table of data. This way you can calculate formulas against thousands of rows and then copy-paste special with values. Removing live formulas seriously reduces calculations times for workbooks with 1K+ rows of data.
Perform Sorts and then use range formula (OFFSET, INDEX) to select a subset of rows, rather than using conditional formula on whole columns. SUMIF, COUNTIF, array formulas etc are very slow on big columns of data. Sorting can filter a table to records that share the same attribute and range formulas can pick up row numbers to only select a sorted block of values.
Keep use of array formulas to a minimum. Keep calculations running sequentially from top left to bottom right when possible. Break up larger internal formula calculations into smaller bites (more columns etc). Look for formula parts shared by formulas. Use offset to keep lookup formulae to the minimum required ranges. Use built-in formulas whenever possible.
I work with large workbooks with extensive formula throughout. I used to use VBA to paste in formulas then I would value them out, but my clients couldn’t easily modify the formulas if they desired a change. Since then I would place a formula row at the top of the data and use VBA to copy that row and paste formulas below, calculate then value them out. The client can then modify the initial row of data to suit their needs. This greatly improved save and load times.
When I have thousands of rows of equations (all the same), I convert all but the top row to values. Then I create a macro that spreads the equations from the top row down to all the necessary rows and makes them values again. Saves a lot of excel recalculating.
use iferror instead of if(iserror(…
I have Excel 2003 files of 45 Mb plus that track daily shift performance that have lots of vlookups, conditional formats, data validation, event triggered VBA. To speed things up I cheat! The historic data is copy-special pasted over itself to turn it into values only – so when auto updates happen they only process the “current data”.
One thing I do where there are multiple columns with formulas is this:
Once my formulas have all calculated and I know the the results won’t change, I copy the formula and put it at the top of my spreadsheet. Then put a red top & bottom border around the formula so I can easily find them.
I then copy the data set full of formulas and re-paste it on itself (keyboard shortcut – copy/file/paste special/values). The spreadsheet calculates much faster.
When I need to update the data I just copy the row of formulas and paste them over the data rows.
save as .xlsb to speed up opening time/decrease file size. also changed =if(iserror) to =iferror to speed up processing. changed from vlookups to pivot table/=getpivotdata format to speed up processing
a) Delete/or clear contents on all blank cells under & to the right of my data. b) On old inherited files, clear out old range names. c) use specific cell references for vlookups (rather than entire columns) d) remove as many calc’s as possible (copy-paste-special values) e) keep pivot tables in separate file from data file f)Stopped using arrays & sumproduct() completely 🙁 g) now considering upgrading to 64 bit OS & 64 bit MSOffice 2010 (currently using 32 bit MSExcel 2010 on XP)
1. Define name of ranges and Use it in the Formula if data is flowing from database.
2. Remove the unused name or name resulted any error or scope outside the workbook.( Formulas—>name manager)
Reduce Images / Shapes that reduces the performance
Linking all my dashboards with pivot tables and queries for to update complex data with one click.
My array formulas used to reference an entire row or column (e.g. A:A or 1:1), and I’m pretty sure that slowed down the sheet. I shrunk the reference to go through, say, row 5000, and it appears to have helped the problem.
No doubt excel is a powerful analytical tool but most of the people do not plan before designing there spreadsheet. One should plan the Start and End in mind, and the assumption that the spreadsheet will never be used again should kept out of mind. Perhaps this is might be the number one rule. Spreadsheets are about giving correct information to the user, not possible erroneous information that looks good.
Excel Best Practices & Design
Formatting
Your spreadsheet should be easy to read and follow. Most of the users spend about 30%, or more, of their time formatting their spreadsheets. Use the cell format of Text if really necessary. Any cell containing a formula, that is referencing a Text formatted cell, will also become formatted as Text. This format is not usually needed but very much used. If you apply a number format to specific cells avoid applying the format to the entire column. If you do, Excel will assume you are using these cells.
Layout
Try and ensure all related raw data is on one Worksheet and in one workbook. When putting in headings bold the font. This will help Excel recognize them as headings when you use one of its functions. When putting data into the data area of your spreadsheet try to avoid blank rows and columns. This is because a lot of Excels built-in features will assume a blank row or column is the end of your data. Use real dates for headings and format them appropriately. If you want the names of the months as headings type them in as 1/1/2001, 1/2/2001, 1/3/2001 etc then format them as “mmmm”. This is a very simple procedure that is all too often overlooked by many. Don’t put in one cell what could go in more than one cell, i.e. the names of 100 people to put into your spreadsheet, don’t put their full name in one cell. Instead, put the First name in one cell and their surname in the next cell to the right.
Formulas
This is the biggest part of any spreadsheet! Without them you really only have a document. Excel has over 300 built in Functions (with all add-ins installed), but chances are you will only use a handful of these.
The usual practice in regards to formulae in Excel is the referencing of entire columns, this is a big mistake! This forces Excel to look through potentially millions, of cells which it need not be concerned with at all. One of the very best ways to overcome this is to familiarize you with the use of dynamic named ranges.
Speeding up Re-calculations
A common problem with poorly designed spreadsheets is that they become painfully slow in recalculating. Some people will suggest that a solution to this problem is putting a calculation into Manual via Tools>Options>Calculations. A spreadsheet is all about formulas and calculations and the results that they produce. If you are running a spreadsheet in manual calculation mode, sooner or later you will read some information off your spreadsheet which will not have been updated, this means using F9 on regular intervals, which can cause bad results, because Pressing F9 can be overlooked.
Arrays, Sumproduct (used for multiple condition, summing or counting), UDFs, Volatile Functions and Lookup functions, can slow down the recalculations of spreadsheet.
Array Formulas
The biggest problem with array formulas is that they look efficient. An Array must loop through each and every cell they reference (one at a time) and check them off against a criteria. Arrays are best suited to being used on single cells or referencing only small ranges. A possible alternative are the Database functions. Another very good alternative which is mostly overlooked is the Pivot tables. Pivot Tables can be frightening at the first site but it is the most powerful feature of Excel.
UDF (User Defined Functions)
These are written in VBA and can be used the same way as built in functions can be, but unfortunately, no matter how good the UDF is written the, it will perform at the same speed as one of Excel’s built-in functions, even if it would be necessary to use several nested functions to get the same result. UDFs should only be used if an Excel function is not available
Volatile Functions.
Volatile functions are simple functions that will recalculate each time a change of data occurs in any cell on any worksheet. Most functions which are non-Volatile will only recalculate if a cell that they are referencing has changed. Some of the volatile functions are NOW(), TODAY(), OFFSET(), CELL(),INDIRECT(), ROWS(), COLUMNS() . If you are using the result of these functions frequently throughout your spreadsheet, avoid nesting these functions within other functions to get the desired result especially in array formulas and UDF’s. Simply use the volatile function into a single cell on your spreadsheet and reference that cell from within other functions.
Lookup Functions
The Famous Vlookup(). Excel is very rich in lookup functions. These functions can be used to extract data from just about any table of data. The biggest mistake made by most, is the forcing of Excel to look in thousands, if not millions of cells superfluously. The other mistake is that the lookup functions are told to find an exact match. This means that Excel will need to check all cells until it finds an exact match. If possible, always use True for VLOOKUP and HLOOKUP. So, whenever possible, sort your data appropriately. Sorting the lookup columns is the single best way to speed up lookup functions. Another Bad practice is the double use of the Lookup Function nested within one of Excels Information functions. Like =if(isna(vlookup(cell ref,Range,2,false))=true, “Please check”, (vlookup(cell ref,Range,2,false)))
This is used to prevent the #N/A error from displaying when no match can be found. This forces Excel to use the VLOOKUP twice. As you can imagine, this doubles the number of Lookup functions used. The best approach is to live with the #N/A, or hide it via CONDITIONAL FORMATTING.
LAST WORDS
Lean to us e database functions. They are very easy to use and are often much faster than their Lookup & Reference counterpart.
Microsoft Tips
Organize your worksheets vertically. Use only one or two screens of columns, but as many rows as possible. A strict vertical scheme promotes a clearer flow of calculation.
When possible, a formula should refer only to the cells above it. As a result, your calculations should proceed strictly downward, from raw data at the top to final calculations at the bottom.
If your formulas require a large amount of raw data, you might want to move the data to a separate worksheet and link the data to the sheet containing the formulas.
Formulas should be as simple as possible to prevent any unnecessary calculations. If you use constants in a formula, calculate the constants before entering them into the formula, rather than having Microsoft Excel calculate them during each recalculation cycle.
Reduce, or eliminate, the use of data tables in your spreadsheet or set data table calculation to manual.
If you only need a few cells to be recalculated, replace the equal signs (=) of the cells you want to be recalculated. This is only an improvement if you are calculating a very small percentage of the formulas on your worksheet.
By changing formulas to manual from automatic
If my model has lot of formulas in the data sheets and working on the summary tab – then I will Keep my formula calculation option as “Manual”.
If you are doing calculation in one sheet Pls use Shift+F9 (to get refresh the formula in the active sheet).
F9 – to refresh the complete workbook.
1. arrange source data before linking to dashboard / report with macros and other aggregate functions
2. separate results into several charts & link list boxes to just one calculation
3. avoid volatile and array functions
VBA / Macros Optimization Tips
First. I find your site awesome.
Well I speedup my VBA code by setting
Application.ScreenUpdating to false
Application.EnableEvents to false and
Application.Calculation to xlCalculateManual
and then setting those values back to whatever they were before I made the changes. I do EnableEvents when I use a Event Driven actions and I know that I do not need them during those calculation/operations.
Two approaches.
a) Profile both worksheet calculations, and if necessary VBA code using the profilers downloadable here to identify and report on slower performing calculations and code.
http://ramblings.mcpher.com/Home/excelquirks/optimizationlink
b) in VBA, always abstract data from the worksheet and work on the abstracted object model.
http://ramblings.mcpher.com/Home/excelquirks/classeslink/data-manipulation-classes/howtocdataset
We design macros which we run across the many worksheets. If formulas are generated, we do final macros to save the formula results as numbers. This retains our worksheets as light.
Regards
David
1. Disable screen updating in VBA.
2. Set calculation to Manual, use Shift-F9 to calculate each sheet as needed. Is a pain, but I have found it is a major time saver on a couple of my largest files.
Recently, I’ve been busy with a project to emulate software for seeking secret messages in classical texts using EXCEL. I need to write hundreds of thousands of single letters each in a cell, and I’ve found it faster to operate internally VBA and finally write as a block in a declared range, rather than doing it via a loop writing individually each cell.
I haven’t measured times, but I would venture it’s a lot faster.
Not sure if this is what you are looking for – but here is what I do to speed up my excel workbook –
Along with all standard keyboard shortcuts – I have been creating a lot of Macros. I ran out of shortcut keys I can use with Ctrl – so now started using Ctrl+Shift to create my own shortcuts. (May be I don’t know any existing shortcut- and tried to reinvent the wheel for some of them)
I have Macros for – Green/Yellow/Pink Highlight – Merge + Wrap Text – Enter TB Link (Entering specific formula to cell) – Single Underline Cell
Just thought to share this as you asked for – considering all the entries I have seen from others on your website, I am just a newbie in the Excel World.
VBA: One powerful one is to use “Destination:” in your copying and pasting which bypasses the clipboard. Or if only values are wanted simply assign values.
So instead of:
Sheet1.Range(“A1:A100”).Copy
Sheet2.Range(“B1”).pasteSpecial
Application.CutCopyMode=False
‘Use:
Sheet1.Range(“A1:A100”).Copy Destination:=Sheet2.Range(“B1”)
If values only required ditch copy and simply assign values from one place to another:
Sheet2.Range(“B1:B200”).Value= Sheet1.Range(“A1:A100”).Value
Avoid loops like the plague while writing macros, unless absolutely necessary.
A tip which is well documented when searching for ways to improve performance when using VBA/Macros is to turn off screen updating, calculations and setting PivotTables to manual update.
Most of the procedures I create in VBA start with:
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
And will end with the following statements:
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
If PivotTables are involved then I include the following in procedure:
With PivotTable
.ManualUpdate = True
End With
And will end with the following statement
With PivotTable
.ManualUpdate = False
End With
Planning carefully before coding.
Passing the entire SQL query into the code, leaving no connection on sheets.
This is a very general tip, but when using VBA — AVOID LOOPS!
Use the “Find” and “Search” methods rather than looping through cells. Loops work quick when you are using less than 100, or sometimes less than 1000 cells — but start adding more and you will be in for a waiting game.
If you have VBA code that writes updates to the screen, this slows down the code (I/O is slow). If you have a lot of screen writes, the code can be painfully slow. You can turn off screen writes while your code is running and then do one massive screen write at the end of the macro. Up at the beginning of your code, maybe just after you declare variables, add the line “Application.ScreenUpdating = False”. At the end of your code, you need to turn screen writes back on so add the line “Application.ScreenUpdating = True” just before you exit the macro.
If you have a load of screen writes, the speed difference can be dramatic.
Hey Chandoo
VBA-speed
at the beginning of the macro
…
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
at the end of the macro
….
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Call Calculate
end sub
regards Stef@n
With Application.ScreenUpdating = False / True
With Application.Calculation = xl.CalculationManual
Using the statement with wherever is possible
and release memory when the objects variable are not used anymore
Everything Else
1. I list out the things required and will imagine the plan of my task.
2. I try to minimize the calculation for speedy calculation. So, I am trying to learn new formulas.
3. In each and every step, I consider about the others who use that excel. So that I can make the workbook user-friendly to others also.
I close MS Outlook when working on heavy files. Basically I exit all the programs that will eat into process speed. It helps to an extent.
Also, I try and minimize cross linking of files.
Power Pivot from Microsoft. This looks like it would solve the problem of large amounts of data.
http://office.microsoft.com/en-us/excel/powerpivot-for-microsoft-excel-2010-FX101961857.aspx
I wrote a blog article on my favorite tips here: http://www.plumsolutions.com.au/articles/excel-model-file-size-getting-out-hand
I haven’t done this myself, but a consultant we used sped up our dashboard by writing VBA code which “dumped” a lot of the back data after it was loaded. This greatly reduced the amount of data stored, thus reducing file size, thus sped up the dashboard.
1. Many database download from whatever system may include blank data occupying cells from the last row of data to the last possible row Excel can provide. So I would look at the data set and delete those rows (or columns, but I see more blank rows than blank columns).
2. Too many pivot tables: I’d ask the person who create multiple pivot table on the same workbook to see if there is a need to maintain those pivot tables. If the answer is
a) no need to maintain: I’d delete.
b) need to maintain but may not be in a pivot table. I’d convert pivot tables into just text/data (thereby removing the pivot function) table.
3. Try to reduce the number of worksheet. I found out that the size of a workbook (I can’t prove it but it’s my general observation) would expand if there are more worksheets.
Standardize, standardize, standardize! The more you are “boring” the quicker it will be to set up work with (and have others) use your formats!
Items to standardize:
– Font
– Color scheme
– Headers
-Colors for “input” (font color or fill color)
-tab color scheme (answers, data input, analytics)
– Color for “answer/solution”
– Lead with a recap page (easy and quick to find the solution)
-Configure to print (courtesy to others – if you need to print an answer – set up the parameters before sharing)
by using single sheet in work book & using alt,clt short key
Oddly enough, the best thing I have found to speed up Excel is to completely disconnect internet access for my computer. I don’t know why, but Excel is unbelievably slow when I am otherwise online, and speeds up immediately when I disconnect. I’d love it if someone could help me understand why this is the case.
Even though I’ve been using Excel for quite some time, I learn and love your site. You teach me the impossible. The simplest way I at least save data space is to save it in .xlsb format. I read somewhere that even a .xlsx is basically a number of zipped or compressed files that need to open and save. Not sure about that, but know the binary file is much smaller in size than the others. Not sure if macro enabled workbooks will save as binary. Thanks. Always look forward to what the next email will hold…scary sometimes. -Jim
I’m not an expert but try to keep the dashboard as much simple as possible.
I use access to have the main table and from that table we create different dashboards and reports and pivots to analyze data
I break any links to the spreadsheet that I am not using.
Try to avoid adding formatting over an area larger than you need, I’ve found that if you format a whole row, column or worksheet it can slow the workbook down and create large files
Dumping out as much unnecessary data as I possibly can, converting formulas to values whenever possible and making sure the empty space on each sheet is empty.
Also I’m using lots of pivot tables on my spreadsheets so I’m trying to use as few pivot caches as possible and trying to use external data sources for my PTs whenever possible (or deleting the original data once the PT is created).
Most of the time, to increase speed & size. what I do is
1) simply copy the used data cells to a new sheet, (by selecting from A1 to the end of the data cell),
2) if there are too much of borders decorated around more cells, then try replacing these borders with minimum dotted lines (just to highlight the difference)
3) Avoid using too many fonts in the sheet
4) Cut short complicated formulas or multiple linked formulas,
I have Liked based models. I try to make my links as small as possible. I try to put all the sheets in one file and interlink them so that they take less storage space and react much speedy in working.
Create a view in SQL and set a scheduled task to run to generate the view before you update the dashboard.
Do we get some SWAG for sharing??
Here are some tips I’ve collected, although they repeat some points they provide different viewpoints:
10 WAYS TO IMPROVE EXCEL PERFORMANCE
http://www.techrepublic.com/blog/10things/10-ways-to-improve-excel-performance/2842?tag=nl.e072
EXCEL 2010 PERFORMANCE: IMPROVING CALCULATION PERFORMANCE
http://msdn.microsoft.com/library/ff700515.aspx
CLEAN UP YOUR MACRO LIST
http://excelribbon.tips.net/T008037_Clean_Up_Your_Macro_List.html
OPTIMIZE MACROS – VBA CODE CLEANER
http://www.appspro.com/Utilities/CodeCleaner.htm
I recently happened to work on a report which has 1.5 lac rows of data in 16 columns. The requirement was that in the main report as soon as a change is made say for a dept or month the numbers should accordingly change. I tried most known formulas like Sumifs, Sumproduct, Vlookup, Index and Match. How ever the calculation time these formulae took was much more compared to one formulae that I felt was the fastest in terms of calculation. That was Getpivot data.
I basically used the “show all report filter” option in the Pivot options to generate summary data in around 500 tabs using the my base data. Then I used get pivot data formula in my report file. Though the file size was a bit huge still my formula get calculated almost instantaneously.
Also one strange thing I noticed in one other file of mine was that when I press Ctrl+end the last cell it stopped was in some 2 lac row or something, how ever the data was only in some 10k rows. I used clear all option from the last cell from where I have data to the last cell it went when I pressed Ctrl+end .By doing this my file size came down from 12 MB to some 600kb.. 🙂
Hope this helps someone.
Hi,
Great topic (as usual).
One thing I like to do is minimize links between workbooks. Instead of using live links to import data I like to use import and export sheets. These are identical sheets on the origin workbook (for export) and the receiving workbook (for import). Values are calculated in the origin workbook and pasted to the receiver as values only.
This gets rid of messy links and keeps spreadsheets smaller and tidier.
One thing to be careful of is that if one changes the other has to change so they stay identical.
Thank you again for your excellent material.
That’s my problem too I would love to hear what others say. For
me closing other spread sheets and unnecessary opened tasks in your PC helps.
– remove external data links – better to import a large data table – or use an SQL statement if possible.
– especially don’t use INDIRECT to anything external
1) Limit color use in Excel
2) Hide gridlines (with “View Gridlines” function) rather than color the cells white
3) Create smart Vlookup formulas (Arrange data in the lookup tab so the range is as small as possible – 3 columns vs. 20 columns)
4) Link multiple tabs using the same data to one data tab. Ie.. Dates, headers, etc…the links will eliminate having to update each tab.
5) Extract only the needed data from the database (10 columns of needed data vs. 40 columns available data.
6) If the Database report does not allow the user to choose what is exported, export the data, organized the needed data into a consolidated area (rows x columns), Copy & paste into a new tab and delete the original tab. Many people leave the original data in the file, which slows down the file and adds to the file size.
1) Don’t create different pivots from same data. Copy the old and slice into the new one.
2) Go to special –> Check Last known cell and delete unnecessary data and formulae.
3) Set Calculation option to manual. Do all the dirty work and finally make it automatic and go for a coffee break 🙂
4) Use excel tables as far as possible.
To speed up workbooks, the reduction of formulas within the workbooks should be the main aim.
If the data is being pulled from an external database into the worksheet:
– Do not start calculations on that data in the workbook which could have been done before, e.g. using formulas to split up the dates like mm-dd-yyyy, weekdays a.s.o..
– Reduce the amount of sources if possible and combine the data in one sheet
– Use name ranges
– Split up the workbooks for different purposes (Dashboard for CEO, Dashboard for CFO a.s.o..).
– Try to use only one format for importing (I prefer *.csv or *.txt)
– If you connect your Workbook directly to a SQL database, make sure the connection is high-speed
If the modeling is too complex, think of using a a (semi-) professional data ETL tool in between or use the additional add-ons like PALO or Pentaho available as open source to rise the power of multidimensional databases for your BI-tools.
This can and will save time in calculating for the necessary functions of the workbooks. Stay with KISS (Keep it simple, stupid).
I use name range for multiple pivots, basically the offset function, this not only speeds up my calculation but also reduces the size of the workbook.
More on Excel Optimization & Speeding up:
Read these articles too,
- Optimization & Speeding-up Tips for Excel Formulas
- Charting & Formatting Tips to Optimize & Speed up Excel
- Optimization Tips & Techniques for Excel VBA & Macros
- Excel Optimization tips submitted by Experts
- Excel Optimization tips submitted by our readers
Want to become better in Excel? Join Chandoo.org courses
Excel SchoolLearn Excel from basics to advanced level. Create awesome reports, dashboards & workbooks. |
VBA ClassesLearn VBA & Macros step-by-step. Build complex workbooks, automate boring tasks and do awesome stuff. |
How do you speed-up Excel? Share your tips
Between these 75 ideas & and previously written articles, we have covered a lot of optimization & speeding-up techniques. What are your favorite methods? How do you optimize & deal with sluggish workbooks? Please share your ideas & tips with us using comments.
337 Responses to “Excel formula showing as text instead of actual result – How to fix the problem”
sometimes i get strange "not working formula" error - "show formulas" is turned off, there is no single quote before formula and the cell is formated as number. but it still shows formula instead of result. it happened some time ago on xl2003, i haven't run into this problem on xl2007. bet only thing that helped in that situation was deliting the cell and retyping formula somewhere else (or copy formula only as full copy reporuduced the same error also in the new cell)
Formula Values did not get updated in my excel sheet. Found out in formula tab in calculations there is an option to set calculate value manually or automatic.
You got it right, man! Thanks a lot for sharing what you have discovered. 🙂
Exactly !!!
Had got me go bonkers !!!
Thanks Buddy.. Still works.
Why would any 1 convert the whole document to text GEEEEZZZZZZZZZZZ. The person is a Technical director and expects me to figure out why the formulas wont calculate ARRRRRRRRRRRRRGGGGGGGGGGGGG. THis Article helped wonderfully thanks Again for helping me to think properly again.
Thanx a lot!! Works like a charm!!
Awesome! Thanks so much.
LIKE!
Thanks a lot Jimmy
Great Jimmy, i would have never found this myself. Thanks.
Thanks a lot. You saved me more than a few hours of aggravation with that little tip.
For those really new to Excel, its is Formulas> Calculation Options> Automatic
Thanks Buddy I was struggling for more than an hour, your suggestion saved me time. Thank you very much
Excellent thanks. I had the cell preset as text. Setting it to general didn't sort it at first, so re-entered the formula and problem solved. Thanks again.
I had the same thing. It was a lengthy formula so I copied it to a notepad page, changed the cell to "general" and then pasted it back in so I didn't have to retype the whole thing.
Same here, thanks for the additional info - I had tried everything else, but with no change!
If any of the above is not working
1) Make sure there are no circular references which are displayed at the bottom of the spreadsheet. This does not always work though.
2) Select all tabs in your workbook, go Home-->Find and Select-->Replace with (or hit Ctrl+H) and type “=” in both fields, hit Replace all.
Step 2 SAVED us a lot of time. Not sure why it works but it does work.
This was our last resort
If the cell is converted from Text to any other condition (e.g. General, Number, Percent), an easy (for me) alternative is: click at the end of the formula in the formula bar and hit enter.
The cell then "accepts it's new format", and calculates the formula correctly.
I am only having issues with one spreadsheet. after copying and pasting a chart into another sheet and eliminating past month's data. Formulas stopped working. I was only getting the formula in the space after hitting enter. I followed the directions here and got everything set to "general", redid my formula, hit enter. and now it always says 0 instead. I went into a new spreadsheet and tried out an example of what I have been trying to do and the equation worked perfectly. So I am not entering the formula incorrectly. What else could it be?
Hi kortnie , i m facing the same issue. What solution did you finally get?
I m watching allover the web and your post is the only resemblance to my problem.
thank you
Thanks a lot, your tip worked from me.
Worked like a charm, thanks for the info !! I needed a leading 0 for serial numbers but all of my cells were formatted for text not custom number so formula was not working.
Nonf of your suggestions work on my Excel 10. So what really works all the time?
When the problem is with cells formatted as text, I do a find and replace "=" with "=" (without the quotes).
mikii, I think your problem might have been related to an add-in.
replacing = with = actually worked out for me
~Thanks Sebastien.
THANKYOU, saved me entering each cell and hitting enter for a range of over 1000 lines - life saver
Best tip ever - been sitting with this problem a number of times, and none of the more "rational" solutions worked.
Now I am in a happy mood which will last for at least a day!
I tried the other suggestions. Replacing = with = helped. I feel relieved! Thanks.
WOW! Who'd have thunk it! Replacing = with = WORKED!
Now, if we could figure out why!?! an entire spreadsheet was this way after importing data from CRM...
Thanks for sharing, made my day!
@Greg
The default character set in Windows has 4 characters that have twin parallel horizontal lines that look like = signs
Only one of these is the = sign
The others will simply be treated as text
Simply select one of the bad ones
Then do a Find & Replace for a normal = sign
We get the same problem in the WordPress Posts here at Chandoo.org where from time to time the " characters are replaced by a similar looking set of characters rendering the formula useless
The global find & replace (Ctrl+ H) of = with = is truly BRILLIANT!!!
Thanks to Sebastian and Valeria for this!
the problem happens to me when I concatenate multiple source cells into long formulas to build complicated lookup-up tables and other worksheets where it's much easier to build dozens of complex formulas as text strings and then copy their values into the working sheet; doing the global search & replace of = for = [char(61)] "activates" all the formulas, which otherwise sit there in perfect syntax but recognized only as 'constants' by Excel. My old method was to "F2" & ENTER in every cell, one at a time; Ugh!
Thanks, again!!
Amazing. I was going absolutely NUTS and then - replacing = with = does the trick. Thanks!!!!! 🙂
Agreed, not sure how or why it works but it sure does!! Thanks!
Change the number format and don't panic.
tnx, Sebastien. It could explain such error. If it'll happen again, I'll check what add-ins are running 😉
I have experienced the following weird behavior with SUM function...
Imagine you have the following values on the column A
1
1
And you use the formula =SUM(A1:A2)
And the result you get is 1 instead of 2.
Then you use the following formula: =A1+A2 and you get 2
I figured out that one of the cells contained the number stored as text.
I also figured out that the SUM function doesn't convert such numbers to text as the plain formula do.
Try it by yourself.
I wrote an article that summarizes the problems of the Excel Sum formula, it may help you to avoid those errors and more in the future.
http://www.excel-spreadsheet-authors.com/excel-sum-problems.html/
This happens to me when I've exported data and the format gets messed up. It thinks it's text, but just changing the cell format doesn't fix it. I have to go to an unformatted cell and paste special format to "reset" the cell. After that, the formula will work properly.
I have the problem where I imported data from a tab delimited text file into Open Office Calc, and then saved the file and opened it up in Excel 2007. Some cells read as numbers and will sum up, but others don't read as numbers and I can't get a sum for them. The computer thinks they are text. I've tried copy/paste-special the format to number but that didn't fix it. Any suggestions?
@Glenn
In a spare cell type the value 1 then copy the cell
Select the columns with the numbers you want to check
Paste Special
Multiply Values
Apply
How, Hui, that was spot-on. Thanks a lot!!!! (You're up pretty early, aren't you?)
Thank you so much for this fix. My issue was cells formatted as text.
These tips really save me a HUGE amount of time. Thanks for sharing!!
THANK YOU.. I was going crazy. Half my formulas were working, the other half were showing the formula instead of the value. Silly excel.
A related problem, the formula total does not update when a variable is changed.
Problem: In Excel Options, Under Formulas, "Calculation options" has been turned to "Manual". Switch back to "Automaticl".
Thank you that was driving me mad!
This is the Best answer and solution, thank you
I imported an old XLS into Excel 2007 and the default condition was to show the formulas.
WHY did MS make this SO OBSCURE ?????
GK
2.00 0.00 =K1685*0/100 =K1685+M1685-L1685
this formula not working in my excell sheet model 2003
Thank you so much!
I have been working on this practice assignment in Excel for an hour & just tried all the steps you suggested and it is STILL showing the formula in the cell and not the Value!!! Ready to pull my hair out...I have never used Excel & this class is a REQUIREMENT! No idea what is going on!!!!!@
@Chad
Is it just one cell or all cells?
If it is all cells try Ctrl ~
Delete the cell contents and reenter the formula
Make sure that you re-type the quote characters " if you have copied the formula off the net
Make sure the first character is a = or a + character and not anything else including a space
+1
My formulas are working now. My issue was the cell format being text instead of general.
I have a web application which allows user to donwload reports in excel 2003 format and 2007 format.
Now when i test the applciation in my system which has office 2010, and open excel in 2003/2007 format, initially those cells which has formulas are not displayed.
If i click the protection level alert, then they will be displayed. How to avoid this. Can any one suggest.
Thank you! I was giving up on googling my stupid stupid excel problem (my columns had been set as text format and I didn't realize that...and I didn't know that formulas won't work on text formatted cells)...until this very straight forward post caught my eye and salvaged my sanity. Seriously, THANK YOU.
I have seen this problem before. Never fixed it, always inserted a new column copied from another location within the same sheet.
However this time I changed the format of the cell to "General" and reentered the formula. That worked.
Previously I would change the format to a number, which has never worked for me.
Thanks!! Simple problem but like a needle in a haystack if you don't know what it is. Cheers.
What if I need it to be text? For example:
2-6
If I format anything other than text it tries to calculate 2 minus 6 or returns gibberish.
I am currently adding ' before everything so I can keep it in general format and still have excel treat it as text. This is a tedious work around though.
@Matt
Enter it as text ie: '2-6 or Space 2-6
What a relief! Thanks so much for the help!
Roger C - you are my hero thank-you!!! I've had 2 giant spreadies which I thought needed to be rebuilt as the formuals had totally stopped. You have saved my day. Thanks
Had same problem with Excel 2007, none of the issues above. It was resolved when I manually added the ' (text indicator), and then removed it again. Weird.
Thank you! I thought I was going insane. Very helpful info with clear, concise instructions.
Hi, I'm having a problem with my excel. When I press the function button a list appears and then I select a formula. For example, if I choose the IF function it pops up and usualy every argument has its own edit box where you tipe in the argument for logical test, true and false. Currently there is only one box to tipe in everything. Is there maybe a setting that I need to check/uncheck. I hope you understand my problem
Your "fix" was right on, my friend. Thanks.
My problem is the formula works fine at the beginning but the cell just shows up the formula after any edits made. I've tried any possible ways to find the reason or solve the problem but no luck.
Any clue or suggenstion? Appreciated.
I have a similar issue, with different symptoms.
When I add a calculation ... nothing happens, it just displays as 0.
I use this spreadsheet daily and this problem just seemed to come out of nowhere. An hour ago it was fine. What have I done?!
I've checked everything: it's set to auto-calculate, the cell is set to 'accounting'.
Help!
@Mieka
Do you have any messages in lower left corner of Excel Screen like
Calculation
or
Circular Reference Error
Anything else?
Came across your thread while looking for a solution to a similar problem - summations adding up to/displaying as 0
...yes, i do see a circular reference error in the lower left corner of the Excel screen.
Any suggestions?
Wow! Thanks very much for your quick response! I will look at that for next time.
For now, I tried restarting my computer, and lo and behold, everything is back to normal!
Thanks very very much for replying! 🙂
Thanks a lot . Problem Solved 🙂
I need this to be english. 1/2 the sheet looks like this!Help!
jpSaí¾¤°S!Õ–¡
@Jenny
It looks like you've impoorted a Word or other binary document into Excel or vise-versa.
Excel can only import Excel and a small list of other files.
Make sure you select the correct file type.
Also Excel 97-03 cannot import Excel 2007/10 files without a convertor, available from Microsoft.com
Thank you for posting this seemingly "basic" excel problem! This problem drove me mad!
If the formula is an equation (=) to another cell (for example to repeat a value in multiple work sheets) and that this source cell is a merge cell, it will display unpredictable results depending on the cell format. These include displaying the formula instead, or a zero, or a #VALUE!.
To fix this, either un-merge the source cell so your formula contains a single cell reference not a range, or simply Define a Name for the source cell and use this Name in the formula rather than the range.
@Chandoo: I'm using an IF formula =ROUND(IF(AG3=0,0,((AI3/AF3)*100)),2).
Cell AG3 has =IF(ISERROR(Query_Actual!K3/'No of proposals_Actual'!K3),"0",Query_Actual!K3/'No of proposals_Actual'!K3) formula in it.
However, even if AG3 is 0, excel reads it as 'FALSE' and does the division!
One more thing. The same formula works for other cells with only a 0 in it. But when the above said formula (in AG3) is referred it does not work.
I'm sure this is some excel error because sometime back I had used the same formula and it used to work perfectly. It was in Excel 2007 and now I'm using 2010.
Please help me with this.
Thanks much.
@Benoy
In AG3 change ”0" to 0
.
of course in 2010 you could just use the following in G3:
=IFERROR(Query_Actual!K3/No_of_proposals_Actual!K3,0)
@Hui: Thanks a bunch. I should have realized "0" is a text and it should be 0 only..!
The new IFERROR is too good. Otherwise the formula's ran pages!!
Thanks again.
I am using a large spread sheet converted from an older excel program. I am calculating in 3 columns. Sometimes one of the two columns will have the same calculation in it and I noticed that the answer is different. I checked the cell formulas and they are the same. I have tried to cut and paste and it won't over ride what is in the cell and still calculates incorrectly. I was just upgraded to 2010 so am struggleing with the new options. What could this be? I checked and "set percision as displayed is NOT checked"
@Carolyn
Is calculation set to Automatic not Manual?
Thanks! The General / Text thing worked for me!!!
Maybe someone can help. I have the below formula
=IF(G4=A3,B3,IF(G4=A4,B4,IF(G4=A5,B5,IF(G4=A6,B6,IF(G4=A7,B7,IF(G4=A8,B8))))))
However, I also have a formula in cell in G4 which reads I4/F4. My result is showing False. If I just type in the result, and not have the I4/F4 formula it does the calculation. Hope I explained that well. I am confusing my self. Thanks for your help.
Mike,
Your inner most IF(G4=A8,B8) has only 2 arguments. If this is the intended purpose, then sorry I don't know. Otherwise, its possibly because when G4 doesn't equal A8, it returns FALSE. I guess it does the calculation (=A8) when you type in the result because the value type typed in G4 equals A8?
How the problem solved.
Problem: When I put the formula A1+1=2 ( If A1=1), But does not responding.It shows A1+1=1 instead of 2.
Solution :
Go to Formulas Where shows a calculate option and click on automatic as a first option.
Regards
Ajit Kumar
Right Click> Paste Special > Values
@Ajit
Your formula should be
=A1+1
make sure A1 has 1 not '1 or space 1
Just to add to the pile, I have a list of codes, could be numeric, could be text.
I want to do a lookup on them and use the TEXT(A1,0) function to make them all text.
Most rows show me the correct value, some show me values that are totally unrelated.
i.e.:
A1 = '5-1800
A2 = '5-1900
Text(A1,0), gives me "5-1800"
Text(A2,0), gives me "122"
What am I doing wrong?
@Okkitrooi
.
Excel is interpreting these as dates
ie: '5-1900 is Tue 1 May 1900
it isn't doing that to '5-1800 as Excel dates start at 1 Jan 1900
Dates entered as text prior to 1/1/1900 aren't interpreted as dates
.
In regards to doing a lookup you can use a Match() function to lookup Text and so should be able to work around this.
And all of a sudden the world was in balance again!!!
Thank you sooooo much this would have taken me ages to figure out!
Thank you so much for this fix! I was getting really frustrated that my formulas we showing up instead of the value (obviously I couldn't figure out why it worked in some cells an not others)!
You are a life saver!
Thank you so much, your site is now bookmarked. I spent three days and believe me I Wasn't Looking Forward to telling my boss that I couldn't get my spreadsheet to work properly. You saved my butt. THANK YOU! THANK YOU!
this is probably a stupid question, but i'm new to working with excel 2010.
what if i WANT the spreadsheet to show ONLY the formulas and not the answers? how do i do that and save it, and then send it to someone? i tried a couple of times, and every time i open the sheet back up, it doesn't show the formulas, only the answers. i only want the formulas to show.
thanks.
@Jay
I use Excel 2010 and when I show Formulas using Ctrl ~ and save a file
it opens with formulas showing as formulas
.
Can you instruct them to use Ctrl ~ after they open it to toggle between formulas and values
In Excel 2007 my formulas are suddenly copying as values, not as appropriately offset-adjusted formulas. In other words, copying is now behaving just as if I if I had done the corresponding "Paste Special" and selected "Values", but I am using simple "Copy" + "Paste" (or, with the same wrong results, CTRL+c and CTRL+v).
This failure now occurs in entirely new spreadsheets (new workbooks, not just new worksheets), and not just in the worksheets where formula pasting was previously working normally.
My next move is to OOCalc and/or to other machines, but I would appreciate any help. (I'm a programmer with 40+ years' experience, and this is going to bug me [maybe that's where the term "bug" comes from?] till I see it fixed.)
Thanks in advance.
Supplemental re "In Excel 2007 my formulas are suddenly copying as values..."
I just noticed that if I do try to use “Paste Special”, now all the numerous usual choices are missing and I get the entirely different window on which the only choices are:
"HTML"
"Unicode Text"
"Text"
--or-- (when I just tried that again), just
"Unicode Text"
"Text"
GROWL... I hope someone will point out that I'm just doing something entirely stupid (rather than discovering that my machine is now haunted by a particularly devious virus).
Supplemental #2 re “In Excel 2007 my formulas are suddenly copying as values…”.
Copy just started working probperly in all my Excel instances (as far as I can see, at least), AND I MADE NO CHANGES ANYWHERE IN ANY OF THOSE INSTANCES, INCLUDING NOT IN "EXCEL OPTIONS".
Somehow this is much more disturbing than a problem that persists consistently but is ultimately explained, even if that takes a long time.
Maybe it was my mentioning OOCalc that did it.
@Ted
I'd be checking for
- Viruses
- Check the Excel Installation is sound and update if required
- Check somebody hasn't played a prank on you
Hey guys. How do you remove quotations when a function within a function when it returns a text. Here is my formula, =OFFSET(address(2, 6, 4, 1), MATCH(I5, F3:F17, 0), 1). The 'address(2, 6, 4, 1) returns an "F2" in quotations and thus the whole formula will not calculate. I would really appreciate any help!
Brownies
I think the problem is that Address() returns a String not an Address
Simply adding an Indirect to your formula, fixed it.
=OFFSET(INDIRECT(ADDRESS(2, 6, 4, 1)), MATCH(I5, F3:F17, 0), 1)
cells formatted as text - thanks! i was about to jump out my basement window, could've hurt my shoulder or something...
@Hui...
Wow, thank you so much. I literally worked on it for hours and couldn't figure it out. Thanks again!
Excel 97-2003 to Excel 2010 migration problem:
Hi all !
I'm sure that many of you has allready experienced the following problem :
I have developped an application in Excel 97-2003 under windows XP using workbooks with a lot of formulas linking many of the workbooks.
I'm now trying to migrate the application, thus the workbooks from Excel 97-2003 to Excel 2010 under windows 7.
The procedure I'm using is to load the workbooks under Excel 2010 and "save as" them using the format option Excel 97-2003. But when I open the workbooks with the biggest number of formulas and links, I get the following message box and excel terminetes when I click on the single button [Close the Program] appearing on the window:
"Microsoft Excel has stoped running
A problem caused the stop of the program.
Windows will close this program an tell you if a solution is avialable"
Do some of you have an idea how to solve my problem?
Thanks in advance for your suggestions.
Gaston.
Having some issues with my formulas--you know when you hit F2 to edit and excel shows the nice colored boxes around the cells used in the formula? You can generally click and drag them to change the range used in the formula; however, my excel application quit allowing that and now only shows the colored boxes but without any nibs (i.e. a solid box instead of a box with little squares). Any help would be welcome! Using Excel 2007 and got nothing with google searching.
@DH... I think you have accidentally disabled "fill handle" functionality. To re-enable it,
1. Goto Excel Options (from office button or file menu)
2. Goto Advanced
3. From Editing Options, check "Enable fill handle" option.
4. Click ok.
I am using a quotient formula. I would like the result to display one (1) when the answer is zero (0).
Thank you Chandoo! That was killing me. Thank you for your response--you're making me an even bigger fan than I was already.
@Cowboy Joe
You can apply a custom Number Format like:
#,##0;[Red]-#,##0;(1)
Note: that the cell still contains zero, but it displays (1)
Whenever I use a formula which includes a paste linked cell, I get an error message that says #VALUE. I cant seem to carry out any sort of formatting on the paste linked cells or the original file from which I copied the cells eg. changing the currency from $ to euro etc. Help please! and thanks in advance 🙂
@Anna
I can't replicate this
Does the cell which is linked to have an #VALUE error ?
@Hui
The #VALUE error comes up when I use the paste linked cell in a formula.Example-I paste link the unit cost of an item from one Excel sheet to another and then multiply the paste linked cell with say..the number of items being sold in order to find the total cost. It is the cell where the total cost should have been shown that displays the #VALUE error.
@Anna
I've never encountered this
Can you email me the file ?
Email is at bottom of page after you click my name
Hi again,
Got an interesting question.
In a nut shell. I've extracted data from the web into a worksheet. I had to sort the data using formulas to extract things like the "mill" out of "125 mill" among others because it would be infeasible to manually do so. Then, I used a rather large offset function to extract certain type of values from my modified data like "Total Sales", "Yield", etc. That works fine, but when I create a chart from the offset function values nothing comes up. Is it at all possible to extract data into a chart from two separate formulas?
Side note: There is all kinds of different data formats like percent, dollars, and regular numbers.
Greatly appreciate any help.
@Brownies
Often numbers copied and pasted from web sites or linked to can appear as numbers but are in fact text
Clean up all your numbers by using Trim and Search/Replace Spaces for nothing
Then Copy a cell with 1.0 in it and Paste Special Mulitply values to all the number ranges
This converts all the cells to numbers
Apply a consistent number format to the cells as well
If that doesn't help can you post or email your data to me
Thank you so much!!!! I was having such a hard time with formulas showing as text. Now I know how to fix it.
Wow thank you so much Hui. It amazes me how the simplest things fixes seemingly complicated problems.
Can i send my excel file to you for correction. if yes, please advise me via my email id.
@Ardneran
Click my Name, email is at bottom of page
Hi There I consider myself a pretty good excel user and I have come across an issue that I can't figure out. I am linking 2 different files (both 2007) and the workbook that I am trying to establish a link does not recognize the "linkee" worksheet as something to link to. sorry, hard to explain. I simply want to have the contents of one cell link to another cell in a different work book. However, when I add the "+" and go to link, it doesn't recognize the cell as anything it can link to. I have tried changing all the cell format, the "allow link updates" in the security centre and have tried linking different cells and workbooks. Everything works as expected except for one source spreadsheet so that tells me its something to do with that spreadsheet. Any advice would be great.
@Pamela
Have both workbooks open before you start your formula
When you start to construct your formula eg: =Sum(
At this point select View, Switch Workbooks
Select the other workbook and navigate to the Sheet & Range
Complete your formula.
Oh my goodness -- thank you! This was driving me *insane*.
But then...MS Office quirks often do, don't they?
Thanks again!
I m not able to in use formula =A1/A2 in excel 2010 it shows #VALUE! error. i have checked cell formats they are in number formats. all other math operators(*,+,_) are working fine, problem only with / operator. an advice will be of great help. thanks in advance.
@AK... What values are there in A1 and A2?
i am using excel 2010, copy the formula and returning value which is fine.
however, when i press F2 (just try to edit the formula without changing anything)
it shows the formula instead of 'value'
i tried 'ctrl' '~', i tried 'show formula' --> doesn't work
i tried replace "=" with "=", it's a temporary fixed, but still want to wonder why...
anyone can help?
@Chandoo.. any number say A1=25 and in A2=5. problem occures only with / operator others say +, - and * are working as expected.
Regards,
AK
@AK
Excel will happily perform maths on 2 numbers entered as text, even though it technically probably shouldn't.
.
2 Questions?
Are you using Excel or Open Office or something else?
If Excel what version ?
.
Can you email me the file ?
Email add is at bottom of page after click on Hui...
Hi, need help. I put in a password using the general options while saving my work sheet and after that i've noticed that the formulas on my worksheet is no longer working, I have a formula to compute average handling time and the sum and the average is not computing and that i need to manuall click on the function menu to compute each and is time consuming. I am using excel 2003. I am not sure if putting a password affected my work sheet or what, but I need to it to be back up and working the same way as it was. Thanks.
@catherine
The passwords should have no impact on the calculation of formula
You may want to check if Calculation is set to Automatic or Manual
Goto the Formula Toolbar and select the Calculation Tab, change to Automatic
I am copying the formula from C17 to D17, as per excel rule it should update and it is updating the formula but showing the value of C17 instead of D17 unleass I press F2 and Enter key it is not updating the value.
Can anybody help me out with this problem , why excel is behaving like this?
@Hasan.. I think your formula calculation mode is set to Manual. You can change this to "Automatic" from Formula ribbon.
hi
i need a if condition formula in Excel Sheet thanks
I think I have the reverse problem to the above example. Sometimes I will be typing a simple sum eg +2593/3 and although it return the correct answer 864.33 however the formula is no longer in the the cell so I can not adjust it. Can you help?
Hi,
I have a formula =if('a'!a1='b'!a1;"matching","notmatching") and i have dragged it over 10,000 rows....now each of the rows will show "matching" or " not matcing" based on the comparison.
If i have "not matching" in 8976th row....how would i know?
Using Ctrl+f does not help.
THANK YOU! I really like you! You saved me! HUZZAH!!!!
Am trying to use average function....but sumtimes it doesnt work...merging cells and trying to apply it....is it the reason?....plz sort it out
I'm facing a issue- when I open a particular file I only see a #Value error (cells in this file are linked to other files which are not on my system) . However, when my peers open the file they see the calculated values (without error). I'm using 2010, my peers are using 2003/2007. If I set the calculation option to manual, I too am able to see the value correctly (however, others do not need to do this). What's different in 2010 thats causing this? Can this be fixed?
Appreciate any help!
Thanks!
was set to Text.... never had that problem happen before.... Thanks!!!
@Kunal
Do you have the correct access to the network location where the file is stored ?
Is it mapped correctly as say M: on their PC's but N: on yours ?
@Hui, We (me and my colleagues) do not have access to the network location of the linked files- We are an Analytics team while the file comes from Finance, there's no way we can access those systems :). Still, I do used to see the proper values in the file earlier.
If I open the old files now it shows an error, however, I've used these files earlier without any issues.
Also tried opening the files in other 'MSO 2010' systems, it does show an error, while anyone using 2003/2007 doesn't see these errors.
Another thing, this started happening only in the last few days. All was working fine until, say, Nov end. I've checked the 'trust center' settings and everything looks normal. I wonder if any recent update to Office 2010 is causing this.
Thanks,
Kunal
@Kunal
I would still check to see if maybe you used to have a network access to accounts and now you don't or it has changed from Read Only to No Access or something similar.
There is not any backward compatibility issues like this, that I am aware of within 2007/10.
@Hui, thanks for your time on this!
I'm sure about the network access- we certainly don't have access to it, unfortunately though! 🙂 (I've worked on these reports 'offline' quite a few number of times)
Backward compatibility issues- Yeah, I too doubt that- just that I'm unable to find another reasoning, so, it came to my mind.
I would post back if I find out something!
I have export my excel file from google docs when i open it formullas not working
instead of a formula it shows Sheet=Sheet1Sheet5Sheet9Sheet6Sheet7Sheet0Sheet+Sheet2Sheet5Sheet1Sheet2Sheet6Sheet
what to do please help me
Thanks
Many thanks for your website--brilliant!
Hui,
I've tried all your solutions to my formula showing problem with no success.
I'm running 2003 and it is simple columns adding the numbers in the columns.
Any chance I could send you my sheet and you might be able to sort it. There are only 7 columns.
Thanks and great site. I too have bookmarked it.
Nat
@Nat
Click on my name Hui to the left
My email is at the bottom of the page
Thanks for the help! This saved me some time digging around the subpar Microsoft help pages.
I have a 2010 spreadsheet that has 3 nested formulas and macros. The cells return a zero value on my pc. I tried the same file (no modifications)on four other pc's in the office and it works fine. I un-installed and re-installed excel and the problem persists. I compared most of the settings in 'options' with another pc and they look similar. I am really worried my reports are showing wrong figures!
@DXB
Check if you have Calculation Turned to Automatic
Goto Formulas, Calculation Options Tab
my auto sum is screwed up, when i total a column then cut and past the total cell to the next column, the sum is wrong, it is the sum from the cell i copied. Example: cell a11's formula is =sum(a1:a10) and the total is 20. In column b, the 10 numbers total 25. If copy a11 and paste into b11, the total will show 20 (a11's sum) even though the formula shows =sum(b1:b10). its driving me crazy. please help
@Gary...
Welcome to chandoo.org and thanks for your comments.
Can you check if your formula calculations are on "manual" mode? If so, change them to "Automatic". You can do this from Formula ribbon.
great stuff
I've been dinged by having my cells formatted as text more than i care to admit. Thanks for the helpful post.
Hi All,
I was having issues with a Summary Report designed to compile individual summaries from a variety of scopes in one large project. I linked all the individual summary sheets into the Summary Report, placing each as its own table onto one sheet. Everything looked great until I tried to tally all the individual costing into an overall project total - only one of the sheets' values totalled correctly!!
After much frustration and trying all of the examples listed here, I discovered a simple proble - the import turned all of the "$" signs from a text formatted condition into an actual cell entry. This cancelled out the number when I tried to SUM the values. I performed a simple "Find/Replace" command with nothing entered into the "Replace" field to get rid of all these symbols and all of my calculations work as they should.
I didn't see this posted anywhere else on here, so thought I would add my discovery to this wealth of information.
Regards
Life saver. Thanks.
A cell conatains date as mm/dd/yy and i need to change its format to mm/dd/yyyy. after changing the format the cell does not show the effect untill i press F2 and Enter key. i am using office 2010. can someone please help on this. i want the format change to take effect immediately.
Thanks
@Praveen
When you say format I assume you mean the Custom Cell Format
accessed by selecting cell and Ctrl 1 or Right Click Format Cells?
Also check that you have calculation set to Automatic
@Hui... yes you are right it is the cutom format set for the cell. calculation is set to automatic as well. i have tried almost all possible options, which could be set in Excel 2010 but nothing seems to be working.
i really really need some solution!!!
Can you email me the file?
sure i can, whats your email id
Click on my name
its at the bottom of the page
Start > Control Panel > Region and Language > Additional Settings > Change List Seperator Value to , instead of /. Click Apply > OK
@Hui
Thanks, i have sent the mail with excel sheet
I have responded
Thanks Hui, I just finished trying few things related to that and it has solved my problems to a very large extent.
Thanks a lot!! God Bless you...
Thank you so much ! This helped me quickly 😉
Hey, just wanted to let you know this fixed my problem, never noticed the text drop down. Thanks awesome website for help.
Cell format was text, changed to General... Thank you very much!
Hi, i'm new new to exel and have an issue with some cells! I have a traffic light system were a formula is added to chage the colour depending on the date on the cell.The formula works great on 95% of the cells bar a few! Bit of a head scratcher ! I've checked the cell format and it's all ok ,I've pasted the formula on the cell but it does not change colour with the formula like the rest of the cells do.
So any help would be great.
Many thanks
These solutions don't work for my spreadsheet.
changing format to general does nothing - still no calculation.
there are no apostrophe's or spaces in the cells.
When I enter =2+2, I only get the text, and not 4.
Looks like I'll have to start from scratch....
Hi Guys,
I need Help for excel 2010. I am importing some data from our official website. It is daily analysis sheet of our store. its shows all figures are in $ sign. by the way this data is get update in every 1 min as our store is busy. Now when i import data on my excel sheet it shows figure with $ sign ( $ 421.20). but when I want to add two cell data (=A1+A2) it shows "# value Error". As i tried to change the format from "General" to "Currency" but as data get updated every 1 min that format get reset.... this problem just occurs in my laptop. on my office desktop (Office 2010) its work fine. on my colleague's laptop (Office 2003) its works fine.
please help me .
Thank you, very helpful
U So Awesome! thanks
I'm having a problem where my formula is showing a result of only 0 or 1, whereas the arguments box is showing the correct value. The formula is =SUM(--(K2:K500<J2:J500)), essentially returning a value of the number of times that the J cell is greater than the K cell. I've ensured my formatting is set to General. Any insight to this?
@Nate
Your formula is close
It should be:
=SUM(- -(K2: K500 < J2: J500))Ctrl Shift EnterNote the 2 - signs
It is also an Array Formula so must be entered with Ctrl Shift Enter
The CTRL + SHIFT + ENTER is what did it. Wish I had known it was that easy earlier. Thank you sir.
I am using Excel 2010, when I first turn on my pc, the copy and paste functions to copy formulas and what not work fine. After working in it for the next few minutes the only thing it will copy is text. I have tried everything. What do I select to ensure I can still copy and paste all of the data with the formulas included?
Everything on the sheet is fine. The next order of business is create a new sheet with formulas. Sure ok no problem; I double-click on the column divisions to make everything fit and look pretty.
My dilemma:
Before turning any work, I like to look at everything in preview. When in preview mode, not only is the formula showing but the file name appears before and after the formula. Ugg what happened, I have never seen these included in the formula and cannot figure out how to reduce it to the formula alone and hence, fit on the sheet. Any suggestions?
formula in non preview mode:
=COUNTIFS(Table1[Bedrooms],5,Table1[Baths],">=4")
formula in preview mode (of course I cannot copy it...)
=COUNTIFS('Excel_LA8-31myname.xlsx'!Table1[Bedrooms],5,Table1[Baths],">=4,'Excel_LA8-31myname.xlsx'!)
THANK YOU very much! 🙂
Got a doozy of excel issue: (Linking value from FileA to FileB)
*1. Going to cell C4 in FileA and typing: =
*2. Switching to FileB and selecting G4: (formula bar now reads =G4)
Here's the doozy
upon hitting enter - the Value shows up in FileB C4
(overwriting C4 with value from G4)
Note: 100% single instance of excel, not 2 running.
Never seen this before - any ideas?
Thank you for this info, it is the first result from my google search. Thanks!
Superb! fixed it than you
I have a workbook in which one sheet importes data from msaccess queries and another sheet has formulas and lookups to the first sheet. everything works perfectly untill i go to Data tab and give refresh to the get fresh data from access. The formulas in the second sheet shows #NA and i have to manually enter f2 on each cell with formula to recalculate the results.. Any solution for this?
THANK YOU THANK YOU THANK YOU. I have always recreated the entire worksheet.
This is the very best solution.
Thank you again
I knew my problem was related to a Text vs. Number issue but still could not figure it out.
I was trying to combine two cells with text into one using a simple =A1&B1 formula. I did not realise that also the cells the formulas was computing had to be general (they were defined as text). Once this was corrected the formula worked.
Thanks for leading me on to the solution.
Hi, might be a simple sollution but one of my users has a cell and they are trying to add up from that cell to give a total for euros so its in number format.
The formula used is =B8+B9:B19 and when I hit enter it gives me #VALUE!
Any ideas on this would be greatly appreciated.
Thanks
@Dave
You can't add a range like that
It possibly should be:
=B8+Sum(B9:B19)Aaahhh it makes sense,
As I am dragging the cells with my mouse to highlite them it enters in the rest when I put in =B8+ ----->Then it puts the rest in but I was not sure that was the correct formula!
Thanks Soso sooooo so much Hui!
Our department are all very happy now and all because of you!
Much appreciated you are a true gentleman!
:-)))
Hi, I am having a similar problem re formulas entering #value! error after copying data from an HTML page and converting text to columns.
I notice that the columns containing the numbers have "space" before and after the number and only if I manually go into each of the cells and remove this space then will it be recognised as a number.
Also tried to do find & replace to remove the space, but wasnt recognised. Any ideas on the best way to solve this problem.
Also, what would be the best way to convert an HTML table to Excel?
Thanks
=INDEX(A2:G1080,MATCH(J2&L2&J3&L3,A2:A1080&B2:B1080&C2:C1080&D2:D1080,0),6)
not getting the oputput because of enter command is not working
is their a way to use { } also to execute including any other key with enter button
plz help
wow. you are amazing. seriously. Look in the mirror and tell yourself today. 🙂
I have experienced the following weird behavior with SUM function…
Imagine you have the following values on the column A
1
1
1
And you use the formula =AUTOSUM(A1:A5)
And the result you get is 1 instead of 3.
when i change the values in between there is no change in sum value.
First, change the formula to SUM(A1:A5), there is no such formula as AUTOSUM()
Second ,check if the values are actual just 1 or 1 with some extra spaces in front or end
Finally, make sure your formula calculation mode is set to automatic. You can do this from Formula ribbon
Thank you! Formatted as text - arrrrrgggghhhhh!
Great tip, thanks very much.
I work at a business doing payroll and my excel spreadsheet will not sum up the selected cells. I changed the result cell from text to general and then when i press enter the formula is shown not the result. When i go back and format cells again it is back on text. I don't know if you have covered this already but i would really appreciate some help.
Hi: I have copied and pasted a list of stock dividends from a website. The amount of dividends column shows its format as 'text', which I subsequently changed to 'General'. All I want to do is convert the column to a number - I have done this so many times before in other spreadsheets by using "Value(Cell Ref)". No such luck this time! I have tried the myriad of solutions on your site, but to no avail. Can you think of anything else to try? Thanks a lot.
Type a value 1 in a spare cell
Copy the cell
Select the General cells
Paste Special
Paste Special
Multiply Values
Sorry, but that doesn't solve the problem. The result is '1' - I have tried the same idea in other cells by entering data (both as Text and as General) and then doing the 'Paste Special' idea, and it works fine. However, I can make no such procedures work on my data - always a #VALUE answer. Appreciate your response, though.
Thanks, the fix was buried in the Ribbon of Confusion. Microsoft Excel help is never useful. Funny that you have to google to get real microsoft software help. This company sucks and their software (especially Office 2010) gets suckier every year. It is sad.
I love you (thanks!)
Excel had me totally confused. I searched for a pretty good while looking for this info. Now after reading this, I'm an expert at the whole formula as text thing.
it was the 'General' formatting problem that's been tripping me up across spreadsheets. thanks for the answer! i just thought excel was bonkers.
thanks, after i push Ctrl + ~ ( all my problem is solve.. !!!!!)
[...] I stumbled across a great blog post from Excel MVP, Purna Duggirala (aka Chandoo), entitled “Excel Formulas are not working?!? What to do when all you see is the formula, not result“. Today, I needed to find it again, so I thought I’d blog it this [...]
thank you so much! Worked like a charm (format as text was selected somehow....) happy camper 🙂
Just great!!!, thanks a lot!!!
can someone please fix this formula??
=IF(AND(B12>=F11+0.2*(F11)),"A",IF(AND(B12>=F11+.10*(F11))-<F11+.20*(F11),"B",IF(AND(b12>=f11-<(f11+.10*f11),"C",IF(AND(b12>=(f11-.1*f11)-<f11,"D",IF(AND(b12>=0-<(f11-.10*f11),"F","NO GRADE"))))))
f11 is an average grade of 81
@Bdgrl651
try:
=IF(B12 > =F11+0.2*F11,"A",IF(AND(B12 > =F11+0.1*F11,B12 < F11+0.2*F11),"B",IF(AND(B12 > =F11,B12 < (F11+0.1*F11)),"C",IF(AND(B12 > =(F11-0.1*F11),B12 < F11),"D",IF(AND(B12 > =0,B12 < (F11-0.1*F11)),"F","NO GRADE")))))I think the error was in your format of the And() functions
They require =And(Condition 1, Condition 2, Condition 3 etc)
Where the Conditions will be something like B12>=0 or B12 < F11+0.2*(F11) etc You may need to retype the " characters
Your site is very much appreciated. I was caught in the formula/text scenario for hours until I stumbled across your solution. Everyone on all the other sites just guessed what the problem was caused by. You were the first place that actually knew.
I have a tekst (of numbers) like this "950012-435" in A1 og want to have a copy of this in B1.
Now, it I define A1 as <text> and B1 as <General> it works ok.
If A1 is <number> a calculation is done. If B1 is <text> the B1-"text" will be "=A1"
However if I make a downflow, A2 (empty) will generate a "0" i B2. Not what I want. I want B2 to be empty if A2 is empty
Thanks for posting this! it resolved my problem..
thanks a lot!
You're awesome!
Savior !! I didnt know text cells would mess with my Fx. Thanks for the tip!!
Thanks a ton for the help. You saved my time nd my hardwork !! 🙂
Thanks; that was incredibly helpful!
Hello,
The following formula works in cell B1 but until a selection is made from a drop down list in C9 it shows "FALSE" in B1. How can I get B1 to either be blank or show another value until a selection in c9 is made?
=IF(C9=FA8,"A",IF(C9=FA9,"B",IF(C9=FA10,"C")))
Thanks
Thanks chandoo!
Thanks that worked like a magic...
Nice work, just the info I needed. Cheers!
thanks . its only in this blog i found solution.I accidentally hit the 'show formulas' now it's working.
Hi ya, CAN ANYONE HELP PLEASE?!?
I have a basic statement that isn't working:
=IF(G21=$M$12, "Yes", "No") This is in cell CI21 M12 is an absolute cell while the column G reference will need to change within each row
G21 is populated by a VB .Value = ""
M12 is a Validation list
All cells are set to 'General' - the Show Formulas is off - and the Automatic calculation is on
But it still doesn't update when the value of M12 is altered from the list?
I have tried typing long hand with more brackets, copying another IF that does update and amending the cell references, switching the order to M12=G21, creating the formula in a 'nearer' cell (incase it was a capacity issue) and checked that all three cells are comunicating with =SUM(G21-M12)... which does work and display the result. I've also tried physically selecting the cells rather than typing their references in the formula ---- but nothing has worked.
PLEASE HELP!!
@Sarah
can you post your file somewhere?
Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
Okay,
It was me being silly --- all i had to do was 'F2' each error in the route cells in column G to free the cells up to 'talk'
thank you anyway
Thanks so much for leaving this on the web. Thankfully found this while I still had hair left!
Signed,
Formatted as Text not General
BRAVOoooooooo!
Running Excel 2007 under XP.
I just discovered that if the cell is locked (as in Format Cells > Protection), the formulas, when edited, appear as text no matter the formatting of the cell is...
Simple answer is:
Remove the lock, edit the formula and then lock it again. At least, it worked for me... 🙂
Hi
i have downloaded excel spreadsheets from the net, and now all my other excel docs do not use there formulas anymore, can anyone help me?
Thanks... CTRL- ` is a killer! Thanks for the tip. It fixed my issue. I've never even heard of formula auding. I know it NOW>
[...] Source: http://chandoo.org/wp/2010/04/12/excel-formulas-are-not-working/ [...]
this post made more for world peace than any polititian ever 😉
THAAAAAAANK YOOOOUUU!
I tried so many things, made sure the cell was not set to text, copied the formula from another cell where it worked, thought I tried everything for the last couple of hours and then found your site. There was not a ` before the = sign, just a space. I backspaced in the formula bar to remove it, and the whole thing works again, yeah!! I can't figure out how it was adding the space, but that tiny space threw a money wrench into the formula big time. Thank you for helping me not pull my hair out (at least for today!), and for reminding me that most errors are operater errors and fixable.
COUNTIFS('[Kept Appts Detail.xlsx]Page 1'!$B$5:$B$50000,">01/01/1900",'[Kept Appts Detail.xlsx]Page 1'!$B$5:$B$50000,"<08/30/2013")
I want the last item "01/01/1900",'[Kept Appts Detail.xlsx]Page 1'!$B$5:$B$50000,"<H2")
I am having a problem with the summation. when i do the sum formula and entered, the value that shows is "0". What could be the problem and what should I do?
Thanks........................................
Turns out (for me at least) that you have to have the cell format set to general BEFORE you start building the formula . . . I had it as text at first, and then went back and changed it to general, and got nothing helpful. It seems you have to begin from a general-formatted cell or else you never get to see the results.
Works now though! Hooray! Time for a coffee.
Thanks for this - I now understand what I need to do to correct this issue rather than trying multiple edits and cell formatting until the formula works!
The key seems to be double checking the "general" format of cell.
Regards, Paul.
One other reason is padding (not spaces) in front of the formula. Sometimes all you have to do is put your cursor right before the first character of your formula and hit backspace to remove the padding....
I was having the same issue. After none of the steps above worked, I decided to try doing just what I wrote above and it worked.
Thanks for at least helping me to think my formula was correct.
Hi I have a spreadsheet that someone else set up, I've had to have it backed up as someone saved it wrong, the formulas now don't work to change the date of births into ages in the column next to it, anyone know how I can change this so it works again?
Sarah, input the date of birth in Cell A1. Then in Cell A2, put the following formula:
=(TODAY()-A1)/365
It should calculate the age...
[…] Source: http://chandoo.org/wp/2010/04/12/excel-formulas-are-not-working/ […]
Thank you, Chandoo! For some reason, the "General" format got changed (elves at work last night??) to "Text," as you suggested. Now the formulas work again. Super thanx! Also -- tho' I didn't need it this time -- thanks for the Ctrl-accent grave hint -- in case that ever happens to my stuff.
Yes.
Thanks...
It's worth noting that if you change it back to text the formula still works as it should. Odd indeed. but thanks for the help!
?n microsoft excel 2007 ? have a problem
I give formula then pressing enter, excel calculates.
But if I change values in the cells witch I used in formulas the answer is same again.
for ex:
=SUMIFS(D5:D17,C5:C17,C20,B5:B17,B20)
I have found it myself (calculate was manuel)
[…] Excel Formulas home page [Visitors: 109,743] Delete Blank Rows in Excel [Visitors: 198,543] Excel Formulas Are Not Working [Visitors: […]
THANK YOU! I HAVE CHANGED MY FORMULA SETTING TO AUTOMATIC NOW IT IS WORKING FINE AFTER HOURS OF FRUSTRATION:(((
Thank you so much, I was so shocked with this problem just now (esp with the approaching deadline), so your advice is very helpful.
I have an issue I could use help with... I have a master spreadsheet that I have transferred information from several other spreadsheets into using a VLOOKUP formula. If a cell was blank with no data to copy, I had the formula return "". I then pasted the values from the lookup data in column AJ into column C. The issue is that I have a LEN formula that points to column C and if it is blank, it is supposed to point to column A. However, for any blank cells in column C it is returning 0 without moving on to A. If I hit F2 in column C, the formula works, but I have 23K rows of data and none of the other fixes I've seen here have worked. Any ideas on how to get them to convert without going line by line?
@Betania
You should have calculation set to Automatic?
Goto Formulas, Calculation Options and make sure Automatic is Ticked
If that doesn't help can you post a sample file with a few rows
Calculation is set to automatic. Here is a sample of the file.
https://www.dropbox.com/s/1yrq1x7wvwrkzt8/Sample%20data.xlsx
I see all the formulas when pressing the command "Show formulas", but when I pree Ctrl + ` nothing happens. Do you have any ideas why the shortcut isn't working?
Hello Jacky
I'm running excel 2007 and when I press CTRL+' it copies the value in the cell directly above the one I am in. To try it for yourself type your name in A1 then select A2 and press CTRL+'.
Thanks a lot buddy.
thanks very much. u've being very helpful
thanks dear
Thank you! This issue was driving me crazy.
Thank you very much, it was a great help.
You rule Chandoo!!!!
thx
had a real problem with a s/sheet keep changing to manual calculation! - finally found out, whatever the first s/sheet that is opened in a session, all others follow that, regardless of their own setting! - my problem was that I was opening a s/sheet that had been sent to me, then opening one of mine - once I found out this, just reversed the order of opening - anyone else had this issue??? (Hui… any idea?)
Thank u ....
I cannot thank you enough for this post!! You saved me from a very aggravated coworker.
Thank you, you solved my problem too. Good vibes and blue skies to you mate.
Love your website!
I just had this problem and I:
1) changed formatting to "General"
2) added a space between = and cell reference i.e. =(space)CP2
3) it worked.
No idea how or why.
Strangely though, if I change the formula, I have to redo these steps.
Hmmmm.
All the same, thanks for the clues!
thank you !!
Praise The Lord! I thought I was going crazy!! THank you Chandoo!
Awesome! THANKS!!!
Excellent!! My case was the 2nd one!!
Thank you very much!
I am trying to sum some cells from one worksheet and show the results of this sum in another. I've tried all the above tips to stop Excel 2010 showing my formulas, but it won't do it. I'm desperate!
Well, I've managed to fix it, but I don't really know how. All I did was copy the formatting from an area nearby that was working like it should! Go figure!
I use Excel Mac 2011: in a table where all the formulas are two numbers multiplied together from two other tables, I have a formatting glitch where the format of some cells (but not others) cannot be changed / format seems to be locked -- I want 187.2 to display as 187, but I cannot change it from 187.2 (cannot make it currency or any other format -- the format is truly locked). Yet neighboring cells behave as they should. Re-pasting the formulas has no effect.
The sheet is not protected, although all cells show as "locked". The cell contents are not locked, I can change freely. However, if its a number, it will only show one format / one decimal point.
I have a question. I am new to excel 2007. I volunteer at a school and I am making a new report card in Excel 2007. I have the spreadsheet done. Now I have used the formula=average(H14,AB14)to get an average. The problem is in the other cells it is giving a rounded number( I want the rounded number) but It is giving me an incorrect number due to the number in format cells function is not rounded. So my final average is not correct. How do you get excel to calculate by the rounded numbers? I hope I have explained my problem well enough.
I am so excited. I found the answer on another site. It was simply put Round at the beginning of my formula. Ha! So funny!
Excel 2003 do not eval formula that have references to other plan if the column is in TEXT cathegory. Change the cathegory to GENERAL to fix the problem.
Hi,
I have problem with excel formula,
when I make I G10 Multiply with I10 then they show rust wrong the G10 cell have also divid formula, if I remove the divide formula from G10 and add value then they make result right, you can see screenshot in this link, http://i.imgur.com/Q0j8fTx.png
pleae fixed this problem,
Sajida
@Sajida
If G10 shouldn't be =D10/3.281
Then remove it
I suspect the value 3.281 dependent on the value of Column F?
eg: =If(F10="Job",D10,D10/3.281)
Thanks for your replay, I find a very good easy solution,
Sajida
Fantastic!!
Ugh. I'm hoping someone can help me.. First, I found this website a few months back when I was having the problem that most people are.. Had hit the hotkey combo to show formulas and this page cleared it up for me.. But today and yesterday I've had a very very strange issue..
I must have hit the same hot-key combo, but what happens is just a SINGLE CELL is showing the formula of what I type in ANY other cell. As soon as I start another formula, it starts showing the new formula in that cell. I can delete the cells but it just keeps showing in that same place. Reformatting it does nothing. The standard stuff I've been reading does nothing.
It's just one cell which shows the formula I'm typing into OTHER cells.. Very frustrating! Any ideas?
Thanks!
@James
Have you tried Toggling Display Formulas Ctrl ~ (<- That should be Ctrl Tilde, The squiggly line next to 1) Do it again to toggle them on/off
my excel not working. when I use proper formula, then double click for below cell, the result is it only 1st cell
@Nunu
Where are you double clicking?
If your referring to autofill down you need to double click the small Black Square at the Lower Right corner of the cell you have selected
Thanks a lot 🙂
When i type 1 in Excel cell it displays 2 ....Can any one advise how to get rid of this problem..Pls
Thanks so much.
Thanks a lot Chandu...was struggling with this for 2 hours, and just came across this page which gave the correct solution!!
Thanks man, this helped me. some how my sheet was showing formula instead of formula. What you wrote in 2010 helped me in 2015. Chandoo you are awesome.
Thanks for sharing this amazing tip. It had taken me 3 days trying to fix the problem of formula not executing, figured out that the reason was i had pressed ctr +`. Keep up guys thanks alot !
[…] Excel formulas not working? […]
The problem that I am having isn't that the formula is showing up, but rather the number that is produced by the formula isn't recognized by a formula in another cell. I have a column of =IF( formulas where if the value is over a specified number, then it automatically enters 8.00 in the cell. When I try to =SUM( the column, the 8.00 values that are entered by the =IF( formula are not recognized and added into the sum. Does anyone have any suggestions?
I am working with a database and need to estimate the workdays between different dates. The issue i have is when i have empty cells included in the calculation, i get a #Value response. with dates most of the "IF" rules don't seem to work. I need a "0" to show when one of the cells is empty. Does anyone have any ideas on how to solve this issue?
thank you
A recent "error-by-mistake" that I found in an Excel file, was manual calculation check - Excel calculate formula only if I edit it again and hit Enter. If I activate Automatic (in Formulas - Calculation Options), it works normally.
Thank you for your lessons 🙂
This is awesome solution.
"You may have accidentally pressed CTRL+` (back quote symbol, the key below escape key in your keyboard) or activated the “Show Formulas” mode in Excel."
This was the solution in my case.
I have macros that need to turn calculation to manual and return to auto on completion.
Sometimes they may be halted and leave calculation at manual.
Is there some code to identify the manual state so I can create a warning directly on my sheet?
@Kevin
When you say halted do you mean by errors or user intervention eg: Ctrl+C
If it is errors you can catch them with an On Error goto Errorcatch
then in Errorcatch:
turn Calculation back to Automatic and Calculate
If your dealing with user intervention
Read the technique discussed here;
http://www.databison.com/prevent-users-ctrl-break-ing-your-vba-code-during-execution/
Thanks Hui
Yes to both points.
I'll set about fixing the deficiencies in my code.
Thank you so much! You saved my time.
Take a look at your file type. My file was doing the same thing...it was an .xls file. After I changed it to a .xlsx file...my formulas started working again.
I NEED HELP, PLEASE!!! My excel formulas SUDDENLY went gaga and I have been sweating in the last 6 hours ever after!
When you highlight a column to SUM, instead of giving result in the cell immediately after the last number to be summed, IT GIVES ME FORMULA ON THE CELL OF THE LAST NUMBER TO BE SUMMED. If I re-do and run through the routine again, IT DOES SAME THING BUT THIS TIME, IT GIVES FORMULA ON THE FIRST CELL OF THE COLUMN TO BE SUMMED and on and on it keeps rotating. I have tried everything I read here BUT TO NO AVAIL! PLEASE HELP! We can Skype if need be, here’s my Skype ID: schrodingerr. Thanks
@Stan
Post the question at the Chandoo.org Forums
Attach your file if possible
@Stan
Post the question at the Chandoo.org Forums
http://forum.chandoo.org/
Attach your file if possible
I have spent about 2 hours trying to sort out a formula not showing the result - 2 minutes reading this article - sorted. Many thanks.
Hi
just i am unable to realize that why my note pad is not calculate formula value. its calculate only =,*,-,+ formula's . i want calculation with conditional formula so plz reply what should i do.
One thing: after you format the conflictive cells as number, or general, you have to go to double click on them (to edit their content), then press enter. That way the format will update and you will see the result instead of the formula. Auto-filling by dragging from other cells works too.
I have just spent 20 minutes trying to figure out why my formulas were not calculating. I went through your suggestions, but still no joy. Eventually I found the problem, I feel a bit embarrassed.......
I did not leave a space between the = sign and the formula. Once I put the space everything worked as supposed to.
@Tom
A space isn't required between the = sign and the formula
=A1 * B1
is the same as
= A1 * B1
But you cannot have a space in front of the = sign
The leading space will force Excel to think it is a text cell
Hi! Just wanted to say thank you. I've been having that problem from time to time and I never realized that setting my cells to Text would deny the formulas.
Thankuuuuuuuuuuuu so much
Thank you buddy your solutions helped me resolve the issue.
Thanks!
Thank you for the help
On C1 cell, the formula is =(B1/A1)*100
where B1=0.02, A1=2.50. the result should be 0.08 on C1 cell.
the program give 1.0 as the result.
any idea?
A is average
B is Standard Deviation
C is Coefficient of variance (S.D./AVERAGE)*100
@Victor
I get 0.8
If you get something else?
1. Check that calculations are set to Automatic
goto the Formula, calculations tab and click Automatic
2. Check the Number format for the cell
Hi,
One of my users is using Excel 2010. He uses Platts formula to extract market data, which requires Platts plug-in. However, I noticed something weird, when the same file is open on a laptop without the plug-in, the formulas keep showing the result, instead of error.
I tried evaluating the formulas, it actually evaluated an error (#NAME?) but somehow it continued to show the result (correct result). As if, the excel refuses to show the error.
Opened the same file on Excel 2016, this time the formula errors are all over the place (which is the correct behavior). Anyone could explain this?
i use the SUP function in excel 2013 it was working well but at the end of the table is no longer give me the results 🙁 the cell still empty
I which you can help me i should finish my work by the end of this week
thanks for every thing
Can anyone help with this problem. I have an excel worksheet made to calculate Payment in Lieu of Taxes. IT works with tax rates and percents. I have it all working properly except the last column.
To get the % due cell I entered a formula and it gives me the resulting percent in the correct cell. (example: d3/d4 and that goes into cell d5 which is my %due cell) Then I needed to calculate the $ due in another cell. So I tried to add a formula to take the total $ amount from one cell (let's say cell e6)and multiply it by the %due cell (D5)....the amount is wrong. I think it is a problem with me already having a formula to calculate the % due. Then when I use that cell in my next formula it gives me a wrong total... Like just a few dollars off. (example: e6*d5)
My numbers are .5904 cell d3 ; 5.6111 in cell d4 and that should equal about 11% to go into cell d5. Then I have $2591.56 in cell e6 and multiply it by d5 (11%) and should get: 285.07 but it keeps giving me 272.68 instead.
Sorry this is so long, not quite sure how to explain it.
Excel formulas (mainly the more complex ones, such as financial functions) return a request for more arguments, but I know I have included all the arguments because I follow the prompts for entering a formula ...
CTRL+`was the culprit. I would have never figured that one out in a million years. Thank you!
Hi,
I am using if formula but the result not coming properly as my ecorrect i checked and there is in format also not in text
i am using formula
=IF(V4<0=4,"Payment received","Pending")
Please help me out plzzz
Hi! Please help.
I am using Excel 2016. I have tried every answer from the blog above.
Its formatted as general. Ive done the = replace, ctrl' is not it.
Still my formula still just shows instead of calculating.
The formula works on another workbook, but not where I need it to. I changed the data lookup range and boom. dead.
Its an array formula pulling rows from another sheet based on the input of a column.
Please help. thanks!!
{=IF(ISERROR(INDEX(Master!$A$1:$Q$459,SMALL(IF(Master!$K$1:$K$459=MID(CELL("filename",O1),FIND("]",CELL("filename",O1))+1,256),ROW(INDIRECT("$1:$"&COUNTA(Master!$K$1:$K$459)))),ROW(1:1)),MATCH(A$1,Master!$A$1:$Q$459,0))),"",INDEX(Master!$A$1:$Q$459,SMALL(IF(Master!$K$1:$K$459=MID(CELL("filename",O1),FIND("]",CELL("filename",O1))+1,459),ROW(INDIRECT("$1:$"&COUNTA(Master!$K$1:$K$459)))),ROW(1:1)),MATCH(A$1,Master!$A$1:$Q$1,0)))}
Thanks very much! You save the day!!!!
I was working in my Excel and sudden it shows that Excel formula not working. I have never run into such problems. The formulas were not getting the update and I was not knowing what to do then one thing that helped Excel formula working were these:
• The calculation is set to Manuals
• The cell is formatted as Text
• Space entered before the Equal sign
Do check these things to make your Excel Formula Working again and gets updated.
You can also visit http://www.repairmsexcel.com/blog/fix-excel-formulas-not-updating-issue for more information
Format the cell as "General". (Right-click the cell, select Format Cells, and choose "General.") Delete the "=" at the beginning of your formula, and hit Enter. Insert the "=" back in the formula at the beginning.
regret to inform you that i could not upload any workbook for reference. Pls help with the excel formula
Salary Range
Age 15-25 26-35 36-45
18-25 1 2 4
26-30 3 5 5
31-35 4 81 1
36-40 6 8 2
Require excel formula when the age and salary is written the corresponding value with the range data of the table gets displayed in "Output"
Example
Input
Age 29
Sal 29
Output 5
@Sreekumar... interesting question. Your Age & Salary ranges are somewhat inconvenient to work with. Assuming you can split the range start and end values into separate cells, you can use INDEX(outputrange, match(), match()) like this:
thank you, it helped me (:
I have excel 2002. My cursor shows a formula when I select a cell hoping to select a range of cells by dragging it. I must have inadvertently pressed some keys which changed the usual behaviour of the cursor. I also used to copy a sheet and rename it using the ctrl and another key but I’ve lost that now .
What can I do now please?
Dear Chandoo, I am trying to enter a formula using VBA, but need to have the entire column formatted a text! It shows formula text only after the code is run.
I don't suppose you resolved this issue, did you? ?
I've got the same issue - I've copied a value from a template field into a cell, and I've tried setting its Formula, its Formula2 and even its Value, but all I get is the formula displayed as text - no preceding ' , the cell is formatted as General, and if I enter the cell, press F2 and then Enter, the cell executes the formula and displays the result just as I want it to, but until I do that, it just displays the formula. ?
I've got a similar issue but none of these suggestions help.
Using VBA, I copy a formula from a template field into a cell, and I've tried setting its Formula, its Formula2 and even its Value, but all I see is the formula displayed as text - no preceding ' in the cell, the cell is formatted as General, and if I enter the cell, press F2 and then Enter, the cell executes the formula and displays the result just as I want it to, but until I do that, it just displays the formula. ?
Paul
Can you please post the question in the Chandoo.org Forums
Please attach a sample file to allow a quicker answer
Thanx
Hui...
How do you debug a donut chunk? Also, what if i want to show formulas just for one cell, but not other cells, but also i want any relative addresses in that formula to update correctly if the position of the source cell moves? In other words, like the Show Formulas mode, but just for one cell.
Using Power Query to create a spread sheet that will have a column with formulas in the cells.
The formula is "=VLOOKUP(P:P,CODES!B:C,2,FALSE)"
When it posts from Power Query, it shows it as "text" and the formula will not function.
I know how to fix it , but this is a report to be ran every day and the formula will not change.
I have a table in which I'd like to use the MID() function in column 'C' to pull a value out of a string in column 'K'. However, all it puts in column 'C' is the formula. I've ensured the "most common" cause is not the issue and the formula is correct in structure. It's not a highly important issue but it bugs me that it's not working.
Thanks in advance.
OK. I read and RE-read this and it finally dawned on me... I set the format of the cells with the formula in them to GENERAL, "edited" the formula, and when I hit ENTER, VOILA!! it all worked!! Thanks to everyone, specially Chandoo, for the assist. Still blows my mind, but...
Cheers!!
All of these still did not solve my problem, and then I wondered if it is because I have iterative calculations enabled (I need that for another part of the worksheet). I switched it off and it solved my issue. (It's under Excel Options > Formulas.) So to do calculations I need to turn this off, then turn it back on again afterwards.
Now tell me how to do this for 4017 rows ...
Helo..my computer is saying that it is not a formula that im trying to enter...my formula says =$6.45*(C2/60) whereC2 is 12
Take out the $
Try =6.45*(c2/60)