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

    • Kaizen #223 - File Manager in CRM Widget Using ZRC Methods

      Hello, CRM Wizards! Here is what we are improving this week with Kaizen. we will explore the new ZRC (Zoho Request Client) introduced in Widget SDK v1.5, and learn how to use it to build a Related List Widget that integrates with Zoho WorkDrive. It helps

    • Set connection link name from variable in invokeurl

      Hi, guys. How to set in parameter "connection" a variable, instead of a string. connectionLinkName = manager.get('connectionLinkName').toString(); response = invokeurl [ url :"https://www.googleapis.com/calendar/v3/freeBusy" type :POST parameters:requestParams.toString()

    • Possible to connect Zoho CRM's Sandbox with Zoho Creator's Sandbox?

      We are making some big changes on our CRM so we are testing it out in CRM's Sandbox. We also have a Zoho Creator app that we need to test. Is it possible to connect Zoho CRM's Sandbox to Zoho Creator's Sandbox so that I can perform those tests?

    • I Need Help Verifying Ownership of My Zoho Help Desk on Google Search Console

      I added my Zoho desk portal to Google Search Console, but since i do not have access to the html code of my theme, i could not verify ownership of my portal on Google search console. I want you to help me place the html code given to me from Google search

    • Timeline Tracker

      Hi Team, I am currently using Zoho Creator – Blueprint Workflows, and I would like to know if there is a way to track a timeline of the approval process within a Blueprint. Specifically, I am looking for details such as: Who submitted the record Who clicked

    • Primary / Other Billing Contacts

      If you add an additional contact to a Zoho Billing Customer record, and then mark this new contact as the primary contact, will both the new primary and old primary still receive notifications? Can you stop notifications from going to the additional contacts

    • Is it possible to enforce a single default task for all users in a Zoho Projects ?

      In Zoho Projects, the Tasks module provides multiple views, including List, Gantt, and Kanban. Additionally, users can create and switch to their own custom views. During project review meetings, this flexibility creates confusion because different users

    • [Free Webinar] Zoho Creator webinars - Learning Table and Creator Tech Connect Series in 2026

      Hello everyone, Wishing you all a wonderful new year! May 2026 and the years ahead bring more opportunities, growth, and learning your way 🙂 We’re excited to kick off the 2026 edition of the Learning Table Series and Creator Tech Connect Series ! Learning

    • Reply and react to comments

      Hi everyone! We're excited to bring to you a couple of new features that'll make your sprint process simpler. A cloud application brings with it an array of social media features that can be efficiently used in your organizational setup. As an agile scrum

    • Module Customisation - Lookup function not available

      Good evening, Within my business, I can have multiple customers, who have multiple mobile assets. When I set these assets up, I enter information such as vehicle registration, Vehicle identification number (VIN), Unit number, YOM, in addition to others.

    • zoho click, and nord VPN

      Unfortunately, we've been having problems with Zoho Click, where essentially the line cuts off after about a minute's worth of conversation every time we are on VPN. Is there a way we can change this within the settings so it does not cut the line off

    • P & L Sub-categorized accounts

      How can I show sub-categorized Income and Expense accounts on the P & L report?

    • Zoho Calender

      a) does the clanender in zoho project allow you to see the name of the event in the celnder view, it currently says either "Task (1) or "Milestoen (1)" b) Alternatively does the calender in Zoho project integrate with zoho calender?

    • Matching ZOHO Payments in Banking

      Our company has recently integrated ZOHO Payments into our system. This seemed really convenient at first because our customers could pay their account balance by clicking on a link imbedded in the emailed invoice. Unfortunately, we can't figure out how

    • New Enhancements to Zoho CRM and Zoho Creator Integration

      Hello Everyone, We’ve rolled out enhancements to the Zoho Creator and Zoho CRM integration to align with recent updates made to the Zoho Creator platform. With enhancements to both the UI and functionality, This update also tightens access control by

    • Work Type - Limitation

      Hello, I'm setting up work types and have noticed, a limitation on the parts area to 10 lines. Can this be increased to 20 or greater? In addition to this, when I attempt to add the work type to a work order, the correct labour hours doesn't flow through.

    • Locked out of MFA due to changing phones

      I have been locked out of my Zoho Books account since i changed my mobile phone over. I can get the OTP through the ZOHO Books software but i cant confirm using my phone app as nothing appears to authorise. I need to know how to register/set up my new

    • Emails sent through Bigin are not posting in IMAP Sent folder

      I have set up my email to work from within Bigin using IMAP.  I am using IMAP so I can sync my email across multiple devices - phone / laptop / desktop / iPad / etc.  I want all my emails to populate my email client (outlook & iphone email) whether or

    • Turning the page for Zoho SalesIQ: 2025 to 2026

      As we wrap up 2025, we would like to take a moment to reflect on what we set out to achieve this year, what we’ve delivered, and where we’re headed next. What we focused on in 2025 This year was all about strengthening the core of engagement and AI, making

    • Temporary Outage in Zoho Cliq Affecting US Users – July 23, 2025

      We experienced a service disruption in Zoho Cliq that impacted core functionality for users in the US region. The issue occurred between Jul 23, 2025, 06:54:00 PM IST and 07:13:13 PM IST, lasting approximately 19 minutes. To restore service stability,

    • Zoho Cliq not working on airplanes

      Hi, My team and I have been having this constant issue of cliq not working when connected to an airplane's wifi. Is there a reason for this? We have tried on different Airlines and it doesn't work on any of them. We need assistance here since we are constantly

    • Credit card transactions are backwards after importing statements

      I am new to Zoho Books so I'm importing my firm's bank and credit card statements in. My credit card statements have a single column with negative numbers so that is the option I chose. But when I went to categorize the credit card transactions, I can

    • Multi-currency and Products

      One of the main reasons I have gone down the Zoho route is because I need multi-currency support.  However, I find that products can only be priced in the home currency, We sell to the US and UK.  However, we maintain different price lists for each. 

    • Create an Eye-Catching Announcement Widget for Your Help Center

      Hello Everyone! In this week’s edition, let’s explore how to keep your customers updated with exciting news in the Help Center. See how ZylkerMobile wowed their customers by bringing updates right to their portal. ZylkerMobile, the renowned brand for

    • Send Whatsapp with API including custom placeholders

      Is is possible to initiate a session on whatsapp IM channel with a template that includes params (placeholders) that are passed on the API call? This is very usefull to send a Utility message for a transactional notification including an order number

    • Customer Management: #6 Common Mistakes in Customer Handling

      Managing customers doesn't usually fall apart overnight. More often, slight gaps in the process slowly become bigger problems. Incidents like missed follow-ups, billing confusion, and unhappy customers will lead to revenue loss. Many businesses don't

    • Zoho Desk iOS app update: UI enhancement of picklist and multi picklist fields

      Hello everyone! We have enhanced the UI of the picklist and multiselect picklist fields on the Zoho Desk iOS app to provide a more refined, efficient, and user-friendly experience. We have now supported an option to Search within the picklist and multiselect

    • Zoho Desk iOS app update: Revamped scribbles with Apple pencil kit

      Hello everyone! We’re excited to introduce a revamped Scribble experience, rebuilt from the ground up using Apple PencilKit for smooth strokes, proper scaling, and seamless image uploads. Please update the app to the latest version directly from the App

    • Zoho Desk Android app update: Norwegian language support

      Hello everyone! In the most recent Android version of the Zoho Desk app update, we have brought in support to access the app in Norwegian language. We have introduced the Norwegian language on the IM module of the Zoho Desk app as well. Please update

    • Payment Vouchers

      Is there any Payment Vouchers in Zoho? How can we create payment for non-trade vendors, i.e. professional fees, rent, and payment to commissioner income tax?

    • API in E-Invoice/GST portal

      Hi, Do I have to change the api in gst/e-invoice portal as I use zoho e books for my e-invoicing. If yes, please confirm the process.

    • When I click on PDF/PRINT it makes the invoice half size

      When I click PDF / Print for my invoice in Zoho Books, the generated PDF appears at half size — everything is scaled down, including the logo, text, and layout. The content does not fill the page as it should. Could someone advise what causes Zoho Books

    • Search by contain letter in a column

      Hello, everyone I need a filter function that searches by letter in a cell, and it should be a macro. To clarify further, if I have a column with several names and I chose a search cell and what I want is search by a single letter, for example, "a" then

    • Enrich your contact and company details automatically using the Data Enrichment topping

      Greetings, I hope you're all doing well. We're happy to announce the latest topping we've added to Bigin: The Data Enrichment topping, powered by WebAmigo. This topping helps you automatically enhance your contact and company records in Bigin. By leveraging

    • Easier onboarding for new users with stage descriptions

      Greetings, I hope all of you are doing well. We're happy to announce a recent enhancement we've made to Bigin. You can now add descriptions to the stages in your pipeline. Previously, when creating a pipeline, you could only add stages. With this update,

    • Can add a colum to the left of the item in Zoho Books?

      I would need to add a column to the left of the item column in Books. When i create custom fields, i can only display them to the right of the item.

    • Verifying Zoho Mail Functionality After Switching DNS from Cloudflare to Hosting Provider

      I initially configured my domain's (https://roblaxmod.com/) email with Zoho Mail while using Cloudflare to manage my DNS records (MX, SPF, etc.). All services were working correctly. Recently, I have removed my site from Cloudflare and switched my domain's

    • AI Bot and Advanced Automation for WhatsApp

      Most small businesses "live" on WhatsApp, and while Bigin’s current integration is helpful, users need more automation to keep up with volume. We are requesting features based on our customer Feedbacks AI Bot: For auto-replying to FAQs. Keyword Triggers:

    • 2025 Ask the Experts sessions wrap-up : Key highlights from the experts

      Here is a rewind journey of our Ask the Experts (ATE) Sessions, where we brought you expert insights and practical best practices together in one place. This recap highlights the key takeaways, learnings, and best practices from all these sessions so

    • How to disable the edit option in subform

      How to disable the edit option in subform

    • Next Page