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

    Nederlandse Hulpbronnen


      • Recent Topics

      • Zoho Forms API

        Is there any way to get all form entry list using API? Looking forward to hear from you
      • Multiple locations

        I have many customers (accounts) that have multiple locations, like bank branches. How can I manage them under one account?
      • Calendar won't load

        I run into this problem about once a week, where I try to open my Zoho calendar in a web browser, and it just gets stuck, waiting forever to load (see attached screenshot). I'm usually using Edge or Duck-Duck-Go browser. Sometimes it loads just fine,
      • Feature Request: Enhance Zoho Books Customer Approvals with WhatsApp, Multiple Approvers, Automated Reminders, and Workflow Automation

        Product: Zoho Books Module: Time Tracking → Customer Approvals Category: Feature Enhancement / Workflow Automation / Omnichannel Communication Description I would like to suggest several improvements to the Customer Approvals module in Zoho Books, particularly
      • Feature Request: Extend Zoho Books Customer Approvals API and MCP Support

        Product: Zoho Books Category: API Enhancement / OAuth Scopes / MCP Integration Feature: Customer Approvals (Timesheet Approvals) Description I would like to suggest an enhancement to the integration and automation capabilities of Zoho Books, specifically
      • Introducing PRM: Build, manage, and scale your partner ecosystem

        Growing a partner ecosystem is a good problem to have. What starts as a handful of partners can quickly turn into a maze of spreadsheets, emails, and disconnected processes. More partners mean more opportunities, greater reach, and new ways to grow
      • Tip: #14 Save words and phrases in Writer for faster writing!

        As you start working with more documents everyday, you'd find yourself using certain words and phrases across documents quite often. In this week's tip, let's discuss two easy ways to train Writer to remember your commonly used words—equipping you to
      • Introducing the Markdown editor in Zoho Learn

        Hello everyone, We're excited to announce the release of the Markdown editor in Zoho Learn. With Markdown editor, you can create and import articles as Markdown. You can write content using simple Markdown syntax and see how it looks with the built-in
      • Zoho ERP | Product updates | October 2026

        Hello users, A new month, a fresh set of updates in Zoho ERP. This month brings Netting, enhancements across our verticals, and more updates to help streamline your everyday work. Explore this month’s updates and see what’s new. Compliance with Revised
      • Zoho Forms - Feature Request - Embed iFrame or Dynaic URLs in Description fields

        Hi Zoho Forms team, I'm working with a client who uses Zoho Forms internally to collect information over the phone from potential work placement candidates. At the end of the form, the candidate needs to be booked in for an induction, which we've configured
      • BANK FEED - MAYBANK , provider from YODLEE IS NOT WORKING

        As per topic, the provider YODLEE is not working for the BANK FEED. It have been reported since 2023 Q3, and second report on 2023 Q4. now almost end of 2024 Q1, and coming to 2024 Q2. Malaysia Bank Maybank is NOT working. can anyone check on this issue?
      • Digest Octobre - Un résumé de ce qui s'est passé le mois dernier sur Community

        Bonjour chers utilisateurs, Bienvenue dans ce nouveau digest de la communauté Zoho France ! Vous y trouverez les discussions marquantes, les nouveautés produits, les conseils pratiques partagés et bien sûr les événements à venir. Le mois dernier, nous
      • Bank feeds - still a problem

        I'm really frustrated that the bank feeds only "sometimes" work.  I raised this problem once before and was advised that Zoho used Yodlee and feeds were only updated once a day.  That's fine but it only "sometimes" seems to work.  Eventually transactions appear but they sometimes seem to take days.  I know of other programs (Xero) that another business I am involved with uses Yodlee and they do not have this issue. My bank feeds are active,  I tried to manually refresh however Zoho tells me that
      • this is getting ridiculous

        My checking account bank feeds haven't worked since 6/4/21.  Multiple emails to Zoho since 6/10, and all they tell me is they have re-escalated my problem to their banking service provider team.  14 days and nothing is fixed. I'm just about ready to bail
      • No feedback from Zoho Books regarding Yodlee feeds for Investec

        I reported on 6 Feb 2025 that the Bank Feeds for Investec bank via Yodlee are not working. To date there has been no resolution. You are charging us for a suite of products where the functionality does not work. Please advise how you will refund me for
      • ongoing bank sync problems

        My bank feed broke May 14th 2021 Since then I just get an endless stream of we are working on it, "Kindly bear with us for sometime" from support Multiple times I have asked to speak to managers and half the time they don't call at all. If they do call
      • We must admit - Yodlee is an unreliable partner!

        Hello Zoho, Our Yes Bank account intergration is messed up since the beginning and the problem has not been addressed yet. With bad synchronization, poor categorization of transactions, duplicate/unknown copies of accounts, no support, geared at income/expenses,
      • Make your PDFs clearer with shapes

        Sometimes the best way to mark up a PDF is to point at it directly. Zoho PDF Editor includes shape tools that let you add circle, check and cross marks anywhere in your PDF, so you can flag content for review without extra comments. Here's what you can
      • Admin Logging in as another User

        How can a Super Admin login as another user. For example, I have a sales rep that is having issues with their Accounts and I want to view their Zoho Account with out having to do a GTM and sharing screens. Latest Update (27th April 2026): With the early
      • Project Migration

        We currently have two separate Zoho organizations: A Spark Robotic organization under Zoho CRM Plus A separate Contract Manufacturing organization under Zoho One We are working with Zoho Support to consolidate the CRM Plus organization into our existing
      • CNIL - Suivi de Pixel

        Bonjour à tous, À la suite de la nouvelle recommandation de la CNIL sur les pixels de suivi dans les e-mails, savez-vous si Zoho Campaigns permet : de conditionner le suivi des ouvertures au consentement de chaque contact ; de proposer un lien permettant
      • Live connection to DS files in Zoho Creator / ChatGPT

        As an idea, we are using ChatGPT to build with Zoho Creator (outside of Zia), which is works brilliantly to assist write deluge, provide test suggesitons etc. Currently we are uploading the DS files which are very point in time, it would be great to have
      • Improved Filtering and Tagging options for Notes

        We rely on the notes section across several modules and would love to have some more options in filtering, grouping, and tagging notes. We would like to be able to filter notes differently on a contact or deal and have that choice stay on that record
      • Action required: Update the Zoho Desk Client SDK URL in your extensions

        Hi Developers, To improve reliability and performance, the Zoho Desk Client SDK is now served from a new CDN domain. We request that you update your existing extensions with the new Client SDK URL given below. Note: Going forward, all newly created extensions
      • Decoupling Email Sending from Storage in CRM & Email Storage Add-on Purchases

        Hello everyone, We are excited to announce the rollout of the second phase of our email storage management, designed around a simple principle: your business communication should never be disrupted by storage limits. We've decoupled email sending from
      • Enhancement Request: Preserve Canceled Zoho Bookings Meetings in CRM

        I recently worked with Zoho Support on ticket 170383788 regarding what happens in Zoho CRM when an appointment is canceled in Zoho Bookings. Support confirmed that the current behavior is intentional: when a Bookings appointment is canceled, the corresponding
      • Edit PDFs using Zoho PDF Editor

        Hello users, In a previous article, we covered the organizing capabilities of Zoho PDF Editor. Here, we'll provide a quick recap of its editing features, including a few new additions. A few highlights of Zoho PDF Editor's editing capabilities: Add and
      • Feature Request - Ability to Customise Contact Info Card on Ticket Details View

        Hi Desk Team, I've added a "Contact Priority" and "Account Prioirty" field and it would be very useful to agents if they could see that information in the Contact Info card on the Ticket Details view. It would be great if we could choose some fields to
      • What is a realistic turnaround time for account review for ZeptoMail?

        On signing up it said 2-3 business days. I am on business-day 6 and have had zero contact of any kind. No follow-up questions, no approval or decline. Attempts to "leave a message" or use the "Contact Us" form have just vanished without a trace. It still
      • Customer portal: recommended way to scope lookup fields to the logged-in client?

        Hi all, I'm building a customer portal on Creator for our clients (we're a esting lab, so each client has their own sites, contacts and jobs). Planning stage, so I'd rather get the approach right before I build it. The portal forms need several lookup
      • Zia for Mail: AI-powered assistance for Email, Calendar, and ToDo in Zoho Mail

        Zia for Mail brings AI assistance into Zoho Mail for everyday email, calendar, and task activities. Administrators can integrate third-party AI providers, such as ChatGPT or Gemini, and decide which features are available to users. What is Zia for Mail
      • "Else / If" Statement in Zoho CRM Inventory Template

        Hello, I would like to use the below "Else If" in my Inventory Templates, but it's not working correctly. Essentially, if the field "Quote Payment Terms" is filled in, display that. If "Quote Payment Terms" is blank, display "Account Payment Terms" instead.
      • Office Integrator

        Our users are complaining the slowness and speed when working in Zoho Office. Explicitly when working in a sheet. Here are observed issues *Copy and pasting values from one cell into another takes 10-20 seconds to show it has done it. *Modifying a value
      • [ZohoDesk] Improve Status View with a new editeble kanban view

        A kanban view with more information about the ticket and the contact who created the ticket would be valueble. I would like to edit the fields with the ones i like to see at one glance. Like in CRM where you can edit the canvas view, i would like to edit
      • Subform UI Unresponsive

        Has subform with a couple of hundreds of rows but suddenly the you can't add any row nor edit any fields. The subform has 10 columns Any thoughts on this Zoho team?
      • Kaizen #267: Four Developer Tools, One Prompt: Cracking Custom Cases with the Zoho CRM Skill

        Hello everyone! Welcome back to another Kaizen post. In this Kaizen, we’ll see how the Zoho CRM Skill solves a custom use case that requires multiple Zoho CRM developer tools. You describe the requirement in plain language, and the skill identifies the
      • 【開催報告】 福岡 ユーザー交流会 NEXUS 2026/10/30 | CRM×AIの実践事例とZia Agents活用を紹介

        Zoho ユーザーの皆さん、こんにちは。 コミュニティグループの中野です。 10月30日(金)、福岡にてZoho ユーザー交流会 NEXUSを開催しました。 今回は、Zoho CRM × AIをテーマに、ユーザーさんによる実践事例、 Zia Agentsのデモ、参加者同士のグループワークなどを実施しました。 ご参加いただいた皆さん、ありがとうございました! 本投稿では、当日の様子を一部ご紹介します。 ユーザー活用事例 :Zoho CRM × AIを、実際の営業業務でどう使う? 登壇者:株式会社etika
      • KB Articles from Zoho Learn - Import as .md

        I love the concept of being able to use articles from Zoho Learn as knowledge base articles for Zia Agents. This will absolutely help with proper documentation and organization of company processes and SOPs. This was a great concept. However, even when
      • Service Account Admin for API Calls and System Actions

        Hello, I would like to request the addition of a Service Account Admin option in Zoho product. This feature would allow API calls and system actions to be performed on behalf of the system, rather than an active user. Current Issue: At present, API calls
      • Trigger workflows from SLA escalations in Zoho Desk?

        Hey everyone, I’m currently working with SLA escalation rules in Zoho Desk and ran into a limitation that I’m hoping someone here has solved more elegantly. As far as I can tell, SLA escalations only support fairly limited actions (like changing the ticket
      • Next Page