Many of us start using Excel to keep track of something. And along way, we realize that Excel has a powerful feature called formulas, using which we can automate a lot of things. BOOM! Before we realize, we are in the thick of VLOOKUPs and SUMIFs.
But, along way, we also pick up a few bad habits or believe a few myths. Today, lets bust 10 Excel formula myths that we hear often.
1. Shorter Formulas are Better
I think it is human tendency to shorten and optimize things. We take great pride if we can shrink a task that takes 10 minutes to 12 seconds. But is it the case with Excel Formulas?
In my opinion, any formula that does the job is better. It does not matter how short or long the formula is. Often, we can come up with a reasonable formula in few minutes, but we waste several hours trying to shorten it. Time that could be used for better things like impressing your boss or shipping a product.
2. IF Formulas are Bad
I dont know where this comes from, but I hear it often. Oh, why use IF formula, as if it is going to slow down the computer drastically. Well, for most cases, we are dealing with reasonably sized data and Excel is fast enough to calculate formulas whether they are IFs or REPTs or something else.
So go ahead and use IF formula, if that is what you need to use.
3. VLOOKUP is slower
Ok, here is another one. For some reason people believe that VLOOKUP is slower than alternatives like INDEX+MATCH, OFFSET+MATCH, MATCH, Array formulas. Well, in my private tests, I found mixed results. VLOOKUP performance is almost same as that of other alternatives for small and medium (10000 rows) sized data sets.
Of course, if you have a workbook with million rows, then you should spend time looking for the fastest formula. Otherwise, just use VLOOKUP and be done.
4. Helper Cells, Helper Columns are Lame
Again, another myth that has no reason to exist. Each Excel sheet has 17179869184 cells and there is no reason why we should not use a few to support us in our formulas or models. Use helper cells, they keep your worksheet simple and easy to understand.
5. Formulas should start with = sign only
Do you know that you can start a formula with + or – sign too?
Well, you can type -SUM(1,2,3) to get -6 in a cell.
Similarly, you can type +SUM(1,2,3) to get 6 in a cell.
PS: You can also begin a formula with @ sign. I am not sure if there are more…
PPS: You can put ‘ before the formula if you just want to show the formula instead of running it. So if you write ‘=SUM(1,2,3), Excel would show =SUM(1,2,3) in a cell (instead of 6)
6. Formulas cannot refer to other Excel Workbooks
Well, that is not correct. You can refer to data in other workbooks in an Excel formula. For eg.
=SUM(sales.xlsx!q1Sales,2000,$H$2:$H$13)
will sum up the named range q1Sales in Sales.xlsx workbook, the value 2000 and the cells H2:H13
Remember, if your workbook is closed, you need to put the full path, like this:
=SUM(‘C:\full\folder\path\sales.xlsx’!q1Sales,2000,$H$2:$H$13)
PS: Certain formulas do not work with closed workbooks.
7. Formulas should be written in a cells only
Well, this is wrong. You can use formulas in named ranges, conditional formatting, data validation. You can also assign formulas to drawing shapes, chart elements (like titles, labels etc.).
See these examples:
5 ways to use formulas in Conditional Formatting
Custom Data Validation with Excel Formulas: Example 1, Example 2, More
Make your charts smarter with Formulas
8. We cannot copy a formula without changing references
Of course you can. If you want to have the same formula as in the cell above, just press CTRL+’
You will get the same formula and you can modify it as you want.
If you want to have the same formula elsewhere, just go to the formula cell, press F2, select everything (SHIFT+HOME), copy (CTRL+C).
Now go to the target cell and press F2 and paste (CTRL+V)
9. Formulas cannot do ‘x’…
May be they cannot feed your cat or take your dog for walk or change a nappy. But there is a formula for almost everything. And Excel team at Microsoft is adding new formulas in each version. It wont be long before a =ChangeNappy(kidname, <optional dispose nappy>) appears. Well, may be.
But the best part is, you can create your own formulas, called as User Defined Functions. And once you start doing that, there is no limit to the possibilities. You can create a CONCAT() to add up a bunch of text values, a NETWORKINGDAYS() to calculate working days based on a custom weekend setup or anything. [More UDF Examples]
10. Formulas are difficult to learn
Only if you think so.
Excel formulas are very powerful and very easy to learn. You need to start slow and go one step at a time. It might take a while to wrap your head around the referencing styles and various formulas.
But once you learn a few simple formulas, rest of them will be easy to learn. And before you realize, you are in the thick of VLOOKUPs and SUMIFs.
Oh, wait, I said that already. But then who says we cannot repeat. That is another myth!
What myths you hear about Excel Formulas?
Thanks to all your emails, comments and forum discussions. I hear about a lot of myths and bad habits all the time, when it comes to Excel. I found that giving in to these myths limits our ability to do more.
What about you? What myths you have heard when you started learning Excel? Please share using comments.
Learn More About Excel & Excel Formulas
If you just started using Excel, then you are at the right place. Go thru below links to learn more.
1. Excel Tutorials for Beginners – 10 videos to start your Excel Journey
2. Excel Formula e-book – 75 Excel Formulas, explained in plain English
3. Excel Formulas – Examples & Demos – More than a 100 examples on Excel formulas
4. Excel School – Online Excel Training Program by Chandoo. With 23 hours of video lessons and downloadable excel files, you will master every aspect of Excel, very soon.
PS: Join our news letter. You will get emails with Excel tips, tricks, tutorials and more, 3 times a week.
40 Responses to “10 Excel Formula Myths – Busted!”
Excel formulas are not suitable for text processing: I think this one is more truth than myth.
I tried once to use Excel to put together a report with lots of text and numbers for a product evaluation by combining data from different Excel sheets from various evaulators. The problem I encountered was that long text blocks were not completely transferred from one workbook to another. It turns out that Excel 2003 appears to have a character limit for references to text cells, but this applies only when the referred workbook is closed - the complete text was transferred when the workbook with the referred cells was opened.
In general, I find that it is often very cumbersome to do simple text transformations with SUBST, RIGHT, LEFT etc. that would be completely effortless with regular expressions in PERL.
Lesson learned: don't use Excel as a word processor if you can avoid it.
"Otherwise, just use VLOOKUP and be done."
I would recommend using INDEX+MATCH though. Not because of speed but because of the flexibility; you can lookup columns to the left from your lookup value and you don't have to specify column numbers. It takes a little getting used to but I now almost always use INDEX+MATCH instead of VLOOKUP.
Your comment doesn't debunk a myth, it re-states an opinion and adds details that are beyond the scope.
As Chandoo said, for small and medium-sized datasets, VLOOKUP is perfectly fine.
When someone is trying to compare a static list of 200 names against a static list of 150 names, I agree with Chandoo, "use VLOOKUP and be done."
No. VLOOKUP should be discouraged from use.
1. By default, VLOOKUP returns an approximate match. This is dangerous for users who don't know formulas
2. VLOOKUP can't lookup to the left of the table array origin column. So when columns get rearranged, difficulties can arise.
The only good thing about VLOOKUP is that it shows you that the user is a medium level Excel user.
Have you ever used VLOOKUP? I doubt because, VLOOKUP has an Exact match. If someone tells me that VLOOkUP is bad because it does not lookup to the left, I will forgive that person if he/she does not have a database background. Unique identifiers (Primary Keys) should always be at the left-most column of a table. Always. Microsoft must have assumed that database principle in coming up with VLOOKUP
#1 - VLOOKUP indeed has an Exact match (by using "FALSE" or "0" as the last argument.
#2 - There are some cases where there are no Primary Keys to be put on the letf-most column. As en example, I need to build a "Table of equivalence" for (i) Country Names and (ii) Country Classification Groups as (i) and (ii) differ between input tables, i.e. between the World Bank, the IMF, the OCED, the United Nations, etc. For this specific case, INDEX/MATCH is more powerful than VLOOKUP.
As for "3. VLOOKUP is slower", I remember that when using Excel 2007 VLOOKUP was indeed waaay slower than INDEX/MACTH for just a few thousand lines. I admit that I did not try again now that we have Excel 2016 with more powerful machine, I will give it a try.
(PS - Apologies, English is not my mother tongue)
Myth #11 - Database formulas are ok to use. =DSUM, =DCOUNT, =DCOUNTA, =DGET are just as good as function.
Don't be afraid....
agree with m-b
MYTH - you can only lookup data to the left or down.
BUST - use INDEX+MATCH
Others (which I think you can find answers to on chandoo)
MYTH - you must have a complete match for vlookup to work
BUST - use fuzzy lookups (e.g. vlookup("*"&something&"*",somewhere,index,FALSE)
MYTH - you can't have a "carriage return" in a cell
BUST - use ALT+ENTER
MYTH - you can't use EXCEL to change a nappy
BUST - I earn my living in Excel... a little of it pays the nanny... she changes the baby's dirties! Thanks Microsoft & Excel Gurus for your help!
vlookup is definitely slower on a lower end machine from the late 90s, where some companies I know still use them. Also, I'm not sure if someone has tested it. Files with vlookup instead of index+match, somehow, look much larger to me.
helper cells or columns are not lame. But the absense of helper columns definitely serve the purpose of making my life harder. Coming in as a temp replacement worker I illustrated to the company that how much harder and more time I needed to understand a 10 line formula with multiple functions. I'm just guessing my predecessor was just making it difficult for the company to replace him.
press CTRL+’ to copy formula in a new cell - AWESOME 🙂 I used to copy and paste from the formula bar, NOW NO MORE!! 🙂
another great blog. thanks!
as to the vlookup versus index/match issue, i have found that recalcs are tough no matter what you use when you get above 10,000 rows. but, in general, i think that "pointer" operations work best. say you need to pick up 8 fields from one row in a large dataset. then, one match formula finds the relative row location and 8 "index" formulas referencing the match row number are better than many vlookups. for 2007 and 2010 excel, restructuring the database (one that is structurally stable) so that there are more columns and less rows also can speed things up because moving from a match/vlookup operation to a pointer operation (direct to the col with index) is faster... sort order is also inportant as is presence of volatile versus non-volatile formulas in the database set being feferenced.
"Formulas should start with = sign only"
For clarity, this should not be treated as a myth. Start your formulas with "=" so it's immediately obvious that it's a formula.
I tend to agree with you Jon, but in Excel 2010, if you start with a + or - Excel will insert a leading = anyway (you end up with "=+..."). I sometimes use the + sign if a formula requires number pad operators and I am mousing left-handed.
I can't make Excel start a formula with @ or any other top row operators except =, +, -.
However, when training others I say exactly what you say! 🙂
I have never heard of these "myths" before. I consider myself an intermediate to advanced Excel user. No one I know of quotes these myths or believes them. Perhaps it is a geographical thing. The other Excel users I know and work with do not quote any of these misconceptions.
The closest thing I have heard to any of these is to avoid long, nested IF statements, especially since Excel 2007 will let you nest dozens of them. If your IF statement is nested twenty or thirty times, then it is too long, and there IS a better formula available.
Charles Williams says (http://www.decisionmodels.com/optspeede.htm):
"VLOOKUP is slightly faster (approx. 5%), simpler and uses less memory than a combination of MATCH and INDEX or OFFSET.
However the additional flexibility offered by MATCH and INDEX often allows you to make significant timesaving compared to VLOOKUP."
Great post as always Chandoo. In some ways, I have found it useful to believe in myths #1 Shorter formulas are better; #2 IF formulas are bad; and #4 Helper cells are lame. The reason I say this, is that by believing these myths, I have often forced myself to think about other solutions using a combination of different excel functions. By doing so, it has greatly improved my knowledge. Sure, I could quickly and easily use an IF here and a helper there, but by doing so I feel that I am cheating myself of a more "elegant" solution. As you point out, it often does take longer...but hey I love using Excel so I dont care how long I spend....but my boss probably has a different perspective 🙂
I think the two most important lessons people need to learn is that usually there is no need to import data from antother excel file into another.
The second one is: Don't abuse Excel as a database. However, there are cases that can make sense, but in general you are better off to work in a database like Excel if you want to store data and not calculate or generate diagrams in it.
Index + Match
Do be careful with this one depending on your version.
I cannot remember "Exact" details, but back in 2004/05 I cam up with this combination by myself when trying to create a s/sheet.
My system would crach and take hours to do so, freezing everything in sight along the way.
I referred to the Microsoft website for error details and this error was documented.
There was a limit of only being able to use certain nested formulas a certain number of times times within one s/sheet using v2003 (7 or 10 times from memory).
Very random error I know.
Maybe some of the gurus on this site can remember this one in more detail than I do?
For my 5 cents worth
1. Shorter Formulas are Better
Any Formula that YOU understand is better than one you don't.
This doesn't mean you shouldn't use formulas you don't understand but use them as learning guides until you understand them.
5. Formulas should start with = sign only
All Formulas DO start with an = sign
You can enter a formula using +, - or @ and Excel happily adds the = bit to it
Type +Sin(B2) and Excel happily adds the = to it
Type in @Len(B2) and Excel once again happily converts it to =Len(B2)
Give it a go...
Crtl+' <---- Brilliant, thanks!
Oh, and I LOVE helper columns. Obviously we all have different jobs but helper columns are just essential in my little corner of the world
Related to #5 (I think I first saw this on John Walkenbach's site)
Typing "++++1" into a cell will convert to "=+++1" and will remove an extraneous plus sign every time you re-enter and exit the cell. However, type "++++A1" and while it changes to "=+++A1", the extra plus signs won't go away.
Also, I have to agree with Hui about #1. You need to understand the formula first, and learning how to use some AND/OR functions to reduce your nested IF functions CAN increase formula speed.
#9 UDFs
Long nested formulas can be a great opportunity to dip your toe into UDFs and VBA.
I caution against confusing the terminology:
- FORMULAE are the combination of cell references, hard coded values, Excel functions and/or mathematical operators to produce a desired result (generally in mathematical or text format)
- FUNCTIONS are special inbuilt shortcuts/tools that return an outcome using cell references and hard coded values as their arguments (inputs/fuel).
To produce good outcomes with formulae requires a good understanding of simple algebra (yeah, that strange math's topic our high school teachers confused us with!)
To produce good outcomes with functions requires the user spending time to read and understand the Excel Help information about what the particular function is designed to do and the arguments it requires.
Although I haven't seen any real tests of performance, replacing my IFs with boolean operations appears to significantly speed up performance when there are lots of conditional operations (as I usually have on my spreadsheets). I've also noticed that using INDEX is much, much quicker than using OFFSET. Finally, if you are using VBA, Application.VLOOKUP is no where near as fast as using a FIND function within Excel.
@Jordan
You are correct, Boolean operations are as fast as you can get within Excel as they have minimal overhead.
.
Index is a Non-Volatile Function whereas Offset is Volatile and this is why you see the slow down expecially when using large number of Offset formula
Hui,
I think you are correct in the volatile nature of Ofset.
My previous note re nested error may have related to a nested offset - this now makes more sense.
Another powerful function that people seldom use is LOOKUP....
what is worth noting is the ability of this function to point both Left and Right to get the values.... unlike VLOOKUP...
I am not sure if there is any myth about this function "for not to be used".
@Hui Right, so there is a good reason to use boolean operations over IFs. But I do I agree with your earlier post; that the best formula is the one most easily understood.
.
As for VLOOKUP - it is significantly slower in my experience. From a computational complexity standpoint, VLOOKUP must be inherently slower than INDEX because it takes O(n) time (that is - it will have to look through n items before reaching a match - and the match might be on the bottom!) whereas INDEX is constant time. I think the real myth concerning VLOOKUP is that you need several of them to return several items when, in fact, you can supply an array to return several items with one VLOOKUP statement. Chandoo's post on that point is what made me a follower of this blog!
I'll second the UDF's statement. They were covered in Chandoo's VBA School(TM), and with really quite minimal learning I hacked together a couple of UDF's that I'm making regular use of. And the complexity of building them was really on par with 'hello world' type stuff.
I literally just ported some of the nasty formulas into VBA. I tried to make use of the case statement that we learned in the class, but even if I didn't have that, I could have achieved the same thing with if's - which is to say, just packing what I already do between a few basic lines of code.
A few other assorted comments:
-Vlookup/Index - I don't really have a dog in that fight. Need quick and dirty? Reach for vlookup. Need something more exotic? Use index. I'm sure there is a performance difference, but I've personally never found a project where the performance is magically improved by replacing vlookups with index.
-Helper columns? Any 'mega formula' (I think that's what walkenbach calls them) I'm probably going to prototype with helper columns. They help me to isolate and simplify the problem. But I don't beef with them. I generally try to hide them because I feel like a dope:) Hell, when I make a dashboard, I often end making at least one 'helper sheet' that's all support for the presentation anyway. I've done it less and less since somebody put out an excel markup for Notepad++, fwiw.
And Chris: sometimes it's fun to play with index/offset/match while using things like address or indirect. I guarantee you'll crash excel at some point.
The beauty of excel is that you can very quickly create a
(Again) The beauty of excel is that you can very quickly create a solution ( hopefully sound) but may not be techically efficient. What one needs to think about prior to getting too bogged down on better or faster ways of doing things is does this spreadsheet have an on-going life and then is some other party going to have to understand its structures and formulas to maintain it.
Beyond that play it as you see it, however
Try and make formulas as readable as possible - refer to named ranges if possible ;
If you're not using IF formulas then you probably not going to get a great deal of added advantage from a product that has eclipsed a human generation ;
Use Helper rows/columns/cells if you please but ensure that they are safe from destruction ;
If you think that a UDF might be a better approach then its not that hard. It can get a bit fiddly if you send the file elsewhere as there may be mismatched libraries and security issues;
=/+/- are all relevant ways to start a formula with =/+ being a better way to enter a number into a cell as you can add another number to it later and excel will see it as number rather than label (remember Lotus) ;
A formula referencing another spreadsheet was never an issue but there are traps if folder of filenames change or if the primary file is sent to another location;
Ctrl+' to copy a formula without altering the anchor points seemed interested but all it seemed to do was remove the source formula!
#8 We cannot copy a formula without changing references
I've created a couple of little macros in my personal workbook that help me with this one - i use it all the time to copy and paste a formula exactly as is from one cell to another.
MyFormula = ActiveCell.Formula 'mapped to Cntl+Shift+C
and
ActiveCell.Formula = MyFormula 'mapped to Cntl+Shift+P
I now need to figure out how to do the same for a range of cells.
@Stuart
I remember the first Spreadsheet I ever used was an Apple Spreadsheet back in 1982, can't remember what it was called, but it had a Replicate Function which does exactly what your suggesting and nearly 30 years later we still don't have it in Excel !
If you want send me your code and I'll assist with applying it to ranges.
Click my Name to the left for my address.
Dear chandoo. I have two excel files. Want to copy one to another. I got formulas like: ='C:\Users\Vali\Desktop\FOR FEEDBACK\WEST\WT-III\DABDAGANG UB\[15) HH DEBDAGANG FILE BY vali FINAL 20 JUN 2011.xlsx]Data HH'!J666. Now i want to keep just 'Data HH'!J666.
I tried in Find replace options. But, I failed. How should I do this. I have bundles of data.
Kindly help me
Replying after four years...you might have already found the solution.
If not here is the solution. Assuming that your formula text is in Cell A1.
=MID(A1,FIND("]",A1)+1,99)
[...] encountered. A prime example of recommended reading for moderately familiar excel users is “10 Excel Formula Myths” which contains this [...]
I just recently realised while trying to explain the benefits of INDEX+MATCH to someone that you can actually use VLOOKUP+MATCH (to look up the column number dynamically). Strangely I've never seen anyone mention this. Is there a particular reason for that?
From my own experience, I don't often run into dynamic columns. You know that old saying "a stitch in time saves nine..." so in this spirit I try to organize my spreadsheet in such way as to limit the amount of dynamic lookups - usually that means the location of my columns is always static (so I always know the column number I'm looking for). My guess is that the static-column layout is typical for many folks, so that's why there hasn't been much use for VLOOKUP + MATCH. But that's just a guess. And now that I think about it, there are lots of cool uses for VLOOKUP + MATCH. Good find.
http://arstechnica.com/tech-policy/2013/04/microsoft-excel-the-ruiner-of-global-economies/ scary
Right you are about the VLOOKUP! Did a comprehensive analysis of several approaches to looking up data in Excel (VLOOKUP, INDEX MATCH, VBA and SQL): http://www.analystcave.com/excel-vlookup-vs-index-match-vs-sql-performance/