Skip to main content

Working with Array Functions

The 'Get' selections in the SF menu (buttons in the ‘Get Data’, ‘Get Summary’ and ‘Get Transfers’ areas) often return functions that span more than one cell. These are known in Excel as "array functions." The function is returned enclosed within curly braces "{}" and it spans more than one cell.

Please note the following, when working with the array functions through the menu dialog boxes that create formulas and paste the results in the spreadsheet cells:

  • The output cell referenced in the dialog box is the upper left-hand cell in the array function.
  • The array fills to the right and downwards from that cell.
  • If the output of the function configured by the dialog box overlaps the output of another array function, an error message appears stating that you cannot change part of an array. If this happens, change the output cell to avoid overwriting other occupied cells.
  • When a function is already on the spreadsheet, you can do edit operations in several ways. You can right-click on the cell with the function to open a menu of available function options. To use the menu, see the Updating a Spreadsheet \topic.

Create an Array When You Know the Number of Cells​

To create an array function without using the menu options when you know the number of cells that the array will use:

  1. Select the range of cells that the array function will use.

  2. Press F2 to edit the first cell in the array.

  3. Enter the formula and press Ctrl+Shift+Enter.

    This action copies the change to each cell in the selected range and executes the function.

Create an Array When You Don't Know the Number of Cells​

To create an array function without using the menu options when you do not know the number of cells that the array will use:

  1. Select two adjacent cells to act as a placeholder for the array.

  2. Press F2 to edit the first cell in the array.

  3. Enter the formula and press Ctrl+Shift+Enter.

    This action copies the formula to both cells in the array and executes the function

  4. Right-click either cell in the array and select Recalculate (Resize) SF formula to populate the array.

    This can change the size of the array.

Edit a Function​

To edit a function:

  1. Select a single cell in the array.

  2. Press F2 or double-click the cell to begin editing the active cell.

  3. Edit the formula.

  4. press Ctrl+Shift+Enter.

    This action copies the change to each cell in the selected range and executes the function.

  5. Right-click any cell in the array and select Recalculate (Resize) SF formula to populate the array.

    This can change the size of the array.

Delete a Function​

To delete a function:

  1. Select all cells in the array.

    or

    Right-click a cell in the array and select the Select SF Formula option.

  2. Press Delete.