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

        • update linked contacts when update happens in account

          Hi, I have a custom field called Licence in the Accounts module. When someone buys a licence, I’d like to update a custom field in the related Contacts. How can I achieve this? I noticed that workflows triggered on Accounts only allow me to update fields

        • Problem Management Module

          I am looking for a Problem Management module within Zoho Desk. I saw in some training videos that this is available, and some even provided an annual price for it. I want an official confirmation on whether this is indeed available. This is not a particularly

        • Unable to explore desk.zoho.com

          Greetings, I have an account with zoho which already has a survey subscription. I would like to explore desk.zoho.com, but when I visit it while logged in (https://desk.zoho.com/agent?action=CreatePortal) I just get a blank page. I have tried different

        • Offline support for mobile app

          Accessing your files and folders from your mobile devices is now quicker and simpler, thanks to the power of offline support. Whether on an Android or iOS device, you can use the Offline function to save files and folders, so you can review them even

        • Zoho Desk KB article embedded on another site.

          We embed KB articles from Zoho Desk on another site (our application). When opening the article in a new tab, there is no issue, but if we choose lightbox, we are getting an error "To protect your security, help.ourdomain.com will not allow Firefox to

        • List of packaged components and if they are upgradable

          Hello, In reference to the article Components and Packaging in Zoho Vertical Studio, can you provide an overview of what these are. Can you also please provide a list of of components that are considered Packaged and also whether they are Upgradable?

        • Does Attari Messaging app have Bot option and APIB

          Hi, Does Attari also have Bot and API as we use in WhatsApp??

        • How to add application logo

          I'm creating an application which i do not want it to show my organization logo so i have changed the setting but i cannot find where to upload/select the logo i wish to use for my application. I have seen something online about using Deluge and writing

        • Zoho Analytics Regex Support

          When can we expect full regex support in Zoho Analytics SQL such as REGEXP_REPLACE? Sometimes I need to clean the data and using regex functions is the easiest way to achieve this.

        • Change of Blog Author

          Hi, I am creating the blog post on behalf of my colleague. When I publish the post, it is showing my name as author of the post which is not intended and needs to be changed to my colleague's name. How can I change the name of the author in the blogs?? Thanks, Ramanan

        • how to differentiate if whatsapp comes from certain landing page?

          I create a Zobot in SalesIQ to create a Whatsapp bot to capture the lead. I have 2 landing pages, one is SEO optimized and the other want is optimized for leads comes from Google Ads. I want to know from which landing page this lead came through WhatsApp

        • How to record company set up fees?

          Hi all, We are starting out our company in Australia and would appreciate any help with setting up Books accounts. We paid an accountant to do company registration, TFN, company constitution, etc. I heard these all can be recorded as Incorporation Costs, which is an intangible asset account, and amortised over 5 years. Is this the correct way to do it under the current Australian tax regulations? How and when exactly should I record the initial entry and each year's amortasation in Books? Generally

        • Show Payment terms in Estimates

          Hi,  we are trying to set up that estimates automatically relates payment terms for the payment terms we introduced on Edit contact (Field Payment terms).  How can it be done? Our aim is to avoid problems on payment terms introduced and do not need to introduce it manually on each client (for the moment we are introducing this information on Terms and Conditions.  Kind Regards, 

        • When dispatched to crew, assigning lead missing

          Hello, For the past two or three weeks, whenever an officer assigns Service Appointment to a team, the lead person is missing from the assigned service list. Therefore, we have to reschedule the SA and then the lead person becomes visible in the assigned

        • I want to transfer the project created in this account to another account

          Dear Sir I want to transfer the project created in one account to another account

        • Custom Bulk Select Button

          Zoho CRM offers the ability to select multiple records and invoke a Custom Button This functionality is missing from Recruit Currently we can only add buttons in the detail page and list But we cannot select Multiple Records and invoke a function with

        • Power of Automation :: Smart Ticket Management Between Zoho Desk and Projects

          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

        • BUG - Google Business Buttons - Add a button to GBP Post

          I am experiencing an issue with the "Add a button" feature when creating posts for my Google Business Profile (GBP) through Zoho Social. When I schedule or publish a GBP post and include a call-to-action button with a specific URL, the post itself publishes

        • Do you have software like Windows software?

          We want swadeshi software

        • Support for Custom Fonts in Zoho Recruit Career Site and Candidate Portal

          Dear Zoho Recruit Team, I hope you're doing well. We would like to request the ability to use custom fonts in the Zoho Recruit Career Site and Candidate Portal. Currently only the default fonts (Roboto, Lato, and Montserrat) are available. While these

        • Trying to export a report to Excel via a deluge script

          I have this code from other posts but it gives me an error of improper statement, due to missing ; at end of line or incomplete expression. Tried lots of variations to no avail. openUrl(https://creatorapp.zoho.com/<username>/<app name>/XLSX/#Report:<reportname>,"same

        • Need help to create a attach file api

          https://www.zoho.com/crm/developer/docs/api/v8/upload-attachment.html Please help me to create it... It's not working for while. Do you have some example?

        • Export view via deluge.

          Hi, Is it possible to export a view (as a spreadsheet) via deluge? I would like to be able to export a view as a spreadsheet when a user clicks a button. Thanks     

        • Zoho Inventory's latest shipping integration updates at a glance.

          Hello Users, We would like to share some important news about our latest improvements in the Shipping integration capabilities of Zoho Inventory that we achieved in 2024 with some of our major integration partners in key editions across APAC, North America,

        • Possible to generate/download Quote PDF using REST API?

          See title. Is there any way after a quote has been created to export to a PDF using a specified template and then download it? Seems like something that should be doable. Is this not supported in the API v2.0?

        • Creating an invoice to be paid in two installments?

          Hi there, I own a small Photographic Services business and have not been able to find a way to fit my billing system into Zoho, or any other Accounting software. The way my payments work is: 1. Customer pays 50% of total price of service to secure their

        • Bug in allowing the user to buy out of stock items

          Hi i want to allow the user to buy out of stock items, according to the commerce documentation if i disable Restrict "Out of stock" purchases it will, but it doesnt work, so i want to know if it had any relation with zoho inventory, and if theres any

        • Replace Lookup fields ID value with their actual name and adding inormation from subforms

          Hi everyone,  I wanted to see if someone smarter than me has managed to find any solutions to two problems we have. I will explain both below.  To start we are syncing data from Zoho CRM to Zoho Analytics and I will use the Sales Order module when giving

        • Can a Zoho Sites page be embedded into another website (outside Zoho)

          Hi All, We have a request from a client - they'd like to take one of our information pages created in Zoho Sites and embed it into their own website? I was told through an email with Zoho that this was possible >>Thank you for your patience regarding

        • Bug in allowing the user to buy out of stock items

          Hi i want to allow the user to buy out of stock items, according to the commerce documentation if i disable Restrict "Out of stock" purchases it will, but it doesnt work, so i want to know if it had any relation with zoho inventory, and if theres any

        • Transition Criteria Appearing on Blueprint Transitions

          On Monday, Sept. 8th, the Transition criteria started appearing on our Blueprints when users hover over a Transition button. See image. We contacted Zoho support because it's confusing our users (there's really no reason for them to see it), but we haven't

        • Zoho CRM Sales Targets for Individual Salespeople

          Our organistion has salespeople that are allocated to different regions and have different annual sales targets as a result. I am building an CRM analytics dashboard for the sales team, which will display a target meter for the logged in salesperson.

        • Transfer all Related Data to new Account Owner

          Currently when I change the account Owner I only see the option to change only the open deals But I want the new account owner to take over all the related modules and all the deal stages Is it not possible right now? Am I missing something? Do I really

        • How to Calculate MTTR (Mean Time to Resolve)

          We want to calculate MTTR (Mean Time to Resolve) in our Zoho Analytics report under Tickets. Currently, we are using the following fields: Ticket ID Ticket Created Time Ticket Closed Time Ticket On Hold Time We are planning to calculate MTTR (in days)

        • How to export project tasks, including the comments

          Hi, how can I export the project tasks, whereby I can also see the comments associated to a specific task? The use-case is that often we use comments to discuss or update a task related ideas. I would like to export the tasks, where we can also see the

        • Does Zoho Sheet Supports https://n8n.io ?

          Does Zoho Sheet Supports https://n8n.io ? If not, can we take this as an idea and deploy in future please? Thanks

        • Bigin Android app update: User management

          Hello everyone! In the most recent Bigin Android app update, we have brought in support for the 'Users and Controls' section. You can now manage the users in your organization within the mobile app. There are three tabs in the 'Users and Controls' section:

        • Share records with your customers and let them track their statuses in real time.

          Greetings, I hope everyone is doing well! We're excited to introduce the external sharing feature for pipeline records. This new enhancement enables you to share pipeline records with your customers via a shareable link and thereby track the status of

        • Live webinar: Discover Zoho Show: A complete walkthrough

          Hello everyone, We’re excited to invite you to our upcoming live webinar, Discover Zoho Show: A Complete Walkthrough. Whether you’re just getting started with Show or eager to explore advanced capabilities, this session will show you useful tips and features

        • Deal Stage component/widget/whatever it is... event

          Deal Stages I am trying to access the event and value of this component. I can do it by changing the Stage field but users can also change a Deal Stage via this component and I need to be able to capture both values. Clicking on 'Verbal' for instance,

        • Next Page