Search | Arrays

This command selects all cells within the current selection that form part of arrays, including data tables. Array formulas are a special kind of Excel formula.

Why would you want to use it?

Arrays are among the most complicated formulas that a workbook can contain and as such should be examined and tested particularly thoroughly.

Data tables often constitute the primary output of a model and are hence of particular interest to you.  They also cause Excel to iterate and can therefore slow down the calculation speed of your workbook.  You can actually set the calculation option in Excel so that it does not automatically recalculate data tables, which is another reason why you might want to inspect them carefully.

Arrays and data tables are protected by Excel so that it is impossible to modify a single cell within an array, or the body of a data table, in isolation.  This is because all cells that constitute a particular array, or the body of a particular data table, must have a common formula.  A consequence of this is that Excel will refuse to insert and delete rows and columns through arrays or data tables.

By contrast, OAK provides replacement insert rows / columns and delete rows / columns commands which will work through arrays and data tables.  A user might wish to use the OAK4 | Search | Arrays command to locate, and then examine, all arrays and data tables before carrying out such an insertion or deletion.

How to use it

Either select the range of cells you suspect contains an array or select just a single cell to find all the arrays on the worksheet.  Then choose the OAK4 | Search | Arrays command from the menu; the command will either select those arrays, including data tables, that intersect the current selection or return a “No array cells found in selection” message.

What you could do if you didn't have OAK

At first sight there seems to be an overlap between the functionality offered by OAK’s Search | Arrays command and the current Array option of the Go To | Special command built into Excel.  But they are not quite the same.

To recap, the Excel Go To command will identify and select the whole of any array formula of which the current active cell forms a part.  It can be reached by pressing F5, clicking on the Special button at the bottom of the dialog which then appears, and choosing Current array from the options that are offered.  The shortcut is Ctrl+/.

Regardless of how many cells are initially selected, Excel’s Go To command will only consider the active cell.  Since that cell can only be part of one array formula, Excel will only highlight a single array (or report that the cell is not in fact part of an array).  By contrast, the OAK4 | Search | Arrays command follows the One-cell rule which leads it to consider potentially many cells in its search.  It can locate potentially many arrays on the worksheet.  In the extreme case where only one cell is selected, it will identify all the arrays on the worksheet.

The Excel Edit | Go To | Special command is particularly useful when there is knowledge of the existence of an array, and there is some initial indication of its location within the workbook.  On the other hand, the OAK4 | Search | Arrays command can be used to locate numerous arrays within a workbook, without the benefit of prior information about where they are located.

As it confines its attention to the selected cells, OAK will only identify those cells that both form part of an array formula and are within the initial selection.  Some cells might form part of the same arrays but lie outside the initial selection.  OAK will not highlight them.  For this reason, it is common to use the OAK command first to locate a worksheet’s arrays, and then the Excel command after that to understand the exact extent of any of the individual arrays.