Tip of the Week - Spot Risky Sales with Conditional Formatting

Tip of the Week - Spot Risky Sales with Conditional Formatting

In Zoho Analytics, small tweaks can lead to big insights. One such feature is Conditional formatting based on other columns, your key to instantly spotting where sales success is overshadowed by product returns.

Our tip this week shows you how to apply conditional formatting across columns to uncover products and categories that look like top performers in sales but reveal a different story once returns are factored in.

The Big Picture

High sales don’t always mean healthy business. A category may dominate revenue, but if product return rates are unusually high, your profits and customer trust take a hit. Looking only at sales hides this risk. 
Conditional formatting based on return rates bridges that gap. It helps you go beyond surface numbers and focus on product quality and customer experience.

In this demo, we’ll start with a pivot table arranged as follows:
Columns: Month
Rows: Product Category
Data: Sales (USD), Return Rate (%)

Get ready to see how sales dominance changes month to month and how return rates reveal a deeper layer of truth.

We’ll highlight three eye-catching zones using conditional formatting:
  1. Healthy Zone - Low returns
  2. Warning Zone - Rising returns
  3. Critical Risk - Unacceptable return rates
By the end of this demo, sales won’t just be tall bars on your pivot; they’ll instantly tell you which categories are fueling sustainable growth, and which ones are silently eroding your margins.

Check out the video here:
​

Steps to Apply

  1. Open your Pivot Table.
  2. Creating Return Rate Formula:
    1. Click Add Aggregate Formula.
    2. Enter Formula name as Return Rate.
    3. Define the metric as below:
      4. sum("Sales return Data"."Returns Qty")/(sum("Sales return Data"."Sales Qty"))*100
      This formula calculates the percentage of sold items that were returned, giving you the Return Rate % for each product category and month in your pivot.
    4. Click Save.

  3. Set up your pivot table as shown below.
    1. Columns: Month
    2. Rows: Product Category
    3. Data: Sales (USD), Return Rate (%)

  4. Hide the Return Rate % column from the pivot as shown below.

  5. Click Visuals and select Only Data Bars.
  6. Right-click on any Sales cell and select Conditional Formatting.

  7. In the Conditional Formatting dialog, under Based On, choose Return Rate (%).
  8. Define three conditions based on the following zones:
    1. Critical Risk - Set the condition as Greater than or Equal to 10 and choose Red fill in Additional Formatting options.
    2. Warning Zone - Set the condition as Between 5 to 10 and choose Amber fill.
    3. Healthy Zone - Set the condition as Less Than or Equal To 5 to 10 and choose Amber fill.

  9. Click OK to save the conditions.

What you should see

  1. Green Sales Bars where return rates are low → sustainable business.
  2. Amber Bars where returns are rising → early warning.
  3. Red Bars where sales are hit by high returns → high-priority fix.

With one glance, your pivot now tells a double story: who’s leading in sales and who’s at risk due to high returns.

Best Practices

  1. Highlight what matters most: Focus on key risk signals like high return rates or unexpected spikes. This keeps the pivot sharp and attention where it belongs.
  2. Use KPI-driven thresholds: Base your rules on meaningful KPIs (like Profit Margin % or Return Rate %), not arbitrary numbers. This ensures the colors always map to business impact.
  3. Keep colors intuitive: Stick to natural associations: Green = Healthy, Red = Risk, Orange = Caution. This makes insights instantly recognizable for everyone.
  4. Pair visuals for impact: Don’t stop at colors. Combine conditional formatting with Data Bars to highlight magnitude, or Sparklines to reveal trends over time. Layering visuals makes patterns clearer without adding extra clutter.
  5. Test across different data ranges: For broader cues, try Color Bands to show intensity (like a heatmap of return rates) or Icon Bands to flag quick signals
  6. Avoid overlapping rules: Overlaps can confuse users. Keep each condition distinct to avoid conflicting colors on the same cell.
  7. Explore Color Bands and Icon Bands: If you want a broader visual cue beyond rule-based formatting, try Color Band (gradient shades that show intensity, like heatmaps) or Icon Band (symbols that signal performance trends). These are especially effective where quick scanning matters more than raw numbers.
  8. Think ahead for storytelling: Design your formatting with the end reader in mind. The goal isn’t to decorate numbers; it’s to tell a story at first glance.
When done right, conditional formatting turns pivots into a decision board. Your wins glow green, your risks flash red, and your opportunities pop out without a single extra click.

Keep Exploring

  1. Help Documentation
    1. Pivot Tables in Zoho Analytics 
    2. Conditional Formatting in Pivot 
    3. Visuals in Pivot
  2. Help Videos
    1. Creating Pivot tables
    2. Adding Visuals in Pivot table




      • Sticky Posts

      • Tip of the Week - Spot Risky Sales with Conditional Formatting

        In Zoho Analytics, small tweaks can lead to big insights. One such feature is Conditional formatting based on other columns, your key to instantly spotting where sales success is overshadowed by product returns. Our tip this week shows you how to apply

        • Recent Topics

        • Create Invoice and Invoice Items from Sales Order via API

          Currently, when creating an Invoice associated with a Sales Order via the API, it appears that I must manually include all of the items (line_items) even though they are already part of the Sales Order. My question is this: is it possible to raise an Invoice via the API based on all of the information associated with a Sales Order--such as the  items? In other words, do I always have to manually include the items (line_items) when raising an Invoice via the API when the Invoice is associated with

        • Outlook 2013 Calendar Syncs but "Related To" Field in Zoho is blank

          Outlook 2013 Calendar Syncs but Related To Field in Zoho is blank I expect the "Realted To" field to be populated with the calendar participants

        • Export a Course

          Is it possible to export a course from Zoho Learn to a SCORM file?

        • 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

        • Zoho learn Custom portal - networkurl & CustomPortalId

          I want to get my individual account’s networkurl and customportalId to use in this API: https://learn.zoho.com/learn/api/v1/portal/<networkurl>/customportal/<customportalId>/manual How can I retrieve the networkurl and customportalId using the API? I

        • Consumer Financing

          Does Zoho currently have a payment gateway (such as Stripe, Square, etc) which offers financing for customers? So, let's say the estimate we give the customer is greater than what they can afford at the time, but we can sell the service now, letting them

        • Intégration de la gestion des Passkeys dans Zoho Vault

          Zoho Vault est depuis plus d’une décennie une solution fiable pour les entreprises : pour la gestion, le partage et le stockage des mots de passe. En 2018, nous avons fait un pas en avant en proposant la connexion unique (SSO). Nous sommes fiers de franchir

        • Scan & Fill with double quote key/value pairs

          Hi, An old Ticket moved to a Topic/Idea: I love the idea of the new Scan & Fill as it nearly covers my previous request for a QR Scanner to read a multi-part QR Code. My QR Codes are hard-coded as below: {"key1":"value1","key2":"value2","key3":"value3"}

        • Analytics SQL Queries should allow # as comment

          # and // are very common for commenting in SQL. Not sure why analytics only allows /* and */ for commenting. Especially when # grays the line as if it's being commented out. This should be added for sure.

        • SalesIQ Operator Activity Reports in Zoho Analytics

          I'm busy building a dashboard in Zoho Analytics and I want to include SalesIQ stats in the dashboard, but I'm unable to get the statistics mentioned in the attached image. Any idea where I can get the stats for Operator Activity?

        • No longer can indent

          Hey there! Is it just me or were we used to be allowed to used tab or indent when writing. It’s not working right now, has this always been the case?

        • Free webinar alert! Seamless Transition with Lossless Migration: Zoho One + Zoho Mail

          Hello Zoho Mail Community! 🚀 Attention IT Admins and Email Administrators! Are you planning to migrate your organization's email to Zoho Mail within the Zoho One ecosystem? 📧 Join our exclusive webinar, Seamless Transition with Lossless Migration: Zoho

        • Add Resource to Export

          The Export Data feature does not include a column for the Resource field. Without this column, Zoho Bookings cannot be used by any business for resource-based services or event types e.g. room bookings, equipment bookings. It seems to be an oversight,

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

        • Mandatory field via deluge code

          I would like to ask you if it is possible to make a field mandatory via deluge script. For example, if I have a decision box and I click on it then I want a single line field to be mandatory. If uncheck the decision box then to do the single line as optional. I think it is not possible to do that and I have to do it via validation in 'on validate' field. 

        • Revenue Management: #1 What does it mean to "recognize" revenue?

          Earning revenue isn't just about collecting cash from your customers. It's about recording the income correctly and consistently. Revenue recognition is the process of deciding when and how to record revenue in financial statements so that they reflect

        • Power of Automation :: Auto-Populate Integration Field in Projects with CRM Account Data

          Hello Everyone, A custom function is a software code that can be used to automate a process and this allows you to automate a notification, call a webhook, or perform logic immediately after a workflow rule is triggered. This feature helps to automate

        • Zoho Forms and ChatGPT - populating a field using AI.

          I have a form where I would like the user to enter a response or query, and have another field populated using AI. For example, user enters Field 1, AI populates Field 2 in response. I want to be able to wrap some additional instruction text around the

        • campo tag para api

          debo conectarme a una api de zoho inventory y ocupo tomar el campo tag para poder asi jalar el articulo que cuente con el campo correcto en tag ejemplo que tag existen carro y avion que cuando busque los articulo con tag carro arroje solo estos por mas

        • Uploading file as attachment to Zoho CRM

                 Hi,   I am trying to attach a file to a Zoho CRM contact using Zoho Flow. Right now, I try to do it through the “Upload File” field in Zoho CRM (In my screenshots, it’s called Téléchargement du fichier 1).   Here is what I tried:   Case 1: Webmerge document The Flow is called “Custom Function” (see screenshot 101).   Step  1: Creating a Webmerge document (screenshot 99)   Step 2: I use “Update module entry” to upload the created file. I upload Webmerge’s “Document” in my “Téléchargemet du

        • Zia Answer Bot - Create Ticket

          Surprisingly, the current iteration of Zia will try to answer a question and unless you have "transfer to SalesIQ chat" enabled, it won't create a ticket for the user or offer them a method to create a ticket. We don't want it to create chats for us,

        • meassure leads phases

          Hi, I need to create a table to meassure the time that a lead stay in blueprint phases. the phases are first contact, second contact, lead spam, contacted, appointment. any idea? I have attached an example

        • In the Custom Module I have 500 Records , this 500 record only want to view to the specific user only example user A ,

          In the Custom Module, I have 500 Old records that should only be visible to a specific user, for example, User A. Any new records created from today onwards should be visible to Record owner in the Custom Module. Pls help how i achive this .

        • Invoice template, how to change the text under "Notes" and "Terms and Conditions"

          In "Invoice templates", there are two text/info sections at the bottom:"Notes" and "Terms and Conditions". It is possible to change the names of these two headings, but how is it possible to change/alter the text under it. As a standard it says "Thank you for your business" under Notes - I need to change it into something different- How? Thank you.

        • How to reply to thread via API

          We have built a webapp for our customers that uses the Zoho Desk API to enable each customer to view their full list of tickets, view individual tickets and raise new tickets. The Zoho Desk API doesn't have the ability to reply to a ticket/thread. Replies

        • Sending merged mail templates for signatures fail since today

          We have ZOHO one, we use merge templates in CRM to edit in ZOHO Writer, and from there send it for signature through zoho sign. This all worked up until today, suddenly we read in the log that the merge is succesfull but the sending for signature failed.

        • Feature Request - Make Lead List Larger and Adjustable

          Hi LandingPage team, I recently started using LandingPage and I am happy to share my feedback to help improve the app. I've noticed on the Leads page, there is no option to make the columns wider. It would be great if the comlumns expanded to fit the

        • Zoho Projects - Pin Recent Projects

          Hi Projects Team, It would be great if I could "pin" projects on the Recent Projects list in Zoho Projects. We have some internal projects which we regularly have to add time and some regular client projects. It would be great if I could pin those projects

        • ZDK Error

          I get this error when trying to trigger a CRM Function from Client Script: Uncaught (in promise) ZDKError: {"code":"NOT_ACTIVE","details":{"api_name":"activate_client_from_prospect"},"message":"api is inactive for the given custom function","status":"error"}

        • "Disbursing product components in phases, monitoring them, and displaying only the final product."

          i have a product composed of multiple components, and these components will be delivered to the customer in batches. However, the final invoice should only show the finished product. How can I issue (or release) the components and track their delive

        • Followed Subtasks doesn't show up in the Subtasks Section

          I have a task assigned to me now in the same task, there's a subtask and I am added as the follower on that task Even though I am a follower I still don't see that in the subtasks section The view permission for the profile is Related It's supposed to

        • Tip #39- Strengthen account security with Multi-factor Authentication (MFA) – ‘Insider Insights’

          Securing your organization's data begins with verifying that only the correct individuals have access to it. One of the simplest yet most effective ways to accomplish this is to enable Multi-factor Authentication (MFA) within Zoho Assist. MFA introduces

        • Automate timeout chat tracking with Workflows in SalesIQ

          With our feature-packed Nova release, Workflows has become one of the most powerful tools in Zoho SalesIQ. They let you automate follow-up actions when key events occur, such as when a chat ends, a visitor leaves a bad/good rating, or a lead is updated.

        • Mass edit / Mass update products

          Hi, Is there any way to mass update or bulk edit product fields in Zoho Inventory?

        • Automatic Verification of IMAP Integration Status

          Our sales staff have their O365 email integrated with CRM, over time this integration requires re-authentication via the UI. I can manually check the integration status by accessing Settings -> Channels -> Email -> Email Sharing -> "Configuration Type"

        • Tip of the Week #68– Share and access files faster with Zoho WorkDrive extension.

          Have you ever wasted time searching for the right file to attach to your emails—or worried whether the right people could access it? Without proper sharing settings, files might end up inaccessible to teammates or, worse, visible to people who shouldn’t

        • CV-Library: The Newest Source Booster in Zoho Recruit!

          We’ve expanded your sourcing toolkit — CV-Library, one of the UK’s largest and most trusted online job boards, is now available as a Source Booster in Zoho Recruit. This gives recruiters instant access to millions of UK-based candidate profiles, all without

        • Zoho AI Translate Task as Rest API

          I cant find any docs on how to use Zoho AI Translate Task from a rest api call https://www.zoho.com/deluge/help/ai-tasks/translate.html I am working on a custom Widget and I dont think I can execute zoho deluge ai translate task from a custom widget.

        • Is there the possibility to book less than 250 customer portal users?

          If you use the Creator, which is included in Zoho ONe, you can create a customer portal and give access to a maximum of 3 external people / customers, right? On the Creator website I saw that you can add 250 users for 100€/month. However, we don't need

        • Display Parts custom fields in the Work Order

          Hello, I see that is it possible to add custom fields in the Parts module. Would it be possible to also add those custom fields in the Work Order Module under Parts? This would be very useful for customer who want to showcase more information about parts.

        • Next Page