Identify cells without array formulas

3
votes

I would like to identify cells where an array formula has not been entered. This will help me to identify where an array formula has been 'lost' because I incorrectly exited out of a cell and did not press CTRL+SHIFT+ENTER. Originally I was thinking of using conditional formatting to highlight cells without a curly bracket { or }, but it doesn't seem possible. Any thoughts on another way? Thanks in advance!

2
arraysexcelexcel-formula
AFAIK you won't be able to do the cond. formatting using the bracket as the condition. Excel doesn't place the bracket in the cell itself, so it'll never see one. You can test this by doing an array formula in A1, then using =COUNTIF(A1,"*}*") which returns 0. Are you open to a VBA option? Let's think of the opposite - what is in the cell if the array formula hasn't been entered? If it's empty, you can use IsBlank() to find those that have no formula. – BruceWayne
do you know the syntax of the array formula you entered? and are there many formulas with the same (or similar) syntax in the sheet? – Scott Holtzman

2 Answers

4
votes

You can do this with a named formula.

Select all the cells in the sheet.

Open the Name Manager (Ctrl+F3) and click "New..." button.

For the "Name" field enter: ShowArrays

For the "Refers To" field enter:

=GET.CELL(49,A1)

Click "OK" and then "Close".

Now for Conditional Formatting enter this formula:

=ShowArrays

Note: GET.CELL is a function from the ancient (yet still included) predecessor of VBA, the Excel4 Macro language. Some Excel4 Macro functions can be used inside named formulas in the Name Manager. Argument 49 instructs the function to return True/False of whether the cell is part of an array formula.

1
votes

With a clever IF check, you can get your formulas to self-identify whether they've been entered as an array-formula or not. Here's one I came up with that's fairly concise:

=IF(SUM(1*CHOOSE({1,2},-1,1)), "Error: You must use an array formula (Ctrl+Shift+Enter)", (Array Formula Here))

enter image description here

The way this works is that the CHOOSE formula always returns arrays (or lists) but when entered as a regular formula, all but the first array element is silently chopped off. As a regular formula, it will just return the first number (-1), but as an array formula, it will return a list with both numbers {-1,1}. When SUM acts on formuer, the result is -1, which is treated as "TRUE" by IF (IF actually treats any non-zero values as "TRUE").

When SUM gets the list {-1,1}, it sums the two and returns 0, which IF treats as FALSE, so we know this was entered an array formula, and can proceed to the real formula.