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

      • Bug Report and Suggestions for Improvement in Zoho Applications

        Hi Zoho Team, I’d like to report a few bugs and improvement suggestions I’ve noticed while using Zoho products: Zoho Cliq Video Call: The camera sometimes turns off automatically during video calls. This seems to be a bug — please check and fix it. Zoho
      • Need Help: Updating Related Records via Subform Entries in Zoho Creator

        Hi everyone, I’m trying to set up a workflow in Zoho Creator where each row in a subform updates related records in another form automatically. Here’s the situation: My main form (e.g., “Receipts”) contains a subform where each row selects a related record
      • Zoho Desk - Community - Customer Portal - Description Field UX Improvement

        Hi Zoho Desk Team, As a prolific user of Zoho Cares Community, I find it very frustrating that I cannot increase the size of the Description box (this one which I am typing this message). Many apps with multi line text fields have a small handle in the
      • Add the same FROM email to multiple department

        Hi, We have several agents who work with multiple departments and we'd like to be able to select their names on the FROM field (sender), but apparently it's not possible to add a FROM address to multiple departments. Is there any way around this? Thanks.
      • Can I change the format of the buttons in the email templates?

        Hi all! We have been working hard trying to brand our email templates, and have some way to go yet. One of the things we can't seem to edit is the green ${Cases.CUSTOMER_PORTAL_BUTTON} button and the font of the View Ticket text. Is there any way of doing
      • Introducing parent-child ticketing in Zoho Desk [Early access]

        Hello Zoho Desk users! We have introduced the parent-child ticketing system to help customer service teams ensure efficient resolution of issues involving multiple, related tickets. You can now combine repetitive and interconnected tickets into parent-child
      • Zero Personalization of the File Sharing Experience

        By now (2025) this is the maximum level of personalization available for a Zoho sharing link. We gently asked Zoho if we could modify at least the background, and they replied that it cannot be customized. We're truly disappointed – and surprised every
      • Lead to Contact Conversion with multiple email address fields

        We are a B2C business with a strong repeat cycle, and as such it's not uncommon for customers to use multiple email addresses with us. We have both our Contacts & Leads modules set up with 3 email fields. (Primary Email / Secondary Email / Historic Email)
      • Introducing Dark Mode / Light Mode : A New Look For Your CRM

        Hello Users, We are excited to announce a highly anticipated feature - the launch of Day, Night and Auto Mode implementation in Zoho CRM's NextGen user interface! This feature is designed to provide a visually appealing and comfortable experience for
      • Does Thrive work with Zoho Billing (Subscriptions)?

        I would like to use Thrive with Zoho Billing Subscriptions but don't see a way to do so. Can someone point me in the right direction? Thank you
      • How to display two measures (sales and price) divided by categories on one line chart

        Hi everyone, I’m having trouble figuring out how to display two columns on a line chart with category breakdowns. What I need is a line chart where one line represents Sales and the other represents Price. However, the Price data is divided into around
      • Exporting Charts from ZohoCRM

        Hi...I'm relatively new to ZohoCRM, but very happy with it so far. I have all my leads and potentials accurately entered, and like the reports that I can view, with charts at the top of the data. But when I export the data, I'm receiving only the data, whether I export as excel, csv or pdf. How can I export both the chart and the data? In case it makes a difference, I'm using the free version right now. I tried researching the other editions to see if a paid version of the software offered the ability
      • Automatically Update Ticket Status in Zoho Desk Based on Actions in Zoho Projects

        Hi Zoho Desk Team, Hope you’re doing well. We’re using the Zoho Desk–Zoho Projects integration to manage tasks related to customer tickets, and it works well for linking and tracking progress. However, there are a few important automation capabilities
      • sms long credits

        I’m trying to purchase Long Code credits so I can send SMS campaigns to my contacts. However, when I click the “Buy Now” button, the page appears blank and doesn’t load any purchase options. Could you please assist me in purchasing the Long Code credits
      • Tip #48- Power Your AI Workflows with Zoho Assist on Zapier’s MCP- 'Insider Insights'

        We’re thrilled to announce that Zoho Assist is now part of Zapier’s Model Context Protocol (MCP), bringing remote support automation right into your AI ecosystem. What is MCP? The Model Context Protocol (MCP) is Zapier’s new framework designed to connect
      • Insert Cookie Policy in Zoho Sites

        Hello, i need to insert a banner on my site because i'm in Italy so i have to respect EU laws for Cookie Policy and Privacy Policy. I see that i need to insert a code in <head> section of my site to show a banner/popup with cookie info. How i can do this? Thank you Luca
      • Marketing Tip #1: Optimize item titles for SEO

        Your item title is the first thing both Google and shoppers notice. Instead of a generic “Leather Bag,” go for something detailed like “Handcrafted Leather Laptop Bag – Durable & Stylish.” This helps your items rank better in search results and instantly
      • Customer Parent Account or Sub-Customer Account

        Some of clients as they have 50 to 300 branches, they required separate account statement with outlet name and number; which means we have to open new account for each branch individually. However, the main issue is that, when they make a payment, they
      • Forced Logouts - Daily and More Frequent

        In the last month or so, I've been getting "power logged out" of all of my Zoho apps at least daily, sometimes more frequently. This happens in the same browser session on the same computer, and I need to re-login to each app separately after this happens.
      • Paste issues in ZOHO crm notes

        Hi, since a week or so I have issues with the paste function in ZOHO CRM. I use "notes" to copy paste texts from Outlook emails and since a week or so, the pasting doesnt function as it should: some text just disappears and it gives a lot of empty lines/enters.....
      • ENTER key triggering Submit

        Is it possible to stopped the ENTER key from the mandatory triggering of the Submit button on Creator form? I want forms submitted "ONLY" when the Submit button is pressed. 
      • Is it possible to assign Client user to external task ON PROJECTS' TEMPLATES?

        Is it possible to assign Client user to external task ON TEMPLATES PROJECTS?
      • Draft & Schedule Emails Directly in Bigin

        Greetings, I hope all of you are doing well. We're happy to announce a few recent enhancements we've made to email in Bigin. We'll go over each one in detail, but here's a quick overview: Previously, you couldn't draft or schedule emails in Bigin, but
      • Zoho CRM Workflow and Function Backup Options

        Hi everyone! I have been able to make several backups of my CRM data and noticed that the Workflows and Functions are not included in these backups. To my knowledge, there is no backup feature for workflows and functions, which is problematic in of itself.
      • Cliq does not sync messages after Sleep on Mac

        I'm using the mac app of Cliq. When I open my mac after it was in sleep mode, Cliq does not sync the messages that I received. I always have to reload using cmd + R, which is not what I want when using a chat application.
      • Link to images

        I have added images in pages. I would like to link those images with linked in URL so that they open in new window. There is an option of image -> link but I am not able to use the same to open URL in new window. Please check the attached image. Can you
      • Canvas View - Print

        What is the best way to accomplish a print to PDF of the canvas view?
      • What's New in Zoho Analytics - October 2025

        Hello Users! We're are back with a fresh set of updates and enhancements to make data analysis faster and more insightful. Take a quick look at what’s new and see how these updates can power up your reports and dashboards. Explore What's New! Extreme
      • Respond faster and smarter with Zia in your IM Inbox

        You’re in the middle of a busy chat queue. New messages keep popping up. One customer sounds upset. Another is asking a long list of questions. You need context. You need speed. You need help. That’s exactly when Zia Insghts jumps into action. It shows
      • Meeting impossible to use when sharing screen

        he Meeting tool in Brazil is practically unusable when sharing anything, whether it’s a presentation or simple navigation. When accessed via Cliq, the situation gets even worse: even basic calls fail to work properly, constantly freezing. And as you are
      • Let's Talk Recruit: Meet Zia, your all-in-one AI assistant (Part-2)

        Welcome back to Let’s Talk Recruit series. In Part 1, we introduced Zia and how AI is reshaping the way recruiters work. This time, we’re taking a closer look at how far Zia has come and how each update continues to simplify your everyday tasks. When
      • WARNING : Smart list automatically unsubscribes your contacts.

        I created a smart list of my team members based on the criterion that their email domain was @_____. The list refreshed as expected — but it ended up unsubscribing almost all members of my team. I contacted support, but it took two months to get a reply,
      • Export Purchase orders as Excel

        Is it possible to export purchase orders as excel rather than PDF? Our suppliers don't want orders made in PDF, they need it to be excel
      • 5名限定 課題解決型ワークショップイベント Zoho ワークアウト開催のお知らせ (10/31)

        ユーザーの皆さま、こんにちは。Zoho ユーザーコミュニティチームの中野です。 10月開催のZoho ワークアウトについてお知らせします。 今回はZoomにて、オンライン開催します。 参加登録はこちら(無料):https://us02web.zoom.us/meeting/register/BGYTysOnSqa9LA9eY2IKww ━━━━━━━━━━━━━━━━━━━━━━━━ Zoho ワークアウトとは? Zoho ユーザー同士で交流しながら、サービスに関する疑問や不明点の解消を目的とした
      • Subforms in Stateless Forms

        Stateless Forms are very useful, but we can't build complex data entry pages without Subforms. Can this be implemented? This would make Stateless Forms truly powerful.
      • Custom Function not getting package details when triggered from Workflow Rules.

        I have a custom function for Packages that submits a form in our Creator app that we use to generate custom shipping labels (internal staff complete deliveries so we cannot generate shipping labels straight from Inventory). When the function is executed
      • Billing Preferences per Account

        Hello, We are trying to setup Billing Preferences in Zoho Desk to set up a different pricing per account. We charge different pricing per hour per customer/account. Account A = 100 per hour Account B = 125 per hour In the Billing Preferences in Time Entry
      • Time entry preview for custom time entry templates.

        Our company needed time entries in a specific format to document our client interactions. Since we are using a custom time entry layout, we have lost the "preview" on the time entry tab. Using the default time entry layout, you get a small preview of
      • Set to Review for all

        We are testing the use of Writer as part of an internal review process for statement of work documents and have found that when the document is changed from Compose to Review by one person, that is not reflected for all others who view the document. Is
      • Implementing a Self-Invoicing/Vendor Portal in Zoho Creator

        Hello Zoho Community / Creator Experts, We would like to build a Self-Invoicing Portal (Vendor Portal) in Zoho Creator for our external contractors. Our goal is to allow approved contractors to log in, submit their invoice details (hours worked, project
      • Next Page