Unlike standard formulas, which typically work on single values or cells, array formulas can perform multiple calculations on one or more items in an array. With array formulas, you can reduce manual effort needed and ensure consistency across rows and columns.
Read the array formulas community post to learn more.
Spill capabilities
With array formulas, there are now spill capabilities in spreadsheets and embedded tables which allow a return of multiple values from a formula.
Note: The spill operator (#) isn’t supported in Workiva.
Array formula examples
Roll-forward table automation with VSTACK()
You can create roll-forward tables using =VSTACK(PriorPeriodRange, CurrentPeriodRange). This formula dynamically combines periods and the table will automatically update when the source data changes.
Learn more about VSTACK.
Remove duplicates with UNIQUE()
Use =UNIQUE(DataRange) to instantly extract a list of distinct values from a column or range. This is especially helpful for cleaning data or creating dropdowns without repeated entries. The formula updates dynamically if the source data changes, so you’ll always get the most current list of unique values.
Learn more about UNIQUE.
Filtering large data sets with FILTER()
Use =FILTER(DataRange, AmountColumn > 1000000) to automatically pull only the rows that meet your criteria. With this formula, you don’t need to reapply filters or delete rows since the view will remain accurate.
Learn more about FILTER.
Array formula functions
Here are the array formula functions supported in Workiva spreadsheets.