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

    Nederlandse Hulpbronnen


      • Recent Topics

      • Extend GitHub Commit Integration to Tasks in Zoho Projects

        Hello Zoho Projects Team, We hope you are doing well. We would like to submit a feature request regarding the current GitHub integration in Zoho Projects, specifically the ability to link commit information. Current Limitation: As of now, Zoho Projects

      • Zoho Projects API requests limit question

        Hello, Regarding the API request limit of 100 requests per 2 minutes, I have implemented a control mechanism which, if the limit is reached, will wait the required amount of time before sending new requests. However, this mechanism seems to be failing,

      • What is the esacalation email for Zoho Support/ Zoho One/ Zoho Workplace ?

        Hello, My business has been stalled due to Zoho duplicating workdrive with switching on zoho one trial. There are two workdrives - one with my files mapped to workplace and one empty mapped to zoho one.-- integrationg programs are super consfused and

      • How to show Assemblies AND component items in a report

        Hi Is there any way in Analytics to create a report that shows the Composite Item AND the Component Items with mapped quantity? It seems that the component item is not exposed in any table that I can find. Also, the same question but for Stock on Hand.....this

      • How can I use the API to add a drop-shipping address to a sales order for one-time use?

        I need to be able to add a drop-shipping address for one-time use to a sales order via the API. Adding every such address to the contact (customer), then feeding the shippingaddress_id into the sales order, is not an acceptable approach; we have some

      • Mass Update Option for Changing Work Order Owner

        Hello FSM Team/Mr. Abid, We would like to inquire if there is an option to mass update or bulk change the Owner of Work Orders in Zoho FSM. Current Situation: At the moment, the Owner of a Work Order can only be changed individually, which becomes time-consuming

      • Make and receive calls from Bigin's Android app

        Hello everyone! We're happy to announce that Bigin's Android app (version 2.0.0) now supports built-in telephony, RingCentral, and Zoho Voice, all of which enable users to make and receive calls from the Bigin app. You can only configure the telephony

      • Account Watchlist: Order of accounts in the watchlist

        Is there a way we can sort the order in which accounts are shown in the accounts watchlist in Zoho Books dashboard?

      • GST composition scheme dealer - treatment for client expenses reimbursement

        Hello, I am new to GST and need your help. I am registered as a composition dealer and attract 6% gst charges. In bill of supply, there are also expenses done on behalf of client for which I seek reimbursement AT ACTUAL with NO MARKUP. As I understand,

      • Zoho Books: tax is not automatically pulled from product-data anymore - why?

        Hi, until a short time ago, you could set a default taxrate for each product/item. This taxrate automatically appeared each time the item was chosen in an invoice or quote. Why does this not work anymore? The field is still there at the product record,

      • Kaizen #237 - Zoho CRM APIs: GraphQL & COQL- When to use which?

        Hello everyone, Welcome to another week of Kaizen. This Kaizen post discusses Zoho CRM GraphQL APIs and COQL APIs and is meant to be a decision guide on selecting between them. It aims to help developers to choose the most efficient approach in different

      • Deluge Update Function

        I am struggling to get this function to work no matter what changes I make - this is the constant error (my code is 30 lines long): Data type of the argument of the function 'get' did not match the required data type of '[BIGINT]'at lineNumber 31 I am

      • Add a way to connect Log360 Cloud logs with Zoho analytics

        Hi, Several month ago Log360 Cloud was added to zoho one - and this is great. But as far as I see there is no prebuilt way to connect Zoho analytics to the logs we have in Log360 Cloud. Please add a prebuilt connection like we have for so many other zoho

      • Option to Convert Request to a Specific Work Order (Parent WO Linking)

        Hello FSM Team/Mr. Abid, We would like to inquire if it is possible to convert a Request into a specific existing Work Order (as a child or linked Work Order) instead of always creating a new one. Context: An inspection/emergency Work Order is completed

      • Automation of CRM Items to FSM Work Order (Parts Section)

        Hello FSM Team, We would like to inquire about the possibility of automating the transfer of item details from CRM to FSM Work Orders (Parts section), specifically for LPO WO Job type. Currently, in CRM, we have detailed item information including: 1.

      • Introducing Product Catalog in Bigin

        Greetings, I hope all of you are doing well. For many small businesses, sharing products and collecting customer interest often involves multiple tools, manual follow-ups, or even building a full ecommerce website. To address this challenge, we're excited

      • Creating a stakeholder register for a Project

        Should we connect and/or associate Projects to Contacts to build a stakeholder register? If a Contact is associated to more than one Project and has different roles across the Projects, what is the best way to parse this information?

      • Displaying Rate Inclusive of GST per unit

        Hello, Currently when I enter the pricing of a line item it shows the overall amount (price including tax * quantity). I would like to see price/unit inclusive of GST. Is there any way to simplify this and to be able to view it? I've attached my current

      • Announcing Zoho Sheet desktop app for macOS and Windows (Beta)

        Hello Sheet users, We know you’ve been waiting for this one. It has always been the top priority on our roadmap to provide a single native desktop app for macOS and Windows that works both online and offline. Today, we are excited to announce that the

      • Allow Zia Agents using Zoho One Account

        When I went to try Zia Agents, it forced the creation of a new, separate account. This seems counter-intuitive to me. I don't want to manage a separate account. Plus, aren't Zoho One users the ones most likely to adopt Zia Agents most quickly? It seems

      • Adding Custom Status Options in Work Order Action Menu

        Hello FSM Team, We would like to inquire if it is possible to add more custom options in the Work Order action/status dropdown (e.g., Cancel, Terminate, Non-Billable, Void, etc.). Currently, the available options are limited, and we are unable to customize

      • 商談の特定ステージ選択時に、集計項目の入力を必須化したい

        こんにちは。Zoho CRMの商談管理で以下を実現したいです。レイアウトルールでは、集計項目を条件に選択できず実現できておりません。よい方法があればご教示いただけると嬉しいです。 ■やりたいこと 商談で特定のステージに変更する場合に、特定の集計項目（同じ商談データ内の項目。以降、集計項目Aとする）を入力必須化したい。 ■概要、状況 ・集計項目Aは、別のオブジェクトで、商談に関連する月ごとの売上を集計している数値項目 ・見積を顧客に提示した際に、集計項目Aに見積金額と一致するようにデータ登録する運用をしている

      • Bank Fees - Allow User to Choose Expense Account

        Bank fees from Customer Payments can only be debited against the system Bank Fees account due to hard-coding. The user should be able to select which expense account those fees are debited against. The use case is pretty straightforward. A business like

      • How do I change the account bank charges are charged to?

        I want bank charges charged to my Credit Card Fees account. Is there a way to do this?

      • Feature Request: Estimated Delivery Date Display on Product Pages

        Dear Zoho Commerce Support Team I am writing to submit a feature request that I believe would greatly benefit all Zoho Commerce merchants: the ability to display an estimated delivery date directly on the product page. Today's e-commerce customers expect

      • Automated Sales Order Fulfillment Based on Inventory Availability (Allocation / Commitment Modes)

        Hi everyone, I recently reached out to Zoho Inventory support regarding a workflow challenge and wanted to share both the issue and their response here for visibility. Zoho confirmed: “Zoho Inventory does not currently support automatic notifications

      • Set Mandatory Lookup Fields in Ticket Layout

        I added a custom module called 'Site' in the desk. I also added a lookup field 'Site' in the ticket layout. Now, I want the 'Site' field to be mandatory in the blueprint transition. How can I do that?

      • Properly Capture Dropdowns & Hover Elements When Recording a Window/Tab

        Hi Zoho WorkDrive Team, Hope you are doing great. We encountered a limitation when recording a selected window or browser tab: Certain UI elements, such as dropdown lists, hover menus, and overlays, are not captured unless we record the entire screen.

      • Followed Tickets view

        Hello, following a ticket makes no sense at all / offers no additional value.... Of course I can install and use the add-on My Followed Tickets for Zoho Desk, but what does it do for me? Not much! It cannot be used across departments! The range of functions

      • Mail Merge preview won't load

        Hello! One of our users has not been able to use mail merge templates in CRM as the preview screen does not load. We have many templates for one of our custom modules and the users are used to this feature and have been using it for years without any

      • Zoho Desk - Event Calendar View

        Hi Desk team, Are there any plans to introduce a calendar or timeline view for Events in Zoho Desk? It would be very helpful if we could see Events visually in a calendar and/or timeline. This is very helpful when desk side support activities need to

      • Make Module Name In CRM 4 Everyone A link To List View.

        I've been using the new UI for a little while now and I have what I think is a simple suggestion that would add speed and ease to the UI At the top of the detail page for a module entry you show the module name as shown in the screenshot - if that could

      • mask Customer phone number and agents cant see customer phone number

        Is there any way we can integrate Zoom Phone with Zoho CRM while ensuring that customer phone numbers remain masked? We need a solution where agents can make outbound calls but cannot see customer phone numbers. Please let us know if there is any solution

      • Can Zoho CRM Emails be used in Zoho Analytics in any capacity?

        We're wanting to display details about Lead Activity in regular reports through Zoho Analytics but we're having difficulty integrating Emails at all. We'd like to be able to note when an email is received and when it is sent somewhere other than just

      • User can import contacts into Campaign even without permission

        Hi everyone! We have a role that's configured with "Import records: Calls" only, but when a user with that role enters a Campaign, the "Contacts" section shows a "Import contacts" button. Is this expected?

      • Allow moving closed Issues between Zoho Projects without reopening them

        Hello Zoho Projects Team, We hope you are doing well. We would like to submit a feature request regarding moving closed Issues between projects in Zoho Projects. Current Limitation: As of now, Zoho Projects allows users to move closed Tasks between projects,

      • Improve Back Navigation (X Button) Inside Nested Milestones, Task Lists, Tasks, and Issues

        Hi Zoho Projects Team, We would like to request an improvement to the X / close button navigation behavior inside milestones. Currently, the milestone area behaves as a nested hierarchy (tree structure): Project → Milestone → Task List → Task So when

      • Accept credit card payment for multiple invoices

        We have a problem with Zoho Books.  While we can charge a customer's credit card for an individual customer invoice, we cannot charge a customer's credit card for more than one invoice. In our business, it is common for a client to phone up and say, "Here

      • Zoho Books | Product updates | February 2026

        Hello users, We’ve rolled out new features and enhancements in Zoho Books. From Advanced Reporting Tags to the ability to mark projects as completed, explore the latest updates designed to improve your bookkeeping experience. Introducing Advanced Reporting

      • How is Covid affecting the 2021 Super Bowl Commercials

        Super Bowl, the annual championship game of the National Football league, USA has an incredible viewer base of 100 million viewers on an average and is also one of the most-watched television broadcasts in the USA of all time. Nine out of ten Americans

      • Next Page