- Wedding: June 2014 - British Columbia
I use Excel on a daily basis. I work in Finance/Accounting. The functions I use are quite extensive: NPV, IRR, and FV for lease accounting. For my professional accounting studies, I like to use Excel Solver to get it to solve my multiple constraints and targets for me.
There are many helpful Excel blogs and YouTube tutorials out there, but you will need to know which functions to use.
It is also important to know how to build a model in Excel. A file that does not have a good database structure is very prone to errors and manual work. A good Excel model should not require much manual work nor unnecessary formatting.
Two biggest pet peeves in Excel:
1. External links to files on a shared drive
I’ve seen many bad models where they link references to external files. So, when files get moved, the data becomes essentially garbage. (It becomes an Excel user’s worst nightmare)
2. Too many IF statements
Whoever built a model before me flaunts his/her Excel skills by building an IF statement within an IF statement within exponential times of IF statements that I need a bottle of rum to forget how many IF statements I just had to trace.
Therefore, use IF statements sparingly, please.
The basis for using Excel is to automate a lot of the boring data entry. So, if you have got a table set up, which could be imported from MS Access; etc. You could reference data (within the same file) to different tabs by using:
V-Lookup (Vertical Look-up, which is the most user-friendly thing to use)
My basic V-lookup formula looks like this:
=Vlookup(value you’re looking for, array where the data is contained, column number, false)
However, if the data you look for doesn’t exist; for example, you’ve got a new customer or new cost centre that isn’t on your existing table, it will return a nasty error #N/A. This will affect your overall sum totals.
So, I always have mine modified to using only one IF statement which looks like:
=IF(ISNA(Vlookup formula)<>FALSE,0,Vlookup formula)
What does that mean?!?! It means if the Vlookup returns an #N/A, it will return a value of zero. You can change it to a text string instead of a zero by using ” “. ISNA is essentially returning whether the function is true or false. <> is the opposite of the = sign.
H-Lookup (Horizontal Look-up; I’m not a fan of H-Lookups as it’s not as straight-forward as Vlookup. It’s a very useful one to learn)
Then, there’s Match function.
Dread having to go Ctrl-F or print the whole darn thing and draw lines? No problem. Use the Match function and it will tell you the row number where it is located, depending on the size of your array. If you have different data sources and try to centralize everything in one file, Match() is your friend.
The ones that I’m still trying to figure out and learn is Offset. Apparently, Offset and Match functions when used together becomes a really powerful seeking Excel function.
My other favourites when Vlookup is not practical is the SUMIF function.
I’m looking forward to taking advanced Excel classes!