This was an amazing experience and is still fresh in my mind as I write this blog post. So, it got me thinking, "In the Excel jungle, what would I have in my survival kit?"
I thought I'd ask myself the following question (and I'd love to hear your answers too, in the comments) - If I could only have three functions/features in Excel, what would they be? I am going to assume that basic mathematical functions like adding and subtracting and SUM are there, but what are the top three additional features that I wouldn't want to be without?
So, here are my top three (in no particular order):
- VLOOKUP - Having Excel look up information from tables elsewhere in the spreadsheet is an incredibly useful facility. I did, however, think for a while about this as I would have preferred INDEX and MATCH, but that would have taken up two of my choices!
- PivotTables - I use them all of the time and they remove the need for formulae in many cases.
- The IF statement - being able to get Excel to essentially make decisions based upon the information it has available is too useful a feature to give up!
Please let me know yours in the comments.
By the way, although you've missed the discount, you can still get Mynda Treacy's Excel Dashboards course until it is taken down on Thursday night.
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".
agree with the above - but how do you get vlookup not to return a value if the value you are looking for is not there? I often would want to use vlookup but am nervous as I find it will often choose the closest value, as opposed to returning N/A. Thanks
ReplyDeleteI'm bushing at your username! You should enter the fourth argument of the VLOOKUP as FALSE. If this is omitted, it is assumed to be TRUE and will find the first row that exceeds the lookup value (if they are in ascending order)! This is great for commission rates or tax bands but rubbish for most requirements. This argument is explained in the post linked to from the article.
Delete@AF: You can also try a combination of IF and ISERROR with your VLOOKUPS using False to ignore missing values.
DeleteThe syntax would look like this:
=IF(ISERROR(VLOOKUP(value_lookup, table_array, index_column#, FALSE)),"Value not found",VLOOKUP(value, table, index_col#, FALSE))
If the value is there, ISERROR returns a False and performs the lookup on the ELSE portion of the IF-THEN-ELSE.
If the value is not there, ISERROR returns a True, and displays: "Value not found" on the THEN portion of the IF-THEN-ELSE.
-Anonymous
Agree with the IF statement,
ReplyDeleteI find Sorting and Filtering essential tools and would rank them higher.
But I can sort and filter in my PivotTables! - even if that is cheating a bit!
DeleteAgree with your top three! If I could also have a list of keyboard equivalents, that would be great.
ReplyDeleteThat's just being greedy!
DeleteNot sure which I would drop to fit it in, but I find "Freeze Sheet Panes" invaluable - I can't believe some people still scroll up and down again every time they want to know what a column heading is......
ReplyDeleteFreeze panes is great, but if I could scroll down if I needed to. I'd struggle to replace the features I've selected with a workaround.
DeleteI would go with INDEX/MATCH rather than VLOOKUP which I see as an over-specialised function. Great when it does what you need; simply annoying when it doesn't.
ReplyDeleteI personally do not think of Pivot tables as being in the spirit of your 'back to basics' theme. I bit like saying 'forget the knife', I have got to take my combined harvester!
Even IF is sometimes dispensable when using the condition directly, e.g.
{= SUM( (testArray=criterion) * valueArray )}
Peter
I would have chosen INDEX/MATCH if I could get them as one choice, not least because you can do everything VLOOKUP can do and more - but it easn't worth using two choices for!
DeleteHard to argue with your top three. I use them regularly. I have to agree with Kevin that freeze panes is extremely convenient though and I might risk stashing it in the bag without Sergeant Feechan noticing.
ReplyDeleteCarl
Surrey, BC, Canada
That's a Court Mashall offence that Carl!
Delete1. SUMIFS - it replaces many IFs, SUMPRODUCTs, AND/ORs and helper columns in my old v2003 files.
ReplyDelete2-3. INDEX and MATCH - separately as well as together.