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

    • Introducing a smarter, faster, and more flexible charting experience

      Hello Zoho Sheet users, We're delighted to share the latest news about a major update to charts in Zoho Sheet! The new version supports dynamic data ranges, granular styling options, faster loading, and other interesting enhancements that allow you to

    • What's new in Zoho Sheet: Simplify data entry and collaboration

      Hello, Zoho Sheet community! Last year, our team was focused on research and development so we could deliver updates that enhance your spreadsheet experience. This year, we’re excited to deliver those enhancements—but we'll be rolling them out incrementally

    • New Features: Repeat Last Action, Insert Cut/Copied Rows/Columns and Hyperlink

      You might have noticed the constant updates to Zoho Sheet of late. Here are 3 more features that have been added to Zoho Sheet recently: F4 - Repeat Last Action Insert Cut/Copied Rows and Columns Insert Hyperlink Here is a screen cast demonstrating each of these features. Read further below to learn more about these new features. F4 - Repeat Last Action: You can now repeat the last action you made on your spreadsheet by using the keyboard shortcut, F4. It is quite handy and helps you get your work

    • Introducing Forms in Zoho Sheet

      We hereby bring you the power of ​forms in Zoho Sheet. ​Now, build and create your own customized forms using Zoho Sheet. Be it compiling a questionnaire or rolling out a survey, Zoho Sheet can do it all for you. Forms is an excellent feature that helps you collect information in the simplest of ways and having it in Zoho Sheet takes it a notch higher. Build Simple yet Powerful forms Building forms using Zoho Sheet is fairly simple. The exclusive 'Form' tab lets you create one quickly. Whether you

    • 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,

    • Recent Topics

    • URGENT. Recovering email without eArchive

      Hello, I have deleted some email from my trash but do not have eArchive. Is it possible to recovery without this? many thanks!

    • filter on sheets mobile (iOS, iPadOS) does not work

      re-posting this as a question because reporting as a problem netted zero responses... I have this issue on every spreadsheet, whether imported or created natively in Zoho Sheets. I can use on desktop without issue: but on iOS the filter dropdowns are

    • How do you print a refund check to customer?

      Maybe this is a dumb question, but how does anyone print a refund check to a customer? We cant find anywhere to either just print a check and pick a customer, or where to do so from a credit note.

    • Zoho Books' 2025 Wrapped

      Before we turn the page to a new year, it’s time to revisit the updates that made financial management simpler and more intuitive. This annual roundup brings together the most impactful features and enhancements we delivered in 2025, offering a clear

    • US State abbreviations in Address fields

      In regards to all Address fields within Zoho, Is there a way to change the State field to be the 2 letter abbreviation vs the full spelled out US State name? Example: "Washington" should be WA. I am able to type in the abbreviated state, but it's not

    • Zoho Booking - TIN vs ATIN & ITIN

      Zoho Booking Vendors allows for TAX ID values of SSN, EIN, ATIN an ITIN. There is no option for TIN. What is the method to properly add TIN to the list of taxable values for companies? For reference: Social Security Numbers (SSN) Individual Taxpayer Identification

    • Filters and calculations for comparison of day, week to date, month to date, quarter to date and year to date of current year and prior year

      I am building a dashboard that includes reports for comparison of today and week to date (on a like-for-like basis), and month to date, quarter to date and year to date (same date basis) between current and prior years, and forecast for the current year.

    • Ability to Set Text Direction for Individual Cells in Zoho Sheet

      Dear Zoho Sheet Team, We hope you are doing well. We would like to request an enhancement in Zoho Sheet that allows users to set the text direction (right-to-left or left-to-right) for individual cells, similar to what is available in Google Sheets. Use

    • Support Bots and Automations in External Channels

      Hello Zoho Cliq Team, How are you? We actively use Zoho Cliq for collaboration, including with our external developers. For this purpose, external channels are a key tool since they work seamlessly within the same interface as all of our other channels

    • Add Flexible Recurrence Options for Meeting Scheduling in Zoho Cliq (e.g., Every 2 Weeks)

      Hello Zoho Cliq Team, We hope you are doing well. Currently, when scheduling a meeting inside Zoho Cliq, the recurrence options are limited to Daily, Weekly, Monthly, and Yearly. There is no ability to set a meeting to occur every X weeks — for example,

    • Ability to Attach Images When Reporting Issues to Zoho Projects from Zoho Desk

      Hi Zoho Desk Team, Hope you’re doing well. We’re using the Zoho Desk–Zoho Projects integration to report bugs directly from support tickets into the Zoho Projects issue tracker. This integration is extremely useful and helps us maintain smooth coordination

    • Add Comprehensive Accessibility Features to Zoho Writer

      Hello Zoho Writer Team, We hope you are doing well. We would like to submit a feature request to enhance Zoho Writer with a full set of accessibility tools, similar to the accessibility options already available in the Zoho Desk agent interface. 🚧 Current

    • Enable Screen Recording in Zoho WorkDrive Mobile Apps (Android & iOS)

      Hi Zoho WorkDrive Team, How are you? We are enthusiastic Zoho One users and rely heavily on Zoho WorkDrive for internal collaboration and content sharing. The screen-recording feature in the WorkDrive web app (similar to Loom) is extremely useful- however,

    • Make Camera Overlay & Recording Controls Visible in All Screen-Sharing Options

      Hi Zoho WorkDrive Team, Hope you are doing well. We would like to request an improvement to the screen-recording experience in Zoho WorkDrive. Current Limitation: At the moment the recording controls are visible only inside the Zoho WorkDrive tab. When

    • Add Camera Background Blur During Recording

      Hi Zoho WorkDrive Team, Hope everything is well. We would like to request an enhancement to the video recording feature in Zoho WorkDrive. Currently, the camera preview displayed during a recording does not support background blur. This is an essential

    • Properly Capture Dropdowns & Hover Elements When Recording a Window/Tab

      Hi Zoho WorkDrive Team, Hope you are doing great. We encountered a limitation when recording a selected window or browser tab: Certain UI elements, such as dropdown lists, hover menus, and overlays, are not captured unless we record the entire screen.

    • Support Uploading YouTube Videos Longer Than 60 Minutes

      Hi Zoho Social Team, How are you? We would like to request support for uploading YouTube videos longer than 60 minutes directly through Zoho Social. Your support team informed us that Zoho Social currently cannot upload videos over 60 minutes due to “API

    • can't see contact's last activity when in list view

      A contact has past activities recorded in their name. When I go to list view, no Last Activity is displaying. How do i fix this?

    • AM unable to edit my footer

      Hi, I had created my footer, was trying to reduce the width of the same, not sure what i pressed, but footer has taken over the page totally, unable to edit the footer now. cant even view the page. 

    • Bin Tracking with eBay integration

      When trying to setup bin tracking on items that are linked to eBay listings using the built-in Zoho eBay integration, I keep getting this error: "Storage Tracking cannot be enabled for items that are manually fulfiled or for serial/batch items tracked

    • Best practice to handle 50+ invokeurl calls in a loop without hitting the 30-second timeout?

      Hi everyone, I am working on a custom Deluge function where I have a Map containing around 50+ key-value pairs. I need to iterate through this Map using a for each loop and make a GET API call (invokeurl) for each item. The Problem: Because of the 50+

    • sending email with another account

      Hello there, i write there for an our costumer request. They want to send email from CRM with a different email (confirmed and added to zoho profile). For example they use account@zilium.com but with this account they want to send (not only with email

    • Check out in Meetings

      Why there is no check out in Meetings of Zoho CRM, very difficult to track

    • Cloning Module Customizations in Zoho FSM

      Hello Latha, I have two Zoho FSM accounts, each in a different data center. I would like to know if it is possible to clone the module customizations I have already completed in one account to my new account in the UAE from the backend. In other words,

    • Read Thread Details

      Does anyone know how to "read" the complete thread details in a ticket? I figured out how to pull a summary of the threads using a webhook, but it doesn't have all the details I want. I tried to create a loop in flow, which should have worked, but instead

    • All new Address Field in Zoho CRM: maintain structured and accurate address inputs

      Availability Update: 29 September 2025: It's currently available for all new sign-ups and for existing Zoho CRM orgs which are in the Professional edition exclusively for IN DC users. 2 March 2026: Available to users in all DCs except US and EU DC. 24

    • Associate ORG modules using Connected Records in Zoho CRM For Everyone

      Hello Everyone, We are here with a set of new enhancements to Connected Records in Zoho CRM For Everyone. We've made module connections clearer, easier to manage, and less confusing. What's New? Associate Org modules as Connected Records. Clear separation

    • What stops me from packaging and shipping an order when the inventory is negative?

      It seems if the inventory value is negative, that Zoho Inventory should not allow me to create a Package and Ship it.   But, there seems to be nothing to stop me from doing that other than when I go to physically package the item and realize that there is no stock. There also seems to be nothing on the screen that even indicates to me that I should not package and ship.  To me this is the fundamental point of an inventory system.  Am I doing something wrong?   

    • Zoho Show Keeps Crashing and Goes Down with the Data

      Today, I am experiencing something unusual: Zoho Show keeps crashing and goes down with the data. When you try to reopen the same deck, you get that lovely "Presentation not found" error. This has happened today rather frequently with multiple decks.

    • Let us view and export the full price books data from CRM

      I quote out of CRM, some of my clients have specialised pricing for specific products - therefore we use Price Books to manage these special prices. I can only see the breakdown of the products listed in the price book and the specialised pricing for

    • Deluge error on updating datetime field

      Hi, while updating date-time field , i am getting this error  {"code":"INVALID_DATA","details":{"expected_data_type":"datetime","api_name":"Date_Time_1"},"message":"invalid data","status":"error"} Here is the code , i have tried leads = zoho.crm.getRecordById("Leads",

    • 商談の特定ステージ選択時に、集計項目の入力を必須化したい

      こんにちは。Zoho CRMの商談管理で以下を実現したいです。レイアウトルールでは、集計項目を条件に選択できず実現できておりません。よい方法があればご教示いただけると嬉しいです。 ■やりたいこと 商談で特定のステージに変更する場合に、特定の集計項目（同じ商談データ内の項目。以降、集計項目Aとする）を入力必須化したい。 ■概要、状況 ・集計項目Aは、別のオブジェクトで、商談に関連する月ごとの売上を集計している数値項目 ・見積を顧客に提示した際に、集計項目Aに見積金額と一致するようにデータ登録する運用をしている

    • inventory based on bills and not physical stock

      Hello, I have noticed a very annoying issue with zoho books/inventory. I use composite items. If I have an sub assembly item on back order, I am unable to make up the composite item, even when I have received the goods and it is in my stock. I have to convert the PO into a BILL in order for the item to show as 'Accounting Stock'. The problem is that the supplier Invoice is not shipped with the goods, but can follow even a week later. So I have to make the bill have a 'dummy name and number' until

    • Zoho Inventory. Preventing Negative Stock in Sales Orders – Best Practices?

      Dear Zoho Inventory Community, We’re a small business using Zoho Inventory with a team of sales managers. Unfortunately, some employees occasionally overlook stock levels during order processing, leading to negative inventory issues. Is there a way to

    • No option for pick up in Zoho Books / Inventory but yes on commerce

      Is it planned to release soon on books/inventory?

    • Update to CRM Custom Buttons: Collect Users' Location

      Hello everyone! Buttons in Zoho CRM allow you to extend the default CRM capabilities for your bespoke business needs. It provides the flexibility to connect to any third-party application to perform necessary actions. Wouldn't it be better, if those buttons

    • Speed (again!)

      Same old story again: impossible to work at this "speed". Category NOTES!

    • Client Script | Update - Client Script Support For Custom Buttons

      Hello everyone! We are excited to announce one of the most requested features - Client Script support for Custom Buttons. This enhancement lets you run custom logic on button actions, giving you greater flexibility and control over your user interactions.

    • Marketing Tip #27: Add a favicon and social preview image for your online store

      Small branding details can make a big difference in how professional your store looks online. A favicon (the tiny icon shown in browser tabs) helps customers recognize your store instantly, especially when they have multiple tabs open. A social preview

    • Knowledge base: The nitty-gritty of SEO tags

      A well-optimized knowledge base with great SEO can benefit your company by allowing customers to find help articles and support resources using search engines. This enables customers to quickly and efficiently find the information they need without direct

    • Next Page