Summarizing Data in Excel: Pivot or Formulas?

As a finance professional, excel is one of the tools you can never work without. Excel is used for data consolidation and reporting on a day to day basis. We often set up templates that are used for repetitive processes. Sometimes we do have complex reporting that needs to be interactive too. We can use Pivot tables and a variety of formulas to achieve the same result. We will be comparing both these methods today in this quick read. Pivot Tables: This can be considered as a report, unlike any static reports they are very interactive, requires little effort and, fast to prepare.

Users need not audit the formula for accuracy.
Filtering across dimensions is easier.
Data are grouped automatically.
The design can be changed easily with inbuilt templates.

You can go through this tutorial to get an idea of how to create pivot tables Formula Approach: Reporting can be done in excel using a combination of Formulas like,

This methodology is a time-consuming process and would require a person to have intermediate knowledge of the tool. Formulas work out better when the data is complex. Formulas provide the ability to customize the report to the needed output. Pivot charts are rigid and are tied to the Pivot table. SO, if you are charting a requirement, create a report with formulas that provide the flexibility to chart as required. Both methods have their own advantages. While pivots are quick, easy and, comfortable, they limit you to basic reporting. The formula method, though it requires you to start from scratch and put more time into it to get the final result, it is a very effective method when it comes to creating a complex dashboard.