What is an array in Excel?

Basically, an array is a set of items. The items can be text or numbers and they can occupy in a single row or column, or in multiple rows and columns. An array is only a list or range of values.

What is an array formula in Excel?

Array formulas in Excel are a very powerful tool and difficult to master. A sole array formula can carry out multiple calculations and update numerous of usual formulas. Still, many users have never used array functions in their worksheets because it is one of the maximum tough to grasp.

An array formula is a formula that could perform multiple calculations on one or more items in an array. Array formulas can give either multiple results, or a single result.

This feature is available in all versions of Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 and lower.

Simple example of Excel array formula

Assume you have some items in column B, their prices in column C, and you want to calculate the grand total sales. You can do this by calculating subtotals in each row first in a traditional way as simple as =B2*C2 and then sum those values:

However, the same can be done using an array formula. So, it simply takes a single array formula and 2 brief steps:

  1. Firstly, Select an empty cell and enter the following formula in it:

=SUM(B2:B6*C2:C6)

  • Then, use the keyboard shortcut CTRL + SHIFT + ENTER to complete the array formula.

On doing this, Microsoft Excel covers the formula with {curly braces}, which is an indication of an array formula.

The formula multiplies the values in each individual row of the specified array (cells B2 through C6), add the sub-totals together, and output the grand total.


Why use an array formula in Excel?

  1. Array formulas in excel are the most handy tool to perform sophisticated calculations and do complex tasks.
  • In Excel, an Array Formula permits you to do hard calculations on one or more value sets.
  • Functions like conditional sums, lookups & linear algebra can easily be done with Array Formula. It’s instrumental for data analysis and matrix math.
  • You can use array formulas to do complex tasks, such as:
  • To quickly create sample datasets.
  • To sum up the number of characters in a range of cells.
  • To sum only numbers that meet certain criteria’s, such as the lowest values in a range, or numbers that fall between an upper and lower boundary.
  • To sum every Nth value in a range of values.

How to use array formula in Excel


As you are aware, the combination of the 3 keys CTRL + SHIFT + ENTER is a key that turns a regular formula into an array formula.

When entering an array formula in Excel, keep 4 important things in mind:

  1. Once you are done with typing the formula and concurrently pressed the keys CTRL SHIFT ENTER, Excel automatically surrounds the formula between {curly braces}. When you select such cells, you can see the braces in the formula bar, which will give you an idea that an array formula is there.
  2. Manually typing the braces surrounding a formula will not work. You need to press the Ctrl+Shift+Enter shortcut to complete an array formula.
  3. Every time an array formula is edited, the braces will disappear and you must press Ctrl+Shift+Enter again to save the changes.
  4. If you forget to press Ctrl+Shift+Enter, your formula will behave like a normal formula and will process only the first value in the specified array.

Array Formula is a unique type of formula and are often referred to as CSE (Ctrl+Shift+Enter) formulas because instead of just pressing Enter, you press Ctrl+Shift+Enter to complete the formula.

Leave a comment

Your email address will not be published.