Analysing data in Excel

Description
In this post I have collected a handful of techniques for working with data in Excel: looking up values in tables, matching text or numbers in the lists and extracting corresponding values from neighbouring columns, querying specific data, aggregating the amounts based on certain condition or a number of criteria, making lists and summaries, doing statistical analysis based on these criteria etc.

From financial modelling standpoint, these techniques will help collect and analyze data at the due diligence stage (I am giving a practical example of how to make an extract from somewhat a messy accounting system of a fictitious investment target). If you are an in-house accountant or analyst, the methods I am going to describe will help you make even the existing system more informative and analytical with no additional resources (I will give an example on this too).

I have tied my examples to the automotive industry (in which I have spent quite some years). But these examples are very generic and flexible and can certainly be adjusted to any other industry or specific analytical requirements.

The techniques make extensive use of the SUMPRODUCT, INDEX, MATCH and other functions in a somewhat non-traditional setting. Some of the formulas used are array formulas.