Tip #9: 5 common spreadsheet errors and how to solve them

Tip #9: 5 common spreadsheet errors and how to solve them

Functions can help you automate your calculations and pull important insights from your data set. However, if something goes wrong with these functions, errors occur and make your spreadsheet less reliable. These errors usually have a cascading effect, especially if your functions refer to and depend on each other. In this post, we'll look at five of the most common errors your spreadsheet formula might throw at you and how to troubleshoot them.
 

#NAME!

The infamous #NAME error is also the easiest to troubleshoot. This error is often the result of a typed error in the formula. This could be a spelling mistake in the function name, the range, or even the sheet name. Unbalanced brackets or quotes could also be at fault. To rectify the #NAME error, take a look at your function and check for possible spelling mistakes, typographical errors, or open brackets and make sure you've furnished all necessary arguments as per the syntax. 


 

#VALUE!

A #VALUE error usually means one of the two things: The function does not have sufficient arguments, or the arguments in the function are not the right data type. For instance, if you intend to add values in cell C5 by the value in cell C6, they must both be numbers. If one of the cells is a text string, then the spreadsheet throws a #VALUE error. To troubleshoot the function, check that all necessary arguments of a function are furnished and in the right format.



#N/A!

This is technically not an error. Your formula is working properly, but there is no value available for the spreadsheet to return as the answer. For instance, if you use a VLOOKUP function in a table to return the sales numbers for transactions handled by Adriana, and there are no records of Adriana in the tables, the formula will throw an #N/A error.



#CIRCULARREF!

#CIRCULARREF is an error that results when functions directly or indirectly refer to themselves. For instance, if you're trying to create a formula in cell A1 that calculates the sum of multiple cell values, and you try to include A1 in the calculation, you'll get the #CIRCULARREF error. To troubleshoot, edit the formula and remove the function's reference to itself. If left unchecked, these functions could overwork and slow down your spreadsheets.



#SPILL!

There are quite a few formulas in Zoho Sheet that support array results. But existing content in a spreadsheet can obstruct spill results. When this happens, Zoho Sheet throws a #SPILL error. To troubleshoot, select the cell with the #SPILL error and click the error icon to view possible resolutions. Simply selecting and moving the cells obstructing the results, or deleting their contents, can resolve the #SPILL error.



We hope these tips were helpful. Follow our community and our Twitter handle for more spreadsheet tips and tricks. 

    Zoho Campaigns Resources

      Zoho Desk Resources

      • Desk Community Learning Series


      • Digest


      • Functions


      • Meetups


      • Kbase


      • Resources


      • Glossary


      • Desk Marketplace


      • MVP Corner


      • Word of the Day


        • Sticky Posts

        • Tip #18: 6 Trendlines and when to use them in your spreadsheet data?

          Charts are a great tool for visualizing and interpreting large chunks of data in spreadsheets. Zoho Sheet offers you 35+ chart options, along with AI-powered chart recommendations based on the data set you select. There are various chart elements that
        • Tip #16: Ask Zia your data-related questions and get powerful insights

          Zoho Sheet provides a variety of tools and functionalities to help you analyze your data from end to end. But what if you could analyze all your data sets with a single AI-powered tool? Meet Zia, our smart virtual assistant who will answer all your data-related
        • Tip #17: Easily share spreadsheets with non-Zoho account users

          With efficient, collaboration-friendly options, Zoho Sheet is a popular choice among users. Sheet's external share links help you collaborate more effectively by sharing your spreadsheets with anyone outside your organization, including users who do not
        • Tip #7: Four things you didn't know you can do with Conditional Formatting

          Conditional formatting helps you highlight and visualize data in your spreadsheet based on provided rules. Zoho Sheet offers three types of conditional formatting: Classic, Color Scales, and Icon Sets. If you're already familiar with how Conditional Formatting works, here are some bonus tips for Classic formats: Customize rules with formulas Zoho Sheet allows you to create personalized rules based on formulas. For example, in a task list you can use customized formulas to highlight tasks which are
        • Tip #15: Stop guessing and start estimating with Zoho Sheet's Goal Seek

          Goal Seek is an ideal spreadsheet tool for devising estimates, as it helps you retrieve your desired output value when the input values are not known. In other words, Goal Seek does back-solving to find input values once you've provided an output value.

        Zoho CRM Plus Resources

          Zoho Books Resources


            Zoho Subscriptions Resources

              Zoho Projects Resources


                Zoho Sprints Resources


                  Zoho Orchestly Resources


                    Zoho Creator Resources


                      Zoho WorkDrive Resources



                        Zoho CRM Resources

                        • CRM Community Learning Series

                          CRM Community Learning Series


                        • Tips

                          Tips

                        • Functions

                          Functions

                        • Meetups

                          Meetups

                        • Kbase

                          Kbase

                        • Resources

                          Resources

                        • Digest

                          Digest

                        • CRM Marketplace

                          CRM Marketplace

                        • MVP Corner

                          MVP Corner




                          Zoho Writer Writer

                          Get Started. Write Away!

                          Writer is a powerful online word processor, designed for collaborative work.

                            Zoho CRM コンテンツ






                              ご検討中の方

                                • Recent Topics

                                • Dialing Microsoft Teams Phone Service via Zoho CRM

                                  I am using the VOIP option in Microsoft teams for my office phone system. I was hoping to have a way to dial numbers directly from Zoho CRM, but don't see anything in the Teams Integration or in the Telephony integration that will enable this. Does anyone
                                • Emailing lookup field but placing this as an ID or number rather than text

                                  Hi there, First time poster and have been a user of Zoho Creator for approx 6weeks so forgive my ignorance as I learn to code. We have a need to send an email to a specific email address with some of the fields triggered by the submission of a form. In
                                • Search mails in shared mailbox

                                  Hi everyone, is there a way to search mails in shared mailbox's? Search in streams or mail doesn't return anything from mails in shared mailboxes. Thanks! Rafal
                                • User Emails Blocked

                                  Community: I keep running into issues where our users stop receiving notifications from CRM because their email addresses get blocked in on the backend some how. I reach out to support, they confirm, they fix, and we carry on, but then it happens again.
                                • Why wont Zoho Support Grammarly!! --- PLEASE VOTE FOR THIS to show Zoho we need this

                                  The spell check and grammar in ZOHO are so buggy and a waste of time. Please support Grammarly! I'm sure I'm not the only one — there are other CRMs that support this. If you're not planning to add this feature, Please let others know before accepting
                                • Is it possible to hide Developer Space for all user in Zoho Projects

                                  Hello! I am Zoho admin in a company and we want to use Zoho Project to manage projects, but after a few days of testing we are not able to "hide" the Developer Space from all kind of users except the admin. To sum up, I want to hide this for all users.
                                • API (v2) Search Criteria using CONCAT

                                  With API I can search for a contact using First_Name and Last_Name. However, when I need to search the Contact Module using a full name — and because CRM does not provide an API for full name — I am not finding a way to do this in the traditional way
                                • Weekly Tips: Stay Focused with Email Snooze!

                                  New Year, New Resolutions Being back at work also means being back to the constant barrage of messages from work and clients. The constant flood of incoming emails can lead to the missing of important messages, especially when you can't respond right
                                • Schedule Zoho CRM reports only on Business Days

                                  Hello, Is it possible to schedule reports only on business days ? We currently get daily sales reports on weekend which has no value since the sales team doesn't work on weekends. Thanks
                                • Zoho Payroll's Year in Review 2024

                                  As we roll into 2025, we'd like to pay tribute to all the milestones we hit in 2024! From releasing out new features that streamlined your workflows to updates that made payroll management smoother, we’ve had a prolific year—all while keeping you, our
                                • Using tickets to train Zia

                                  Hi Team, I would like to know if there is any way that Zia can also learn from previous tickets in addition to the articles from the knowledge base. Since we have most of our knowledge curerently in the tickets and that this is hard to combine into a
                                • Recurring Events Not Appearing in "My Events" and therefore not syncing with Google Apps

                                  We use the Google Sync functionality for our events, and it appears to have been working fine except: I've created a set of recurring events that I noticed were missing from my Google Apps calendar. Upon further research, it appears this is occurring
                                • How can the direct link to a ticket be created from ticket #?

                                  Our agents will be using ZDesk but we will have to create direct link to tickets in another ERP by providing the Desk Ticket # Is there a way to create the direkt links from the ticket # alone? Do we have to use the API to get the direct links?
                                • Multiplying Weight of product by Quantity

                                  I am facing an issue with creating a report that consolidates the total sales volume in kilograms. I have already specified the weight for each product. I have also aggregated the total sales quantity. The key question is: how can I create a report that
                                • Confirmation prompt before a custom button action is triggered

                                  Have you ever created a custom button and just hoped that you/your users are prompted first to confirm the action? Well, Zoho knows this concept. For example, in blueprint, whenever we want to advance to the next state by clicking the transition, it is
                                • Frontal interview scheduling - room availability in office using Google Workspace?

                                  Hi, We're using Zoho Recruit as our ATS and Google Workspace as our email, calendar and resources management. We want to use the interview feature to schedule an in-person (frontal) interview with the applicants. How can we sync the room resources availability
                                • Add and Remove Agents from Departments and Groups in Zoho One

                                  Hi Zoho Flow Team, We hope you're doing well. Currently, Zoho Flow provides an action to add an agent to a group in zoho one, but there is no action to remove an agent from a group or a department. Another action that we find missing is the option to
                                • Explication sur comment mettre en place des règles d'affichage ou "layout Rules"

                                  J'ai passé plus d'une heure hier avec le support et je n'ai rien compris !! Je suis lecteur assidu des guides (je "RTFM") qui ne sont absolument pas orienté "client" chez Zoho, et je tiens à le rappeler ici . Dans la documentation on m'indique un cas
                                • Introducing Hiring Pipeline for Vendor Portal

                                  Keeping vendors informed about candidate progress is often challenging, leading to communication gaps and repeated follow-ups. To address this issue, we've released an update to the Vendor Portal feature that lets you choose to display candidates' hiring
                                • Unlocking New Horizons: A Year in Review

                                  As we bid farewell to 2024, let's celebrate and revisit the key highlights of the year. From adding a new edition to cross-platform enhancements, here’s a roundup of all the feature updates designed to simplify accounting, optimize financial management,
                                • Free user licenses across all Portal user types

                                  Greetings everyone, We're here with some exciting and extensive changes to the availability of free user licenses in CRM Portals. This update provides users with access to all Portal user types for free to help them diversify their user licenses and explore
                                • Address Grabber function for Zoho

                                  I converted from ACT to Zoho. With ACT, I used an add-on called AddressGrabber to scrape the contact information from leads that I buy and contact information contained on emails and websites and directly add it as a new lead or contact. Does anyone know
                                • Integrate zoom with zoho bookings please; or add optional times in zoho meetings

                                  Just like events - these online meetings like zoom need to be integrated with zoho bookings, and there needs to be option for customer to book a time slot. It should not be dictated by CRM user.
                                • Add Owner to deluge-created module record note

                                  Is it possible to include the "owner" aka "creator", of a Note when creating it via delulge? This sets "superadmin" as the Note creator. I need to override it. notemap = Map(); notemap.put("Parent_Id",program_contact_id); notemap.put("Note_Content",program_contact_data.get('Note'));
                                • Directly Edit, Filter, and Sort Subforms on the Details Page

                                  Hello everyone, As you know, subforms allow you to associate multiple line items with a single record, greatly enhancing your data organization. For example, a sales order subform neatly lists all products, their quantities, amounts, and other relevant
                                • Custom "Filter By" in Client Portal

                                  Currently our client portal only shows items for that specific person that is logged on to the portal, we want the current logged user to see all items for that user's company. An example would be invoices, so the current user would see all invoices for
                                • Unique and Random IDs in Zoho Forms: Organize and Secure Your Data Efficiently

                                  When it comes to form submissions, organizing and identifying entries effectively is crucial. Zoho Forms offers two versatile ID generation options for submissions: Unique ID and Random ID. Each serves distinct purposes, providing flexibility to meet
                                • Sort By Date - Deluge

                                  I have the following code, which normally works to sort calls by created time. Every once in a while, it doesn't work and something sneaks through in the wrong order and I can't figure out why. calls = zoho.crm.searchRecords("Calls","(Owner:equals:" +
                                • Iteration through a list - Coming up against a "Failure to update function" error

                                  Hi there! I've been attempting to get a deluge script working and am running into an error that I have been unable to resolve. The error I am getting is Failed to update function Error at line :18. Improper Statement. Error might be due to missing ';'
                                • Error 403: Forbidden When Updating Email Signature via API

                                  Hi Zoho Desk team, First, congratulations again on the excellent Zoho API. But, I’m encountering an issue while attempting to update an email signature via the API. Whenever I make a request to update the signature, the response returns an HTTP 403 Forbidden
                                • Can you modify "Last Activity Time" in deluge? If so what's the field name?

                                  I need to perform some bulk modifications on records in the Leads module, but I need to avoid changing the "last activity time" or "date modified" because I am using those fields to filter and sort leads for follow-up action. I cannot find an answer anywhere
                                • How do I define a weekend

                                  I noticed the default for weekends does not seem to include Saturdays. How can i define weekends to include both Saturdays and Sundays? Thank you.
                                • 【Zoho CRM】ポータル機能のアップデート

                                  ユーザーの皆さま、こんにちは。コミュニティチームの藤澤です。 今回は「Zoho CRM アップデート情報」の中からポータル機能のアップデートをご紹介します。 今回の機能アップデートにより、CRMのポータルへのログインがより簡単にできるようになりました。 【目次】 SAMLベースのシングルサインオンについて ポータルへのアクセスリンク送付について 今回のアップデートにより、アイデンティティプロバイダー(IdP)を利用している組織において、SAMLベースのSSO(シングルサインオン)を有効化できるようになりました!
                                • Getting error during inserting a record in form of zoho people using zoho api

                                  import requests import json # Set your access token and Zoho People API base URL access_token = '1000.XXXXXXXXXXXXXXX.XXXXXXXXXXXXXXXXXXXXX' api_base_url = 'https://people.zoho.in/people/api/' # Set the form name and data to be inserted form_link_name
                                • UI Arabic

                                  can i change the member portal UI to arabic in zoho community?
                                • Resume template to include profile photo

                                  Hi, We would love the Resume Template to be able to include their profile photo. How can this be done? Miriam
                                • Marking a form as 'done'?

                                  Hi! I've got a form that staff fill in when moving stock from location to location. I see the entries they've sent across and I input them into my stock control system. Is there a way to mark a form as 'done'? So that I know which entries I've input,
                                • Zoho CRM - best way to search an account and assign to a deal

                                  Hi Everyone I am looking for some advice. I want to find the best way to complete the below steps. We have a deal and once it reaches a certain stage we need to allocate a supplier / vendor to this deal along with the salesperson. I want to add (ideally
                                • How to easy change layout in existing records in Deals?

                                  Hello, So far i have used only 1 layout in Deals. I have about 1000 records. Now i want to make new layout. So i have 2 layouts: Layout Old (1000 records) Layout New (0 records) How to easy change layout from Layout Old into Layout New for existing records?
                                • Zoho Rating

                                  When I close a ticket, the zoho system automatically sends an email to the customer for them to leave a rating. When they leave a rating, the ticket re-opens and I have to close it again. When I close it again, the zoho system sends the customer an email
                                • Next Page