"Never was so much owed by so many to so few"
Winston Churchill
I'm not sure that Microsoft fully realised the impact Excel would have on business reporting. In my experience, every company I have worked in relied on Excel to produce key reports (even those with business intelligence systems).
The ability to manipulate and calculate data as well as finely format the resulting report is an awesome combination.
 |
| Unfortunately, this is true for excel |
For me, if you are going to do any sort of reporting in excel, you need to know the following excel formulas.
VLOOKUP and HLOOKUP
These allow you to lookup an identifier in a larger table, then return a single value. The identifier can either be in a column (vlookup), or row (hlookup) and you choose which column or row of the table you want to return as a value.
Use this when...
This is really helpful when you want to reformat something - you can have a data table (perhaps you dumped it from your system) and you want to copy the numbers into your report. Rather than using a direct link (eg "=A15"), you can use a lookup instead. That way, it doesn't matter if the order of your download changes, you'll still get the number.Not good for...
Summarising data. HLookup and VLookup only return the FIRST match that they find. So, if your identifier repeats (say you have a list of transactions and the same GL appears more than once), this won't work.
SUMIF, SUMIFS
These formulas work a lot like VLookup and HLookup, except that they search for ALL instances of the identifier and then return the TOTAL of the values found.
Really cool function, because you can specify criteria (eg, only sum if the identifier is less than a certain value).
Use this when...
You want to summarise a column of values. For example, let's say you have a dump of safety incidents (or a P&L) and you want them summarised by location (or, for the P&L a summary expense category). You put the location or summary category next to each row, then a sumif on that column.
Not good for....
Text. It basically only returns number values, so you can't get identifier descriptions (but for that, you'd use VLookup!).
 |
| Right now, this is you, isn't it? |
Follow the link to get a file with working examples.
As a bonus, there are some other useful formulas in there, so check them out too!
What do you say....what are the excel formulas you can't live without?
What's your feeling on INDEX MATCH vs VLOOKUP - I've been told that it keeps down the file size....
ReplyDeleteThanks for reading - a great question. For me the answer is always to use a formula that all the people who might use, troubleshoot or update a workbook can understand.
DeleteI like Index,Match because it provides flexibility that you don't get with VLookup, but the main drawback is that the formula looks scary to a lot of users - hence, it is often not the best practical solution (mainly because of the potential difficulty for other, less experienced users troubleshooting - I don't like being the only person who can resolve issues in a workbook reliably!).
In regards to file size, I've noticed that index match tends to lead to smaller file sizes, however, this could be because I tend to use them in more specialised circumstances.
Practically, if a workbook I have blows up to anything more than 10mb I tend to rework it (normally by changing the way data is brought in or the way data is moved to the reporting part).
Depending on your application (and the level of formatting) - if you are using 2010/2013 PowerPivot tables connected to a powerview may be a better solution (although these have a size penalty of their own).
Hope this helps!