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

    • Sticky Posts

    • Refine by Zia: a smart way to improve data quality

      Zia in Zoho Sheet has been helping users with recommended charts, pivot tables, and offering insights about their data. While there is an exclusive smart data cleaning tool, from now on Zia will also include data quality suggestions for error-free analysis,
    • Webinar: Learn how to instantly clean your spreadsheet data

      Hello! Do you find it tedious to get rid of duplicate entries, inconsistencies, and missing values in your data? Join our webinar and learn the trick to clean up your data in an instant. In this session you'll be introduced to the smart data cleaning tool in Zoho Sheet followed by a live demonstration.  ​ In this session, you'll get to know: What's new in Zoho Sheet About a smart tool to clean your spreadsheets in an instant How to find and remove duplicate records from your data How to get rid of
    • 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

      Zoho SalesIQ Resources

            Zoho TeamInbox Resources

              Zoho DataPrep Resources

                  Zoho CRM Plus Resources

                    Zoho Books Resources

                      Zoho Subscriptions Resources

                        Zoho Desk Resources

                          Zoho Projects Resources

                            Zoho Sprints Resources

                              Qntrl 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 Show Resources

                                                        Zoho Writer Writer

                                                        Get Started. Write Away!

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