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

        • How get stock name from other column ?

          How get stock name from other column ? e.g. =STOCK(C12;"price") where C12 is the code of the stock

        • Adding a developer for editing the client application with a single user license

          Hi, I want to know that I as a developer I developed one application and handed over to the customer who is using the application on a single user license. Now after6 months customer came back to me and needs some changes in the application. Can a customer

        • Download an email template in html code

          Hello everyone, I have created an email template and I want to download it as html. How can i do that? I know you can do it via the campaigns-first create a campaign add the template and download it as html from there. But what if i don't want to create

        • Attachment is not included in e-mails sent through Wordpress

          I have a Wordpress site with Zeptomail Wordpress plugin installed and configured. E-mails are sent ok through Zeptomail but without the included attachment (.pdf file) Zeptomail is used to send tickets to customers through Zeptomail. E-Mails are generated

        • Upcoming Changes to the Timesheet Module

          The Timesheet module will undergo a significant change in the upcoming weeks. To start with, we will be renaming Timesheet module to Time Logs. This update will go live early next week. Significance of this change This change will facilitate our next

        • Best way to schedule bill payments to vendors

          I've integrated Forte so that I can convert POs to bills and make payments to my vendors all through Books. Is there a way to schedule the bill payments as some of my vendors are net 30, net 60 and even net 90 days. If I can't get this to work, I'll have

        • Cant update image field after uploading image to ZFS

          Hello i recently made an application in zoho creator for customer service where customers could upload their complaints every field has been mapped from creator into crm and works fine except for the image upload field i have tried every method to make

        • Billing Management: #4 Negate Risk Free with Advances

          In the last post, we explored how unbilled charges accumulate before being invoiced. But what happens when businesses need money before service begins? Picture this: A construction company takes on a $500,000 commercial building project expected to last

        • Is there an equivalent to the radius search in RECRUIT available in the CRM

          We have a need to find all Leads and/or Contacts within a given radius of a given location (most likely postcode) but also possibly an address. I was wondering whether anyone has found a way to achieve this in the CRM much as the radius search in RECRUIT

        • Zoho CRM Inventory Management

          What’s the difference between Zoho CRM’s inventory management features and Zoho Inventory? When is it better to use each one?

        • Cannot Enable Picklist Field Dependency in Products or Custom Modules – Real Estate Setup

          Hello Zoho Support, I am configuring Zoho CRM for real estate property management and need picklist field dependency: What I’ve tried: I started by customizing the Products module (Setup > Modules & Fields) to create “Property Type” (Housing, Land, Commercial)

        • Get Workflow Metadata via API

          Is there a way to get metadata on workflows and/or custom functions via API? I would like to automatically pull this information. I couldn't find it in the documentations, but I'm curious if there is an undocumented endpoint that could do this. Moderation

        • Zoho Projects - Q2 Updates | 2025

          Hello Users, With this year's second quarter behind us, Zoho Projects is marching towards expanding its usability with a user-centered, more collaborative, customizable, and automated attribute. But before we chart out plans for what’s next, it’s worth

        • FSM setup

          So we have been tinkering with FSM to see if it is going to be for us. Now is the time to bite the bullet and link it to our zoho books and zoho crm. The help guides are good but it would really help if they were a bit more in depth on the intergrations.

        • Upcoming Updates to the Employees Module in Zoho Payroll (US)

          We've made a couple of updates to the Employees module in Zoho Payroll (latest version of the US edition). These changes will go live today. While creating an employee Currently, the Compensation Details section is part of the Basic Details step, where

        • Possible to Turn Off Automatic Notifications for Approvals?

          Hello, This is another question regarding the approval process. First a bit of background: Each of our accounts is assigned a rank based on potential sales. In Zoho, the account rank field is a drop-down with the 5 rank levels and is located on the account

        • ZOHO Creator subform link

          Dear Community Support, I am looking for some guidance on how to add a clickable link within a Zoho Creator subform. The goal is for this link to redirect users to another Creator form where they can edit the data related to the specific row they clicked

        • Allow Resource to Accept or Reject an Appointment

          I have heard that this can be done, is there any documentation on how?

        • Create new Account with contact

          Hi I can create a new Account and, as part of that process, add a primary contact (First name, last name) and Email. But THIS contact does NOT appear in Contacts. How can I make sure the Contact added when creating an Account is also listed as a Contact?

        • How To Insert Data into Zoho CRM Organization

          Hi Team I have this organization - https://crm.zoho.com/crm/org83259xxxx/tab/Leads I want to insert data into this Leads module, what is the correct endpoint for doing so ? Also I have using ZohoCRM.modules.ALL scope and generated necessary tokens.

        • Where can I get Equation Editor por Zoho Writer?

          I need to use Math Formulas in my document. Thank you.

        • Filtering repport for portal users

          Salut, I have a weird problem that I just cannot figure out : When I enter information as administrator on behalf of a "supplier" portal user (in his "inventory" in a shared inventory system), I can see it, "customer" portal users can see it, but the

        • Zoho Inventory. Preventing Negative Stock in Sales Orders – Best Practices?

          Dear Zoho Inventory Community, We’re a small business using Zoho Inventory with a team of sales managers. Unfortunately, some employees occasionally overlook stock levels during order processing, leading to negative inventory issues. Is there a way to

        • BUTTONS SHOWN AS AN ICON ON A REPORT

          Hi Is there any way to create an action button but show it as an icon on a report please? As per the attached example? So if the user clicks the icon, it triggers an action?

        • Dropshipping Address - Does Not Show on Invoice Correctly

          When a dropshipping address is used for a customer, the correct ship-to address does not seem to show on the Invoice. It shows correctly on the Sales Order, Shipment Order, and Package, just not the Invoice. This is a problem, because the company being

        • RFQ MODEL

          A Request for quotation model is used for Purchase Inquiries to multiple vendors. The Item is Created and then selected to send it to various vendors , once the Prices are received , a comparative chart is made for the user. this will help Zoho books

        • Will zoho thrive be integrated with Zoho Books?

          title

        • Product Updates in Zoho Workplace applications | August 2025

          Hello Workplace Community, Let’s take a look at the new features and enhancements that went live across all Workplace applications this August. Zoho Mail Delegate Email Alias Now you can let other users send emails on your behalf—not just from your primary

        • Unable to send message; Reason:554 5.1.8 Email Outgoing Blocked

          Hi, I sent few emails and got this: Unable to send message; Reason:554 5.1.8 Email Outgoing Blocked And now I have few days since I cant send any email. Is there something wrong I did? Also can someone fix this please

        • Want to use Zoho Books in Switzerland. CHF support planned?

          Hi, We're a Swiss company using other Zoho suite software and I discovered Zoho Books and other accounting SaaS when looking for an accounting tool. Do you intend to cover Switzerland and CHF based accounting anytime soon? Roy

        • Celebrating Connections with Zoho Desk

          September 27 is a special day marking two great occasions: World Tourism Day and Google’s birthday. What do these two events have in common (besides the date)? It's something that Zoho Desk celebrates, too: making connections. The connect through tourism

        • Adding Overlays to Live Stream

          Hello folks, The company I work for will host an online event through Zoho Webinar. I want to add an overlay (an image) at the bottom of the screen with all the sponsors' logos. Is it possible to add an image as an overlay during the live stream? If so,

        • Email Sending Failed - SMTP Error: data not accepted. - WHMCS Not sending emails due to this error

          I have been trying to figure out a fix for about a week now and I haven't found one on my own so I am going to ask for help on here.  After checking all the settings and even resetting my password for the email used for WHMCS it still says: Email Sending Failed - SMTP Error: data not accepted.  I have no clue how to fix it at this point. Any insight would be lovely. 

        • Zoho Flow - Update record in Trackvia

          Hello, I have a Flow that executes correctly but I only want it to execute once when a particular field on a record is updated in TrackVia. I have the trigger filters setup correctly and I want to add an "update record" action at the end of the flow to

        • Add Comprehensive Accessibility Features to Zoho Desk Help Center for End Users

          Hello Zoho Desk Team, We hope you're doing well. We’d like to submit a feature request to enhance the client-facing Help Center in Zoho Desk with comprehensive accessibility features, similar to those already available on the agent interface. 🎯 Current

        • Zoho CRM mobile: Support for rich text in multi-line fields, Image upload field, and more

          Hello everyone, We've made a few enhancements to the Zoho CRM mobile app to improve your experience. Here's what's new: Rich text support for multi-line fields (iOS) Image upload field support (iOS and Android) Tool tip markdown (iOS) Rich text support

        • Rename Record Summary PDF in SendMail task

          So I've been tasked with renaming a record summary PDF to be sent as part of a sendmail task. Normally I would offer the manual solution, a user exports the PDF and uploads it to a file upload field, however this is not acceptable to the client in this

        • in zoho creator Sales Returns form has sub form Line Items return quantity when i upate the or enter any values in the sub form that want to reflect in the Sales Order form item deail sub form field Q

          in zoho creator Sales Returns form has sub form Line Items return quantity when i upate the or enter any values in the sub form that want to reflect in the Sales Order form item deail sub form field Quantity Returned\ pls check the recording fetch_salesorder

        • Estimates with options and sub-totals

          Hi It seems it would be great to be able to show multiple options in an estimate. For instance I have a core product to which I can add options, and maybe sub-options... It would be great to have subtotals and isolate the core from the not compulsory items. Thanks

        • Optional Items Estimate

          How do you handle optional items within an estimate? In our case we have only options to choose with. (Like your software pricing, ...standard, professional, enterprise) How can we disable the total price? Working with Qty = 0 is unprofessional....

        • Next Page