We're happy to announce that Zoho Sheet has opened up the new dynamic array formulas to all our users! The addition of dynamic array functionality will make your spreadsheets and the formulas you use in them a lot simpler, while at the same time making them more versatile and powerful.

No more will the array functions be something you'll have to "build" separately. Zoho Sheet now allows all formulas to return results in arrays, if the formula demands. How does this work, you ask?

First things first, this update makes it easy to build an array formula. You don't have to use 'Ctrl+Shift+Enter' to be able to get an array for an answer. Secondly, it takes away the need to select the array range you want the answer in. Simply enter the formula in one cell, and the result spills over to the adjacent cells. 

Speaking of spilling brings us to one of the important problems the dynamic arrays functionality solves. When there's already data present in the result array's range, or if new data is entered in the array range after the formula is executed, the formulas throw a #SPILL! error indicating an obstruction. Not just that, but the error comes equipped with tools to troubleshoot as well.

A handy drop down near the error lets you clear obstructing cells or select them and deal with the obstructions manually. Once the obstructing cells are dealt with, the formulas works again like a charm—a neat way to make sure you can make array formulas work alongside a data set.

With dynamic array formulas at work, if you ever wanted a formula that would naturally return an array for an answer to behave like a non-array formula, the @ operator is how you'd do it. All of your existing non-array formulas with potential to return an array result will be kept from returning multiple values as a result with an automatically appended @ after this update.

If you wanted to refer to the result of an array formula in another cell or a formula, specifying the range gets a little tricky. This is because an array formula, unlike a traditional non-array formula, is not bound to a cell-range and is susceptible to expanding or shrinking based on its source. Use an '#' after referring to the first cell of the range so that the referred range expands or collapses along with the actual data range that's referred to. Here's how it works -

We hope you find these new Dynamic Array formulas useful! Let us know what you think in the comments below.