Data Analysis Using Excel Array Formulas

Excel array formulas are useful and powerful formulas to summarize your spreadsheet data. You have the option of using a single or multiple criteria to summarize your worksheet data and also create a customized report.
How to implement array formulas in Excel

  • Enter your data and organize it.
  • Next select appropriate ranges and name them as shown in the training video. Named ranges make the use of array formulas easier.
  • Enter your formula.
  • Now press ‘CTRL’+’SHIFT’+’ENTER’ keys simultaneously to display the result of the formula.

In case you forget to press the keys and press only enter, you will see the ‘#value’ error.
You can use functions like sum, average, max, min, etc. in your array formulas.

We have also demonstrated that if you are not comfortable with array formulas you can use the database functions to do similar analysis.

Some knowledge about how the array formulas work in the background

Array Formulas - Sum Product

Array Formulas – Sum Product

Place your cursor in cell E12 where the value ’66’ is displayed. Press ‘F2’ to view the formula. In the formula bar above now select ‘product=”Shirts” ‘ and press ‘F9’. You’ll notice ‘FALSE’ and ‘TRUE’ displayed. Wherever the value ‘shirts’ is available, ‘TRUE’ is displayed otherwise ‘FALSE’ is displayed.

Array Formulas - Sum Blue Shirts

Array Formulas – Sum Blue Shirts

Now select ‘quantity’ and press ‘F9’ on the keboard to display the ‘quantity’ of shirts.


Array Formulas - Sum Shirts

Array Formulas – Sum Shirts

Only where the ‘shirts’ is ‘TRUE’ the ‘quantity’ values are added and you get the result ’66’.
In this last image below you select both ‘product=”Shirts” ‘ and ‘quantity’ and press ‘F9’ to see all the background data.

Array Formulas - Sum Customer

Array Formulas – Sum Customer

Further reading:

How to Summarize Data for Reporting and Analysis in Excel 2003



Leave a Reply

Your email address will not be published. Required fields are marked *