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 arrays 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 the F9 key 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
