Introducing Dynamic Array Formulas to Zoho Sheet

Introducing Dynamic Array Formulas to Zoho Sheet

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.

Dynamic Array formulas; what's new?

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. 

Spills, but doesn't spoil

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.

The @ and # operators to make things easy

The @ operator:

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.


The # operator:

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.

You can connect with us on Twitter to learn more about how to get the most out of Zoho Sheet. Or, you can write to us at support@zohosheet.com.

    • Sticky Posts

    • Announcing IMPORTRANGE and cross-spreadsheet linking in Zoho Sheet

      We are pleased to announce the much-requested IMPORTRANGE function in Zoho Sheet. With this function, you can now link or refer to cell ranges across spreadsheets in an instant.   Refer across spreadsheets You can use IMPORTRANGE just like any other predefined function in Zoho Sheet. Start with an equal sign, select the function, and fill in the parameters. For this function, the source spreadsheet link and the desired cell range to import are given as input.     Once you allow access to the source
    • Tab Colors and New Currency Symbol of Indian Rupee

      Zoho Sheet has recently added support for the following 2 new features: Tab Colors: Since last month, you can set a color to your worksheet tabs. Use colors to quickly identify/highlight specific worksheets or to group similar worksheets together. This option is available on the right click context menu of the worksheet tab. Add color to your worksheets! Indian Rupee Currency - New Symbol: The new Indian Rupee Symbol, ₹, is now supported in Zoho Sheet. This replaces the old "Rs." currency symbol
    • Expanding and securing ways to share your spreadsheet: email as attachment and password protection

      While Zoho Sheet allows you to share and collaborate with your colleagues in real time, you might wish to send your spreadsheets as email attachments or keep an offline version of a confidential file with password protection. Here's how our new options
    • Display text the preferred way with text rotation, indent, and wrapping options

      Working with long, textual data in spreadsheets just got better! Zoho Sheet now supports text rotation, indentation, and wrapping options, which you can use to display the text and other values in your spreadsheet exactly the way you want.   Set the right
    • Dark mode in Zoho Sheet—Work with comfort, night or day

      With working from home becoming more and more common this last year, we at Zoho Sheet understand that conventional working hours for many people across the globe have changed. That's why we're pleased to say we're making it easier for our users to work

            Zoho DataPrep Resources


                Zoho CRM Plus Resources

                  Zoho Books Resources


                    Zoho Subscriptions Resources

                      Zoho Desk Resources

                        Zoho Projects Resources


                          Zoho Sprints Resources


                            Zoho Orchestly Resources


                              Zoho Creator Resources


                                Zoho WorkDrive Resources




                                    Zoho Campaigns Resources

                                      Zoho CRM Resources

                                                  Design. Discuss. Deliver.

                                                  Create visually engaging stories with Zoho Show.

                                                  Get Started Now




                                                      Zoho Writer Writer

                                                      Get Started. Write Away!

                                                      Writer is a powerful online word processor, designed for collaborative work.