Tip #10: 3 helpful ways to use data validation in your spreadsheet

Tip #10: 3 helpful ways to use data validation in your spreadsheet

Data validation in spreadsheets makes data entry processes more efficient, while helping reduce inconsistencies and errors in your data sets. In this article, we'll look at three ways you can use data validations to create useful dropdown lists in your spreadsheets.

Dynamic data validations

Dynamic validations help you create a new dropdown list based on an existing dropdown list or range of values. Let's say the first dropdown has the names of all the departments in your organization. You can now create a secondary dropdown list that displays designations based on the value chosen from the first dropdown!  



Dynamic data validation can be achieved using the lookup functions based on the number of criteria required. Here's a published spreadsheet for a hands-on experience!

Dropdowns with symbols  

You might need to include symbols in your spreadsheet for comprehension or visual presentation. Create a validation with a list of desired text symbols and easily access them when needed.  



Note: Only text icons can be used in data validation. There are many sites offering free text icons for easy use via copy-paste.
 

Condition-based validations

Apart from dropdowns, you can use data validation to restrict or allow data entry, and set conditions on a range based on values in other cells. This way, you can restrict edits on a range without using Lock!



Join the Zoho Sheet community and stay up to date on the latest features, interact with fellow users, and learn from the team behind Zoho Sheet. For more exciting tips, follow our  Tips and Tricks forum.

    • Sticky Posts

    • Tip #20 - Three things you probably didn't know you can do with picklists

      Hello Zoho Sheet users! We’re back with another quick tip to help you make your spreadsheets smarter. Picklists are a great tool to maintain consistency in your spreadsheet. Manually entering data is time-consuming and often leaves typos and irregular

    • 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

    • Recent Topics

    • Tagged problem !!!

      Damn it, we're one of dozens of construction companies in Africa, but we can't link purchasing invoices to projects. Why isn't this feature available?

    • Limited review (/questions) for Bookings 2.0

      Hi all, I'm writing this review of Bookings 2.0 for two reasons: 1) it may be of interest to others, and 2) I'd like to be corrected if I'm wrong on any points. It's a very limited review, i.e. the things that have stood out as relevant, and particularly

    • Syntax for URLs in HTML Snippets

      What are some best practices for inserting a URL in an HTML snippet? I've looked at Zoho Help articles on navigation-based and functional-based URLs, but I'm still unclear on how to incorporate them in an HTML snippet. For example, 1. How do I link to

    • The Social Wall: June 2025

      Hello everyone, We’re back with June Zoho Social highlights. This month brought some exciting feature updates—especially within the Social Toolkit—to enhance your social media presence. We engaged with several MSME companies through community meet-ups

    • Make panel configuration interface wider

      Hi there, The same way you changed the custom function editor's interface wider, it would be nice to be able to edit panels in pages using the full width of the screen rather than the currently max-width: 1368px. Is there a reason for having the configuration panel not taking the full width? Its impossible at this width to edit panels that have a lot of elements. Please change it to 100% so we can better edit the layouts. Thanks! B.

    • Tip 7: How to fetch data from another application?

      Hi everyone, Following our Zoho Creator - Tips and Tricks series every fortnight, we are back today with a tip based on one of the most popular questions asked in our forum. This tip would help you fetch data from another application(App B) and use it

    • The Social Wall: May 2025

      Hey everyone, We're excited to share some powerful updates for the month of May! Let's take a look! Reply to your Instagram and Facebook comments privately through direct messages Are you tired of cluttered comment threads or exposing customer queries

    • Sub-Form Fields as Filters for Reports

      Hi, I would like to use the Sub-Form Fields as Filters in Reports just like we do for Main Page Fields. Thanks Dan

    • Zoho CRM Formula - Current Time minus Date/Time field

      Hello, I am trying to prevent duplicate emails going to clients when more than 1 deal is being updated. To do this, I would like to create a formula to identify if a date/time field is >= 2 hours ago. Can someone please help me write this formula? Example:

    • Per Level Approval for admins

      We need Process admins like Zoho CRM in Zoho Books for per stage approval Currently in books, admins only have the option for Final Approval But for example, in cases like when an employee is on leave, we can't just approval one level we only have option

    • Billing Management: #7 Usage Billing in Telecom & Internet Service Provider

      Telecom and Internet Service Providers operate in markets where usage varies drastically from one customer to another. While flexible, usage-based models align revenue directly with consumption, they also introduce operational challenges like real-time

    • Zoho Sprints - Q3 updates for 2025

      The updates for the third quarter of 2025 are out. A few significant features and enhancements have been rolled out to improve user experience and product capabilities. The following are the updates: Manage tags and cluster tags Record and maintain project

    • Kaizen #208 - Answering your Questions | Functions, AI and Extensions

      Hello Developers! Welcome back to a fresh week of Kaizen! We are grateful for your active participation in sharing feedback and queries for our 200th milestone. This week, we will answer the queries related to Functions and Extensions in Zoho CRM. 1.

    • Zoho CRM still doesn't let you manage timezones (yearly reminder)

      This is something I have asked repeatedly. I'll ask once again. Suppose that you work in France. Next month you have a trip to Guatemala. You call a contact there, close a meeting, record that meeting in CRM. On the phone, your contact said: "meet me

    • Creating Restaurant Inventory Management on Zoho

      Hi,  We run a small cloud kitchen and are interested to use Zoho for Inventory and Composite Item tracking for our food served and supplied procured to make food items.  Our model is basically like subway where the customer can choose breads, veggies,

    • To Zoho customers and partners: how do you use Linked Workspaces?

      Hello, I'm exploring how we can set up and use Linked Workspaces and would like to hear from customers and partners about your use cases and experience with them. I have a Zoho ticket open, because my workspace creation fails. In the meantime, how is

    • Zoho Forms to Zoho CRM : First/Last Name to just Name ?

      When integrating a Zoho Form into the Accounts menu of the CRM I'm having trouble with how names are formatted ; In Forms the data is available as First Name or Last Name In the CRM there is only one field called Name How can I ensure that "John" "Smith"

    • Enhancements to the formula field in Zoho CRM: Auto-refresh formulas with the "Now" function, stop formula executions based on criteria, and include formulas within formulas

      Dear Customers, We hope you're well! By their nature, modern businesses rely every day on computations, whether it's to calculate the price of a product, assess ROI, evaluate the lifetime value of a customer, or even determine the age of a record. With

    • This festive season, offer discounts with coupon code support in Stripe Checkout

      Hello form builders! It’s the festive season, the perfect time to spread joy and great deals! Now, with Zoho Forms’ latest enhancement for Stripe Checkout, you can do exactly that with coupon codes! Your payment forms integrated with Stripe Checkout can

    • Cómo creo una factura negativa o de abono?

      NEcesito anular una factura y crear una nueva igual pero en negativo. El sistema no me lo permite

    • Power of Automation::Streamline log hours to work hours upon task completion.

      Hello Everyone, A Custom Function is a user-written set of code to achieve a specific requirement. Set the required conditions needed as to when to trigger using the Workflow rules (be it Tasks / Project) and associate the custom function to it. Requirement:-

    • Is CRM On Premise available

      Hi Zoho team, Can you please let me know that CRM Zoho is available for On Premise as well? Thanks, Devashish

    • Last activity time is acting like last modified time

      When i edit the description or any field in the potential, account, contact and lead, the Last Activity Time is being updated like the Modified Time. This is messing all workflows and reports and we are unable to track real last time of activities like

    • CRM : Function to add user name to text field

      I have a lookup field in a module that is linked to the CRM users so we can assign a Project Lead to the customer. Sadly Zoho Marketing Automation doesn't sync Lookup fields so I need to extract information from the lookup to text fields: Lookup field

    • Zoho CRM - Restrict Login based on work hours

      Hi there, I'm wondering if we can restrict users to login during works - For example the users would be able to login from 8am to 5pm. I have seen the IP address restriction - the only downfall is what if the customer has dynamic IP. thanks Jiri

    • Tips and Tricks #46: Customize themes and templates using Show's Master View

      Hi All!  Let's say you want to revamp your presentation and make changes to its visual design. You can do this easily using the Master Slide and its associated layouts. The Master Slide stores information about all the layouts used in the presentation.

    • Sheet View in CRM portal

      Hi, When will it be possible for my CRM portal users to edit/add records with Sheet View? George

    • What's New in Zoho Invoice | July - September 2025

      Hello everyone! We’re back with the latest updates and enhancements we’ve rolled out in Zoho Invoice from July to September 2025. Here’s what’s new this quarter: Introducing the Singapore Edition in Zoho Invoice Share Invoices through WhatsApp GST 2.0

    • Alert: Audio Call Support to be discontinued for Old Live Chat Widget from December 31, 2025

      Action Required: Upgrade to the New SalesIQ Live Chat Widget We're reaching out with an important update regarding the SalesIQ Live Chat Widget that requires your immediate attention. Effective December 31, 2025, audio call functionality will no longer

    • multiple contacts for one account

      We currently use Zoho CRM where each Account represents a club or organization, and each Contact represents a manager or owner. However, some of our managers own or manage multiple clubs, and Zoho only allows a contact to be linked to one account at a

    • Rich text Merge field - Not using font specified in HTML

      I have a rich text merge field in a writer template which is creating a table. I have chosen to use this method instead of a repeat region because I need to specify specific cell background colours which change every time the document is created. The

    • Can't change form's original name in URL

      Hi all, I have been duplicating + editing forms for jobs regarding the same department to maintain formatting + styling. The issue I've not run into is because I've duplicated it from an existing form, the URL doesn't seem to want to update with the new

    • ヒートマップ詳細設定について

      はじめまして マーケティング担当の浅田です。 PageSenceのヒートマップ設定について質問です。 単一ページ毎の設定は上手くできるのですが 詳細設定にて、トップページのURLを含ませ全体のヒートマップを計測できないか試したところ お知らせ：データを受信していません。と表示されてしまいます。 トップページURLで始まるページの条件も試しております。 全頁を反映させたいと思い、詳細設定を上手く設定できたらと考えております。 どなたか詳しい方がいらっしゃいましたら、教えて頂けませんでしょうか 宜

    • Automatic category assignment

      Hi, I’d like to ask if there is a way to automatically assign an expense category based on the recognized Merchant. What would be the simplest way to set up automatic category assignment? Alternatively, is there an option to first choose the category

    • Experience with Zoho Vertical Studio

      I'm considering Zoho Vertical and would love to hear from some devs who've been using it. The Zoho ecosystem is pretty solid, so I assume the experience has been pretty good, but sometimes Zoho has its quirks. Overall, has your experience been positive?

    • LinkedIn Chrome Extension

      Hello - I believe it is a known issue that the LinkedIn extension Resume Extractor has stopped working and they are working on potential fixes. Wondering how others users are finding this issue and if there are any better workarounds for this issue? It

    • Advance PDF creation from CRM data

      I'm trying to create a PDF export of data in the CRM. My problem is I want a pretty complicated format for the data. I'm trying to export multiple modules worth of data, with nested one-to-many relationships between the modules. Along with that, I want

    • Where does this report come from in the Zoho One ecosystems?

      Is this directly from MA, Analytics or ??? ???

    • Sub-Form Padding in CSV Export

      Hi, When you use the Sub-Form, and for example you have a Date Field on the Main Page, then Option 1 and Option 2 fields on the Subform, when you export this to CSV the Date column will only have the Date in 1 row, the first row, it would be nice to pad

    • More Formula Functions

      Hi, I would like for example to be able to have a Date Field and Formula Fields, and then in the Formula Fields I would like to grab just the Month of the above Date Field or the Week Number of the above Date Field. So more "Functions" than the current

    • Next Page