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

      • Feature enhancement: Highlight rows based on a cell value

        Hello Sheet users, We're excited to announce a new feature enhacement, shaped directly by your valuable feedback! As you might know, conditional formatting is a great tool for anyone dealing with large data sets. Previously, if you’ve ever wanted to draw

      • Create and save user-specific filter views with Zoho Sheet

        The filters in Zoho Sheet have become even more collaboration-friendly. Previously, whenever you filtered any data, the filtered view will be updated for all the spreadsheet collaborators. This disturbed the data for others working on the same file. With this new update, you can apply data filters without altering the view for other collaborators in the file.   Collaborate with user specific filters   Let us say you are working on a sales report spreadsheet in real time with two remote colleagues. If

      • Revamped Print in Zoho Sheet—customized for paper

        The latest enhancements to Zoho Sheet's Print settings allows you to print a worksheet or a selected range, with customizations that make the data comprehensible, even on paper.   Customize header and footer Make your spreadsheets meaningful with a relevant header and footer. You can now choose to display the file name, sheet name, current page number, total pages, date, and time as the header and footer in your print view. Besides the predefined elements, you can also give a custom text for header/footer.

      • Slicers are now available in Zoho Sheet—filter your data interactively

        At Zoho Sheet, we diligently track user requests and feedback. In line with this, based on extensive user requests, we've integrated Slicers to pivot tables and are delighted to announce its release. Slicers are interactive visual filters that have add,

      • Check out the new Checkbox in Zoho Sheet

        Checkbox, the simple GUI widget that lets you choose between two mutually exclusive options i.e Yes or No, is now in Zoho Sheet. Having made it across the web, Checkbox is familiar but is still elusive to most spreadsheet software. Special developer ribbons, macros and all other complexities to add a checkbox to a spreadsheet? Not in Zoho Sheet. How to add Checkbox to Zoho Sheet? Adding a Checkbox in Zoho Sheet is simple. Select a cell/range and choose "Checkbox" in "Input Controls" under the Format

        • Recent Topics

        • Zoho ONE Inventory and Zoho FSM

          Zoho ONE Inventory sees a warehouse just as a warehouse. You have to be a ZOHO ONE usher for example to use a warehouse like counting stock while you have to buy a warehouse as an Add one separately from ZOHO ONE Inventory to use it for ZOHO FSM In ZOHO

        • ZOHO ONE Inventory and ZOHO FSM

          Zoho ONE Inventory sees a warehouse just as a warehouse. You have to be a ZOHO ONE usher for example to use a warehouse like counting stock while you have to buy a warehouse as an Add one separately from ZOHO ONE Inventory to use it for ZOHO FSM In ZOHO

        • Making post copy bold and other limitations

          Hello, Posting through Zoho Social, copy that I make bold in the post copy field appears differently in the actual post. eg: 'Exciting UnLock News!' is supposed to be in Bold San-serif, however it displays as bold Serif font instead. Can this be fixed?

        • Zoho CRM Functions 53: Automatically name your Deals during lead conversion.

          Welcome back everyone! Last week's function was about automatically updating the recent Event date in the Accounts module. This week, it's going to be about automatically giving a custom Deal name whenever a lead is converted. Business scenario Deals are the most important records in CRM. After successful prospecting, the sales cycle is followed by deal creation, follow-up, and its subsequent closure. Being a critical function of your sales cycle, it's good to follow certain best practices. One such

        • Auto-sync field of lookup value

          This feature has been requested many times in the discussion Field of Lookup Announcement and this post aims to track it separately. At the moment the value of a 'field of lookup' is a snapshot but once the parent lookup field is updated the values diverge.

        • How do I update custom fields for a contact via the API using the List Management API

          I have added a new contact via API, which is just the email address. I now need to add first name, last name, and a few other custom fields (and their associated data). I can't seem to find an API that allows me to update contact information?

        • Spell Checker in Zoho desk

          Is there a way to set always check spelling before sending? Outlook does this and it is a handy tool to avoid typos

        • Offline for Maintenance Option

          Making changes to a live application can cause problems. I'm suggesting a new application settings option to temporary disable all access to an app so the admin or developer can make changes without any user interference. Additionally, while the app is offline, there should be some ability to present the users with a customized notification page (perhaps a view within the app itself). For example, "This application is undergoing maintenance and will be back online Sunday at 5 PM. Please email smith@net.com

        • Zoho Creator delete validation seems like does not support <br> html code

          Validation Workflow in Create or Edit, We can use this styling code eg: <br>, <b>, <u> Those very simple code in those validation (in Create or Edit) But, for Validation (In Delete) Zoho Creator seems like does not support it. The alert task just shown

        • Amazon Integration

          Hi, I am seller on Amazon , & I would like to sign up for Zoho books. However my question is can we automate/integrate invoicing, charges and returns in amazon with Zoho using API? Do you have a developer for this? I did take a look at zapier however it just has a create Invoice function nothing else.

        • Connector working as expected but not when called from an extension

          I am playing around with building a Zoho CRM extension and seeing if anyone has some insight on the below issue I am facing. When I execute the Connector API it works as expected returning me the download URL for a merged document that has merged successfully.

        • Is it possible to set a default field value in a Layout Rule?

          I would like to be able to set a default field value based on a layout rule, rather than a default value in the main layout screen. Example: if Field X is set to 'Banana' then set the default value of Field Y to 'yellow' if Field X is set to 'Strawberry' then set the default value of Field Y to 'red' Is this possible? I don't want to use a workflow rule because a) we've hit our limit and b) it needs to happen straightaway whilst the user is on that screen.

        • Changing Data on Cancelled /Paused Subscriptions

          What's a good way to edit a cancelled subscription without reactivating it? There are some custom fields and data that we'd like to add to these but when we edit it, it reactivates it and creates an invoice. Also need to be able to do this for paused

        • Portal users send notifications to licensed usera

          Is there a way for portal users to leave messages or notifications to licensed users?

        • Help me to retreive my Document

          Please help me to retrieve my documents from any date between 1st February, 2025 to 20th,March 2025 .it got mistakenly deleted on the 21 of March 2025 due to phone screen malfunction I earnestly await your positive response .thank you

        • Zoho Subscriptions -- Zoho Commerce integration

          Is there integration between Zoho Subscriptions and Zoho Commerce? I would like to create subscription plans in Zoho Subscritpions and list them for on my Zoho Commerce store.

        • Inserted Records not showing in Kanban view

          When insert a new record into a report from a workflow, it doesnt show in KANBAN view. When i view the report in a regular list view, the records are there. If i edit the record (while in list view) and then update it at all (even if i dont change anything,

        • Zoho creator dropdown option update using deluge

          Hi how can i add options in dropdown rather than updating manually

        • Add an Iterator Module to Zoho Flow

          Hi Zoho Team, I hope you're doing well. We would like to request a new feature in Zoho Flow: an Iterator module. An iterator is a special type of module that converts an array into a series of bundles, outputting each array item as a separate bundle.

        • Restrict form submissions based on records of another form

          Hi, is there a way to restrict submission of one form based on records of another form in creator? For example, there are two forms: one to collect user registration details, and one for a registered user to submit information. If there is not a record

        • Client Script | Update - Introducing Subform Events and Actions

          Are you making the most of your subforms in Zoho CRM? Do you wish you could automate subform interactions and enhance user experience effortlessly? What if you had Client APIs and events specifically designed for subforms? We are thrilled to introduce

        • Canvas Email iMap

          Hi, I want to be able to have this option (seen below) in our custom Canvas design I'm building. But I don't see that option. Being able to see all related emails is important for us. But in our Canvas, it doesn't seem to have any option....

        • Search Mail with URL parameters

          Is it possible to search Zoho Mail by passing URL params like we can in Gmail? eg. mail.google.com/mail/u/0/#search/from:(jane@doe.com)

        • Renaming organization does not reflect on all places like billing and others

          I have renamed the organization name to Novizna from Evnesoft but in billing and email it still displays Evnesoft

        • Forwarding email into Desk - DMARC errors

          Hi, Our email domain is hosted through Exchange Online (Office 365). Customers email support@ourdomain.xyz which is delivered to Exchange. Exchange rewrites the TO email header to the email address associated with our Zoho Desk (support@ourdomain.zoho.xyz).

        • ASAP Chrome Extension not loading

          We have ASAP enabled in Zoho Desk. I installed the ASAP Chrome Extension (Windows 10), but when I click the extension button while on our site, it never fully loads. I just get what's shown below.

        • Why is the Zoho portal not mobile friendly?

          I just got started with the portal and im sort of shocked how it looks. I already filled a hint/complaint about this. On the website you see a wonderful site with a modern design. Yet in real life its a mid 2000 look and feel. One major issue i see thats

        • Unable to access Zoho Help Community on my main browser

          Hi, I have been unable to access https://help.zoho.com on my chrome browser for a while now. Initially I thought it was a glitch from Zoho, but when I accessed from incognito mode, I was able to access it. I have cleared cache - cookies, but still access

        • Controlling Lookup Paths

          When building pivot tables, Zoho automatically selects 'lookup paths' for you based on which columns are configured. Sometimes, I want zoho to not use a look-up at all between two tables.... but that doesn't seem to be an option, as long as any relationships

        • Can you sell Subscriptions using Zoho Commerce?

          In addition to physical products and the apparently coming soon 'Digital Products', it is possible to sell Subscriptions using Zoho Commerce?

        • Does Thrive work with Zoho Billing (Subscriptions)?

          I would like to use Thrive with Zoho Billing Subscriptions but don't see a way to do so. Can someone point me in the right direction? Thank you

        • Free Webinar Alert! Building Your Brand: Solopreneur Tips to Use Zoho Mail

          Hello Zoho Mail Community! We’re excited to invite you to an exclusive session: "Building Your Brand: Solopreneur Tips to Use Zoho Mail." In this webinar, we’ll explore how Zoho Mail helps solopreneurs create a professional brand with custom email domains,

        • Tip 24: How to generate a file preview in a report without downloading it.

          Hi folks, This month we will teach you how to preview a report file before sending it to someone or sending it for approval. While viewing reports in Zoho Creator, users cannot access or preview files uploaded through the File Upload field without downloading them.    In this tip, we'll show you a way to do this. Key concept: The best way to achieve this function is to preview the uploaded file's content using the Add Note field in a stateless form (pop-up window) based on the record ID.   Use case:

        • Zoho Books CREDIT LIMIT is completely USELESS due to a BUG!!! Please fix it ASAP!!

          Credit Limit should not be taken into account if payment terms on the Invoice are without credit. If selected Credit 0 days (Prepayment) why in this world would a notification pop up saying credit limit is exceeded and not allowing to create an invoice?

        • Add additional field to quick search results

          IN the advanced search, we can add any field to the columns. In the regular search results (before you press enter, there is no option to modify the results. It would be super useful to include a custom field where it currently displays the pipleine

        • Zoho CRM API, Python SDK v7 Quoted_Items

          Hello. How do I use this SDK to retrieve the Quoted_Items from a Quote and downstream the items in a Sales Order I can see references to a constant INVENTORY_MODULES_ITEMS = ["invoiced_items", "quoted_items", "purchase_items", "ordered_items"] But I cannot

        • Continue after error for each loop on invoke url

          Hello. I'm trying to upload files to workdrive using invokeurl. It goes through a list of urls using for each. Sometimes the file is larger than 5mb. The whole workflow stops in that event. I'd love a work around to upload larger files, but I don't think

        • Modify the default layout of how task fields are ordered in Project View

          Hi all, I am trying to change the order the columns on project List view when a project is created initially. See image below. Currently, when a project is created, a predefined collumns are created and also its orders. I am aware that I can "hide", and

        • Enhanced Column Customization in Zoho CRM Email Templates

          Dear Zoho CRM Team, I'm trying to create a footer in the Zoho CRM email template based on a specific design, but I’ve encountered limitations with the current WYSIWYG editor. Currently, the editor only allows adding preselected column structures with

        • Zoho Flow Doesn't Detect Desk Custom Field Change

          I have a Flow that is configured to be triggered when a custom field on a ticket changes. I also have a Schedule in Desk that runs a script that changes the custom field. When I change the custom field manually in the Desk interface, the Flow runs as

        • Next Page