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.

    • Recent Topics

    • account in a diferent bas currency

      Is it possible to have an account in currency diferente from base currency?, lets say, the bas currency in japase yen but asset  accut called cash dollars in dollars?

    • Meeting impossible to use when sharing screen

      he Meeting tool in Brazil is practically unusable when sharing anything, whether it’s a presentation or simple navigation. When accessed via Cliq, the situation gets even worse: even basic calls fail to work properly, constantly freezing. And as you are

    • Changing the Default Search Criteria for Finding Duplicates

      Hey everyone, is it possible to adjust the default search criteria for finding and merging duplicate records? Right now, CRM uses some (in my opinion nonsensical) fields as search criteria for duplicate records which do nothing except dilute the results.

    • Is there a way to create a desktop shortcut for a website course portal?

      Hello everyone, I recently got a laptop and bought an online course from a website, Skillwint.com, which I visit regularly. I open that site many times a day and want to create a desktop shortcut so I can open it directly instead of searching in the browser

    • SalesIQ Tip for Admins: Your Safety Net for Data Recovery

      Ever clicked 'Delete' in your SalesIQ and then realized it was the wrong item? Maybe it was a prospect's chat that held important context you needed to refer to later on. Or, maybe you deleted an elaborate Zobot that you were still testing or planning

    • Depreciated mergeAndStore Function Help!

      Hello, I have a function designed to create a PDF containing information from the fields in a Deals record. There is a Writer Mail Merge template in WorkDrive that is populated via Deluge code, and a copy of the resulting PDF is then attached to the record.

    • Can I execute two 'functions' when completing a mail merge from CRM?

      Hi, I have set up a mail merge from CRM Deals to a template. I want a copy of this to be saved in Workdrive, and then a copy also saved back into the deal record from which the merge occurred. I can do both independent of each other, and managed to get

    • External Share > Edit: Cannot Create Zoho Files

      Hi Zoho, When we create an external share link with Edit permission, our external users are unable to create a Zoho file (Zoho Writer, Zoho Sheet and Zoho Show). They can only upload files. They can edit the Zoho files if we create them internally and

    • Zoho Mail iOS app update - RTL languages support and access emails using permalink and universal link, image upload resolution

      Hello everyone! In the most recent version of the Zoho Mail iOS app update, we have brought in support for RTL languages(Arabic and Urudu), providing a seamless reading experience with proper text alignment and layout throughout the app. We have also

    • Why are emails sending with @viazohocrm.com ?

      I just sent out mass emails from CRM. They are sending from the email below and people cannot reply, and they are getting this message: Address not found Your message wasn't delivered to sales.XXXXXXXX.com.au@viazohocrm.com because the address couldn't

    • Duplicating and referencing datasets

      I am moving from PowerBI to Zoho Analytics and while I find Zoho easier to use in many ways, there is one function that I use in PowerBI that I have not been able to find in Zoho.   I have several data sets that I need to modify in different ways to get

    • Zoho Inventory Now Supports VeriFactu for Businesses in Spain

      Starting from January 1, 2026, Spain requires real-time invoice reporting for all B2B transactions. From July 2026, this requirement will extend to B2C transactions as well. All reporting must be carried out through the VeriFactu to AEAT (Agencia Estatal

    • Enhancements to Zoho Map integration tasks

      Hello everyone, We're excited to announce enhancements to the Zoho Map integration tasks in Deluge, which will boost its performance. This post will walk you through the upcoming changes, explain why we're making them, and detail the steps you need to

    • Let’s Talk Recruit: Meet Zia, your all-in-one AI assistant (Part-3)

      Welcome back to the Let’s Talk Recruit series. In the part 2 post, we explored how Zia has evolved with smarter summaries and seamless AI-assisted content creation. This time, we’re diving into the latest upgrades that take productivity even further —

    • Announcing Early Access to "Zoho CRM for Everyone" — A new and exciting update to Zoho CRM

      Update : Zoho CRM For Everyone's Nextgen Interface gets an upgrade! Hello everyone, We’ve updated the Zoho CRM for Everyone Nextgen interface based on your feedback. The UI is now simpler with a unified sidebar, a more visible global search and features

    • Unable to verify domain for Zoho People

      I have added TXT records in my DNS (GoDaddy) [screenshot attached], but unable to verify even after 12 hours after adding. On checking the console, it shows some errors [screenshot attached]. It appears that the verification flow is broken - please provide

    • Zoho Invoice Now Supports VeriFactu for Businesses in Spain

      Starting from January 1, 2026, Spain requires real-time invoice reporting for all B2B transactions. From July 2026, this requirement will extend to B2C transactions as well. All reporting must be carried out through the VeriFactu to AEAT (Agencia Estatal

    • Zoho Billing Now Supports VeriFactu for Businesses in Spain

      Starting from January 1, 2026, Spain requires real-time invoice reporting for all B2B transactions. From July 2026, this requirement will extend to B2C transactions as well. All reporting must be carried out through the VeriFactu to AEAT (Agencia Estatal

    • Weekly Tips : Make your email content error free with Spell check in Zoho Mail

      As someone who writes multiple emails a day, we are bound to make both grammatical and spelling errors. Most of them we may not even notice, and even if we do, we may not have the time to correct each one individually when we are in a hurry. So how do

    • Introducing Enhanced Storage Management

      We’re excited to roll out two new enhancements in Zoho Recruit, Subscription Information and Storage Management — designed to give admins complete visibility into subscription details, feature limits, and storage consumption — all from one place inside

    • how to download all my files

      We are in the middle of zoho docs to zoho workdrive migration. I can not access my zoho docs page. I get redirected immediately to a zoho workdrive page. I would like to download all my files so that I have a backup in case something goes wrong with the

    • Convert Zoho Books SalesOrder - Invoice

      Converting a Zoho Books Sales Order into an Invoice Using the SalesOrder Convert API Hello Zoho Developers, Today, we are sharing a simple and effective solution to convert a Zoho Books Sales Order into a Zoho Books Invoice using the SalesOrder Convert

    • Zoho Expense - Report Templates

      Hi Expense Team, I do a recurring trip once every week which has the same cost: Per diem Milage Toll Payment It would be great if there was a feature which allowed me to clone a previously submitted report or create a template reports. This would save

    • How to add tabular data when execute Add Record API

      Hi everyone! I was send api updateRecord follow by this request url : https://people.zoho.com/people/api/forms/json/<formLinkName>/updateRecord with parameters "inputData", "tabularData", "recordId". Ex picture below And response success: But. When I

    • Tip #51- Centralized Contact Management for Better Remote Support- 'Insider Insights'

      For our final topic of November, we’re diving into one of the most helpful features in Zoho Assist, the Contacts section on Zoho Assist;s dashboard. Whether you’re managing multiple clients, tracking past sessions, or simply looking to streamline your

    • Using gift vouchers

      We would like to be able to offer a limited number of gift vouchers, of varying values, to our customers, and are looking for the best way to do this. We have looked at Coupons and Gift Certificates, but neither seem to fit the bill perfectly. Coupons:

    • Say Hello to Telephony in Zoho FSM

      Zoho FSM now brings complete telephony support so your team can manage inbound and outbound calls without switching tabs. Faster responses, smarter routing, and total call visibility—all in one place. Choose the telephony setup that works for you Zoho’s

    • E-Invoicing in Belgium with Zoho Inventory

      Starting January 1, 2026, Belgium is introducing mandatory electronic invoices (e-invoicing) for all B2B transactions between VAT-registered businesses. Invoices and credits notes must be exchanged in a prescribed digital format. How E-Invoicing Works

    • Employee ID is not correct in get records api V2 for leave

      Hi Team, We are facing an issue when calling the Get Records API V2. The employee.id returned in the response is not the actual employee record ID in Zoho People. As a result, our integration is forced to make an additional Get Record API call for every

    • Formatting Mailing Labels

      I want to use the "Print Mailing Labels" function on the drop down list, but I am not seeing a way to change the formatting on the mailing labels. At the moment, the information that appears on the mailing labels ARE NOT mailing addresses, but random

    • Zoho People. Updating TabularData

      I am trying to update tabular data in the record. I always have the same response. I have checked many times. Section ID is correct. May be something wrong with request structure itself. Can someone help me. Body content type: form urlencoded query params

    • Canvas View - Print

      What is the best way to accomplish a print to PDF of the canvas view? Latest update (27th November 2025): We are excited to announce that the Canvas Print View is now available! We encourage you all to try it out and share your feedback with us. Learn

    • Zia Conversation Summary: Context at a glance for every customer interaction

      Hello everyone! Every customer conversation tells a story—but in CRM, that story is rarely in one place. A sales rep moving between multiple leads has to reopen long email threads, check call remarks, and revisit meeting notes just to remember what was

    • How to set a fixed width of column/cells

      Is there a way to set a 'fixed' width for cells in zoho sheet?   I know how to adjust the size of the colums.... but after I past a large block of text into a cell, it expands the cell size again. (yes, I have turned off word wrap)   How can I set a fixed width so the cells don't change size each time I add data to a cell?   Thanks.

    • Workdrive Oauth2 Token Isn't Refreshing

      I have set up oauth for a bunch of zoho apis and have never had a problem with oauth. With workdrive i am using the exact same template i usually use for the other zoho apps and it is not working. All requests will work for the first hour then stops so

    • Single Portal for Multiple Apps

      Hello, I'm just getting started with Zoho and I'm very overwhelmed. I am currently using the free trial of Zoho One, but if I can figure it out, I intend to upgrade to the paid version. Zoho One, of course, gives me  access to an entire suite of services/applications. One of the things I'd like to do is have a single place for clients (customers?) to log in and view current projects, invoices, contact information, etc. A single login for my clients. A single portal. I've come across documentation

    • 【開催報告】 大阪 ユーザー交流会 2025/11/20（木）Zoho CRM Plus 活用のアイデアとデータの“見える化”

      皆さま、こんにちは。コミュニティチームの中野です。 11/20（木）に、大阪 ユーザー交流会を開催しました。本投稿では、その様子をお届けします。 当日の登壇資料などもこちらに共有しますので、参加できなかった皆さまもご参照ください。 （Zoho 社員セッションの登壇資料については、11/28(金)に開催予定の東京回の開催報告で共有いたします） 今年2回目の開催となる大阪 ユーザー交流会では、株式会社KDDIウェブコミュニケーションズ 山田さんによる Zoho CRM Plus の事例セッションのほか、「Zoho

    • Every rating counts: Shaping customer experience

      We are back to that beautiful time of the year. It is the season to reflect, be thankful, and appreciate everything that has happened throughout the year. Thanksgiving is a time we connect with our family, friends, and relatives to strengthen relationships,

    • How to remove duplicate contacts

    • Custom Field for Subscription

      Hi, I can't find a way to add a custom field (to contain a license key generated from our software) against a subscription? Is the only place to add this information in the Invoice module (as custom field for invoice)? When a customer views his subscription via the customer portal, there appears no way to display a license key for them? The invoice is not the natural place to store a license key for a particular subscription, so where else can this be stored and displayed?

    • Next Page