Tips for Working with Array Formulas and Functions in Excel

ARRAY Formulas in Excel

Array Formulas and Functions are very powerful tools to use in Excel. However, the concept of working with ARRAYs take a little bit of time and practice. In this video tutorial, I demonstrate how to:

Create formulas that use ARRAYS as arguments
Work with ARRAY FUNCTIONS – for example, the TRANSPOSE Function
Key “Got’cha” steps to master – for example, selecting all cells to receive formula results before creating the ARRAY Formula.
The importance of using Ctrl+Shift+Enter to complete the Array formula.

What is an ARRAY?

“An Array is a collection of Cells or Values that Excel treats as a single unit.”

Why Use an ARRAY Formula?

Automatic Level of Protection for Formula Cells – You cannot delete nor edit a single cell in an Array Formula
Eliminate Intermediary Calculations – For example, you can find the Grand Total without having to create a field to calculate “Extended Price.”
Worksheet, usually, calculates faster because you are using fewer formulas.