Tip #7: Four things you didn't know you can do with Conditional Formatting

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 nearing their due date, are already completed, or are now overdue.


 


Format your table with alternate row colors

Want to format your data table rows with colors in an alternating pattern? Conditional formatting can not only help you visualize your data but also format your table. Use the formula =MOD(ROW(),2)=0 as the rule in a Classic format and choose the fill color you want. This way, you can format your data table in an instant.




Highlight duplicate entries in your data

When a spreadsheet has data coming in from various sources, it will often contain a few errors or duplicate values. You can keep an eye on duplicate values, missing values, or errors in your data set by highlighting them with conditional formatting rules.

                                                              

Set and manage multiple rules on a range

The set of conditional formatting rules are applied from top to bottom. If you have two separate rules set on the same cell range, you can just rearrange the rules and put the one you want applied first at the top.
​
                                                

To know more about conditional formats and their use, watch our webinar video.

 

Are there any specific features you'd like to see in our future tips? Mention them in the comments below!

 

Join the Zoho Sheet community and stay updated on the latest features, interact with fellow users, and get to know the team behind Zoho Sheet. For more such exciting tips follow our Tips and Tricks forum.



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

      • Recent Topics

      • Custom API - Need to create a string return value, not only MAP

        @Support: When creating a Custom API it only allows a return from a function of MAP type. The service I'm using requires a string return, how can this be achieved?

      • Extracting data from cells in zoho sheets for zoho books

        I am currently uploading my bank statment in excel format to zoho workdrive. I would like flow to extract certain data and send it to zoho books. Would scripting in zoho flow be able to help me with this? By this I mean should I attempt this in zoho flow

      • missing video-urgent

        hi..I have a problem regarding zoho meeting. I already record almost 2hrs for my interview session. After end my session, i'm stop the recording. Its happened when i didn't received any meeting recording at my email.But i received recording 7minutes after

      • Duplicate New Ticket Emails

        I am getting two Ticket emails when a ticket is created. The first one goes to me as the Admin helpdesk@ And because I am an Agent as well it also send to helpdesk@, support1@ and support2@ Is there any way to stop this?

      • Ticket Response Email Template

        Is there a way to have an email template automatically selected when I click on Reply for a ticket?

      • Unpublishing Job Post for LinkedIn

        Hi, We have experienced issue with the above. We did unpublish a job in Recruit but it still appear under Job section in our LinkedIn page. It did not remove from LinkedIn as it should be.  Please check and advise. Regards, Snyder 

      • New Built In QR/Barcode Generator Print Settings

        I'm trying out the new QR/Barcode generator field in Creator. I would think most people will want to print these, like I do. I am not seeing any way to control the height or width of the barcode for printing (inside the print/pdf template builder). The

      • Como gerar gatilhos para pagamento de impostos no Zoho Books?

        Olá Pessoal, boa tarde! Gostaria de saber como vocês estão escriturando os impostos a pagar no Zoho Books. Vi que temos a opção de Bills, porém se eu escriturar nesta aba do Zoho Books para gerar lembretes de tempo de vencimento por exemplo vai refletir

      • Editor limitations to define screen types

        Guys I have noticed that even in version 2.0 of the editor (which is this new one we use) we still have a lot to improve... When I compare to some more global solutions like Wix, Zyro, Go Daddy, Squarespace and Weebly feel that we have some limitations

      • Dúvidas do Zoho Creator

        Pessoal, Estou colocando um tópico para dúvidas do Zoho Creator. Um abraço, Leandro

      • Tropicalize Books

        Books is an incredibly powerful tool that works well in many countries. But I feel that it is a product that is not yet "tropicalized" for Brazil as we speak (this would be like adapting the local reality). We have many strong competitors who do more

      • Automatic Sitemap Generation

        Guys are all right? Doesn't make sense for me to have to generate a map site and upload it... because it's not automatically generated just as it is done in WIX? where the customer doesn’t have to worry about this.

      • SEO improvements with ZIA

        Are you okay? I would like to bring an idea that would be amazing to improve the product that is the possibility of being able to improve the SEO of the pages (this of each page or each article on the blog) through ZIA so that it could create page Summary

      • How to Replace an Assessment in a Job Opening on Zoho Recruit

        Hi everyone, I’m currently using Zoho Recruit and would like to replace the assessment linked to a specific job opening. I want to remove the existing assessment and add a new one. What is the best way to do this without losing any important data or affecting

      • Automatically updating field(s) of lookup module

        I have a lookup field, which also pulls through the Status field from the linked record. When the lookup is first done, the Status is pulled through - this works perfectly. If that Status is later updated, the lookup field does not update as well. As

      • Initiating a SalesIQ Zobot from a custom button on Zoho Sites

        I have created a Zobot set to initiate on a custom action called "Fast_Answers". On Zoho Sites, I created a code snippet button and set it to on-click run the event called "Fast_Answers". I installed the SalesIQ integration code into the Zoho Sites Page

      • 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

      • Zoho Desk -> Zoho Analytics : Where is the field for "Layout" ?

        I have many different layouts on my helpdesks and I want to be able to identify the stats for each one, however I can not file the field in the raw data from the Zoho Desk datasource. I thought it might be under "Tickets" but there is nothing. There is

      • Set resolution mandatory field

        Hi,  i have 2 questions:) : - i want to set the resolution field mandatory before close the ticket. Because for now, i can close the ticket without writing how i solve it - how can i setup zoho desk to receive ticket by email(e.g. clients sent email to support@mydomain.com) and it create a ticket in zoho desk

      • Impuesto automatico en cotizaciones

        Buen dia Mi pregunta es como se puede poner alguna operacion para que las cotizaciones me salgan automaticamente con impuestos ya que uno al cargarla al final tienen que añadir el porcentaje de impuesto Saludos

      • Zoho Assist "Agree and Download" Button "Greyed Out" ("Light Blued" Out)

        Anyone else having issue where support clients are unable to click "Agree and Download" to access the client so that we can provide remote support? This is for "on demand" support via accessing the support page and entering the support key and name. This

      • Project Billing with the Staff Hours Method in Zoho Projects

        The Staff Hours Billing Method in Zoho Projects allows you to bill your clients based on the actual time spent by each team member on a project, at the rate set for each user. This is useful for projects where different skill sets are needed and service

      • Creator Subform to CRM Subform

        Hello all, Has anyone successfully written data from a Creator Subform into CRM subform? I have been able to get the rows to populate but none of the data will come through. I'll add my code and the result in CRM. Creator Subform is 'Delivery_Receiving_Hours'.

      • custom fields not populating from deluge script into invoice

        Hello, I've created some Deluge script that is meant to take a few inputted invoice custom fields and calculate a few others. I can see when I execute the function that my inputted custom fields are being passed, yet im still ending up with all "null"

      • tax summation function - getting error

        Hello, I'm trying to create a function that adds all of individual tax rates from a few jurisdictions. I'm getting an error on line 9 - Value is empty and 'get' function cannot be applied. I've checked that I have data in each of the required fields,

      • Backstage / Zoho Books integration

        Hello. We have Zoho One and have slowly started using Backstage. Loving it. Problem is, we have ZERO accounting control over what is sold through this product. When will we be able to connect it to our existing Zoho Books tenant? Thank you very much!

      • Getting list of calendar events over api for zoho mail calendar

        Hi, I am using just Zoho mail without using Zoho CRM. I wanted to get all events booked in my zoho mail calendar through an api at regular intervals. I could find such API support for Zoho CRM calendar but not for zoho mail calendar. Can you kindly let

      • Allocate emails to user in a shared mailbox

        Hi, This might be obvious, but I cannot find the answer. I have 3 shared mailboxes so any team member can see the emails. Is there a way of allocating a specific email to a user so that it is their responsibility to deal with it? Thanks in advance.

      • Introducing Zoho Campaigns' own gateway for SMS campaigns

        We are excited to announce the launch of our SMS Gateway to send SMS through Zoho Campaigns. We have also made a few other changes in our current SMS Campaign model to improve your over all user experience. These updates are planned with an aim to expand

      • Can the code in my "Successful form submission" WF be invoked from a function?

        Can "Successful form submission" be invoked from a function? Data gets into a form manually and programatically. My code in "successful form submission" is good and I want to reuse it/call it, from another function which does Insert Into How to achieve

      • How do I get the Text Account name instead of the Reference number?

        Good Morning everyone! I am very new to zoho analytics. I'm trying to create some pivot tables and when I add the Account or Division name it comes up as a reference number. From my research, I need to use a lookup. I'm having a really hard time understanding

      • Subform Time field to string.

        Good afternoon All. I have a Subform 'Delivery_Receiving_Hours' that captures Day (Dropdown), Time_Open (Time), and Time_Close (Time). I need to capture this data and send it to a multiline field in the CRM. The code, posted below, below will capture

      • Update a lookup field in CRM from Creator using deluge

        I have a Creator form that creates a new account. When it creates the new account in the Accounts Module, I need it to also populate the Parent Account, which is a lookup field coming from the Module Parent Accounts, field Parent Account Name. I have

      • How to call a Creator function which is in a different Creator application?

        How to call a Creator function which is in a different Creator application?

      • SMS Keyword Tracking in Zoho CRM From Zoho Campaigns

        Is there a way to track SMS leads in campaigns by associating them with specific keywords or codes? Additionally, can these leads be pushed to the CRM while retaining the keyword for tracking and reporting purposes?

      • Map fields from module X to a lookup field in subform in module Y

        Hi there In the 1st screenshot attached, you can see a subform in myLeads module. You can see that there is a number already filled there - that is the 'Property ID' and it is a single line field. It is the 'Property ID' of an entry I have in another module - The 'Properties' module - take a look at the 2nd screenshot. In this second attached image, you can see the fields 'Property code' - this is basically the number that is mapped in the 'Property ID' field in the subform in the Leads layout. We

      • Projects Multiselect API

        Having troubles setting a mutli select field via API. updateMap = map(); updateMap.put("UDF_MULTI1","picklist_id1,picklist_id2"); updateProjectRes = invokeurl [ url :"https://projects.zoho.com/restapi/portal/XXXX/projects/" + projectID + "/" type :POST

      • Bill quantity received / PO quantity

        PO's are raised & often the quantity received is greater than the PO quantity, so when we receive the bill & adjust the quantity on the bill we get.... Quantity recorded cannot be more than quantity ordered. This necessitates the adjustment of the PO

      • ZOHO BOOKS - RECEIVING MORE ITEMS THAN ORDERED

        Hello, When trying to enter a vendor's bill that contains items with bigger quantity than ordered in the PO (it happens quite often) - The system would not let us save the bill and show this error: "Quantity recorded cannot be more than quantity ordered." 

      • Work Orders / Bundle Requests

        Zoho Inventory needs a work order / bundle request system. This record would be analogous to a purchase order in the purchasing workflow or a sales order in the sales cycle. It would be non-journaling, but it would reserve the appropriate inventory of

      • Next Page