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

    • 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

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

    • Introducing Data Bars: Graphically represent changes in data within the cells

      Conditional formatting has helped millions of spreadsheet users analyze and highlight their data more efficiently. In addition to the classic rules, color scales, and icon sets available in Zoho Sheet, you can now apply Data Bars, a convenient method

    • 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

    • Recent Topics

    • Tip of the Week #33: Appointment scheduler in Zoho Calendar.

      In Zoho Calendar, you can use the Schedule Appointment option to share your appointment request form with the public, allowing people to fill out the form to request an appointment with you. This form can be embedded on your website or blog. Visitors

    • Tip of the Week #34: Embed Calendars using Zoho Calendar

      You can make your calendars public and visible to the general public by embedding them in your websites/blogs using Zoho Calendar. You can use the embed code to add your own calendars to your website's/ blog's HTML code, and the calendar will appear on

    • Tip of the week #35: Migrate to Zoho Calendar from Google Calendar.

      If you are looking to move your Google Calendar events to Zoho Calendar, never worry about missing out the events from your Google Calendar. You can migrate the events from Google Calendar using the export option and import it to Zoho Calendar and manage

    • Tip of the week #36: Migrate to Zoho Calendar from Outlook Calendar.

      If you've been using Outlook calendar and looking to migrate to Zoho Calendar, you can seamlessly export your calendars from Outlook and import them into Zoho Calendar without losing any events, participants, and the reminders set for each event. To migrate

    • Shared calendar issues and duplications

      Apparently there was a calendar update?  Now when I schedule an event for a team member that has shared his calendar with me, the event makes me the organizer and adds the event to my calendar as well.  Previous to this "update" I would scheduled an event

    • Subscribed Calendar

      Hi i have subscribed to a calendar for Holidays in Canada it shows all the holidays perfectly but every one of them has the word Canada before the rest of the name is there a way to remove that word Canada? It takes up a lot of space in the square on

    • The year that was at Zoho Calendar 2023- Part 1

      Hello, amazing community members! Happy new year from all of us here at Zoho Calendar. As we begin the new year, we'd like to thank each and everyone of our community members for your unwavering support and love that you have shown for Zoho Calendar.

    • Zoho Calendar 2024: A Year in Review

      Hello, community members! Happy new year from all of us here at Zoho Calendar. As we turn the page to a new year, we extend our heartfelt gratitude to every member of our Zoho Calendar community for your continued support and enthusiasm. Your feedback

    • Zoho Calendar not syncing correctly with personal Google Calendar

      Coming to this forum as Zoho Calendar support team is not responding, any more. For the past 8 weeks, I have been having an issue with Zoho Calendar not syncing with my personal Google Calendar correctly. I subscribed to Zoho Calendar iCal in my personal

    • Zeptomail API error 500 internal server error

      Hi Everyone, getting this eror continuously! Can anyone please guide around the same! Zeptomail API error 500 internal server error Best Regards

    • Waiting multiple days to buy credits, causing my website to suffer

      So I own a fairly large website that gets a lot of registered users. I use transmail send activation emails, and also forgot password emails. I sent an email to zoho's presales team when I was only at 4K/10K emails sent, hoping to buy more credits before

    • Follow up

      Hello, I sent a message 2 days ago but I don't receive any response and I cannot find my ticket here. this is the ticket: Your ticket has been created with the ticket ID 68925465 and subject "Fwd: Fishing-alert" looking forward to seeing your response.

    • What's new in TransMail!

      Note: TransMail is now ZeptoMail. Click here to know more. Hello again, everyone! We've recently crossed the 6 months mark of TransMail's launch. In this time post our launch, we have been constantly working on updating our platform and adding new features

    • June 2021 in TransMail!

      Note: TransMail is now ZeptoMail. Click here to know more. Hello again, everyone! Hope you and your loved ones are doing well. If you're doing less than fine, we truly hope that things only get better for you.  We've had a few updates in the past month

    • TransMail has a new name—ZeptoMail!

      Tried navigating to TransMail's community forum but see a different name now? That's because TransMail has a new name. TransMail is now ZeptoMail! As we’ve grown from an internal service used mainly by other Zoho products to an up-and-coming competitor

    • July 2021 in ZeptoMail!

      Note: TransMail is now ZeptoMail. Click here to know more. Hello again, everyone! Hope you and your loved ones are doing well.  We've had a few updates in the past month in ZeptoMail—some new features and some important announcements. Take look at what

    • Send Email From the ZeptoMail BY API

      What is Zeptomail:- Transactional email service with reliable and fast delivery How we can Create a Connection for Zeptomail in Zoho CRM Go to the Setup Click on Connection Enter Connection Details:- Generate Consumer Key & Consumer Secret Using Zoho

    • Customer email on Opencart 3

      When I place an order, 2 emails are sent: 1) administrator 2) to the user The administrator receives a beautiful letter, but the user receives a damaged letter (see screenshot). What could be the problem?

    • [Announcement] Insert image from URL changes in Zoho Writer

      Hi Zoho Writer users! We'd like to let you know that we've changed the behavior of the Insert image from URL option in Zoho Writer for security reasons. Earlier behavior Once you inserted an image URL in a Writer document, the image would be fetched from

    • Writer sing up problom

      Zoho writer sing up prolom face

    • Unable to copy into a new document

      Whe I create a new Writer doc and attemp to copy and past I get this message. The only way to copy into a document is I duplicate an existing document, erase the text and save it under a different name and then paste the information. Not ideal. Can you

    • [Webinar] Live demos and user Q&A with Zoho Writer product experts

      Join us on June 12, 2025 for live demos based on your use cases and real-world scenarios raised via form. This is also an opportunity to get your questions answered directly by product experts from the Zoho Writer team. Webinar agenda Live demos based

    • Zoho Writer's built-in citation and bibliography generator

      Hey researchers and writers! Do you manually format citations and bibliographies, spending hours jumping between apps and tabs? If so, then check out Zoho Writer's built-in "Citations and Bibliography" feature. Imagine you're writing a thesis on the future

    • Single and group checkboxes in Zoho Writer's fillable forms

      Hey Writer Fam, Are you making the most out of single and group checkboxes in fillable forms in Zoho Writer? Here is a handy tip to optimize your use of checkboxes, both single and group, and enhance your data collection process. Single checkboxes: Single

    • Collaborate efficiently with Zoho Writer's track changes feature

      Hi Zoho Writer Community, Zoho Writer's track changes feature is a simple, built-in solution that helps you and your team work together efficiently. Instead of juggling multiple versions of a document or building up a long email chain of comments, each

    • Zoho Writer's WordPress extensions

      Hey Zoho Writer users! Say goodbye to all your WordPress content publishing woes with Zoho Writer's WordPress extensions. Publish content with all your formatting and images, republish content when you update a document, and more—from a single window

    • Time-saving table hacks

      Hey Zoho Writer Community, Do you find yourself using a lot of tables in your documents? We're here to share some of our time-saving hacks that will help you work more efficiently, organize your data, and make your documents look neat and professional.

    • Automating document approval and signing with Zoho Writer and Zoho Sign

      Hey Zoho Writer Community! Here's another automation tip to make your processes more efficient! Question: Can I send a document for client approval first, then automatically send it for signing with Zoho Sign if they approve? Since it's the same person

    • Customization hacks in Zoho Writer - Part 2

      Hey community, We're back with some more tricks to personalize your documents, save time, and get in the zone when you work in Writer. Check out part 1 of this post if you haven't already. Let's dive right in! Document ruler units Imagine you're creating

    • Simplify your tax calculations with Zoho Writer

      Hello Zoho Writer Community! Tax season can be stressful, but with Zoho Writer, managing your income tax calculations becomes straightforward and efficient. Here’s an example of how you can use the tables and formulae of Zoho Writer to calculate income

    • Enhance document navigation with headings and TOC

      Hey Zoho Writer Community! We're back with some useful features in Zoho Writer that can simplify your document creation and navigation process. Let's dive right in! Check out our video on how to make the most of Zoho Writer's heading and table of contents

    • Use and download in PDF format of Zoho wirter Merge template using deluge

      Hello Zoho Developers. Here is some information about Zoho Writer. Writer is not just another online word processor, it's a powerful tool for editing, collaboration, and publishing. Even with its wide range of features, Writer's pared-down user interface

    • Customization hacks in Zoho Writer - Part 3

      Hello everyone, Welcome back to Part 3 of our customization tips in Zoho Writer! In this third installment, we'll be diving into some essential customization settings that can enhance your document creation experience. Sender email address in mail merge

    • Daytime saving timezones messing up writer pdf

      Hi, I need help for something I can't figure out. I created a Form to collect data and it is set up with my current Daylight Saving Time (GMT-3). This form is used to generate a contract (pdf Writer) with dates from an event that is being held in 4 months

    • Issue with locked content in Writer

      Hi, I have seen the documentation which outlines how to lock specific content within a Writer document so that it can't be modified by collaborators, but I have come across an issue. When the editor locks a paragraph for example, then a collaborator can't

    • Deprecation of certain URL patterns for published Zoho Writer documents

      Hi Zoho Writer users! We'd like to let you know that we have deprecated certain URL patterns for published and embedded documents in Zoho Writer due to security reasons. If the published or embedded documents are in any of these URL patterns, then their

    • Using Mail Merge Template to Print Documents with One Subform Record's Fields per Document

      Hello, We have a Mail Merge template created in Zoho Writer which is not able to perform the functionality which is currently required to automate the documentation task portion of our process. The CRM module we are primarily using is based on a "Loans"

    • Problem with Writer and Workdrive

      Hi team, I’m the super admin for our Zoho One org. WorkDrive is active, and Zoho Docs is deprecated for our org. However, Zoho Writer cannot connect to WorkDrive at all — we’ve cleared cache, tried incognito, and restarted several times. I was able to

    • Set to Review for all

      We are testing the use of Writer as part of an internal review process for statement of work documents and have found that when the document is changed from Compose to Review by one person, that is not reflected for all others who view the document. Is

    • I’ve noticed that Zoho Sheet currently doesn’t have a feature similar to the QUERY formula in Google Sheets or Power Query in Microsoft Excel.

      These tools are extremely helpful for: Filtering and extracting data using simple SQL-like queries Combining or transforming data from multiple sheets or tables Creating dynamic reports without using complex formulas Having a Query-like function in Zoho

    • Next Page