Exceljet

Quick, clean, and to the point

How to see arrays in formulas

In this video, we'll look at a few ways to see or visualize arrays in a formula.

One of the best things about the new dynamic array formula engine in Excel is that it's much easier to see and visualize arrays.

Let's take a look at a few examples.

The first way to see arrays is to enter formulas or expressions directly on the worksheet.

For example, here we have some numbers in the range B5:B14.

In cell E5, I have a formula that squares and then sums the values in the range.

If I want to see the squared values, I can just remove the SUM function and they'll spill onto the worksheet in a dynamic array.

Any change to the source data is immediately reflected.

So, this is one way to see arrays. 

One advantage here is that we can easily see the dimensions of the array directly on the worksheet.

I'll undo that change now.

Another way to see array values is to use the F9 key.

If I carefully select just the range B5:B14, and then press F9, we see the original values.

To undo this step, use control + z. 

Often, you'll want to check the values in an array being passed into a function as an argument.

This means you need to carefully select the entire expression being provided to the function.

The easiest way to make this selection is to use the function screen tip window, which appears when you click inside a function.

Click once to display the tip window, then click the argument you want to look at, then press F9.

You can use this trick in any function.

To back out of the formula completely without making any changes press the escape key.

Let's look at one more example on the second sheet.

On this worksheet, we are counting unique values with a formula that uses both COUNTA and UNIQUE.

Working from the inside out, I'll check 3 values:

The original values in B5:B16

The values that are returned by UNIQUE

And, the final result of the formula.

You can use F9 to evaluate any formula. You'll find it's an invaluable tool to confirm that a formula, or part of a formula, is doing what you think it is.

Dynamic Array Formulas are available in Office 365 only.

Related shortcuts

Author 
Dave Bruns