Analysing data in Excel
Originally published: 22/04/2019 07:36
Last version published: 08/04/2021 07:51
Publication number: ELQ-52984-6
View all versions & Certificate

Analysing data in Excel

A set of techniques for working with data in Excel

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.

This Best Practice includes
1 Excel file

Andrei Okhlopkov offers you this Best Practice for free!

download for free

Add to bookmarks


Further information

Calculating statistical measures of data based on various criteria

Tables with data which needs to be analysed based on certain conditions and criteria.



keyboard_arrow_downShow all


More Best Practices from Andrei Okhlopkov

See all

Any questions on Analysing Data In Excel?

The user community and author are here to help. Go ahead!

4.7 / 5 (45 votes)

please wait...