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:
   
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

• Topic Participants

• Pradeepkumar R

  

• 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

• Lookups and Custom Modules

  I created a Custom Module that I'm calling "Item Purchasing Group". This module will store information for groups of Items that must be purchased together (including minimum overall quantities and/or values, etc.). This will have a one-to-many relationship

• Months of WooCommerce–Zoho Inventory sync failures and repeated support requests – has anyone experienced this?

  Months of WooCommerce–Zoho Inventory sync failures and repeated support requests – has anyone experienced this? I run a small retail business using WooCommerce and Zoho One. For months, I have been trying to resolve recurring synchronisation failures

• Changing an existing item to different accounts & inventory-tracked

  Hi everyone, I have an item in Zoho Books that was originally set up as a non-inventory item. Over time, I associated it with different sales and purchase accounts, and I now have many invoices, bills, and reports that use this item. My business process

• Address limits per customer by edition

  I’m on the Standard edition and have reached a limit of 30 addresses under a single customer. Before I move to an annual Professional subscription, I need to confirm the following: What is the maximum number of addresses allowed under one contact, and

• Zoho Books | Product updates | October 2026

  Hello users, October brings a range of new features and enhancements to Zoho Books, designed to simplify compliance, streamline accounting workflows, and improve inventory management. From the launch of the Nigeria Edition and France’s e-invoicing capabilities

• Create custom rollup summary fields in Zoho CRM

  Hello everyone, In Zoho CRM, rollup summary fields have been essential tools for summarizing data across related records and enabling users to gain quick insights without having to jump across modules. Previously, only predefined summary functions were

• Button or Links order

  Is there a way to re-order the buttons or links that are created? Moderation Update: With our recent enhancement to custom button creation from the layout editor, administrators can now reorder custom buttons directly from the layout editor, and this

• Zoho Books | Product updates | August 2026

  Hello users, July has been an exciting month for Zoho Books! This month, we're excited to introduce HTML PDF Templates, Placeholders as Pills, expanded approval workflows for Sales Returns and Journals, and significant compliance updates across the India,

• Bulk updating tags

  My organisation handles a number of restricted funds. We previously did this by having a chart of accounts where each restricted fund has its own income and expense accounts. However, our accountant changed this, moving all transactions into accounts

• How to Convert Emails to PDF Format in Batch

  It is helpful to convert emails into PDF format when you want to save important messages, when you need to share business correspondence, or when you wish to keep email records for future reference. PDF files can be readily opened, printed, and shared

• Download or Backup Bulk Emails from IMAP Server

  Taking email messages from an IMAP server and saving them is a wise method for ensuring that important emails are kept safe and easy to access. No matter whether you have a business email address or a personal mailbox, keeping a local copy of your emails

• Kaizen #263: Using Zoho CRM Skill to Build Widgets

  Hello everyone! Welcome back to another exciting Kaizen post. In the previous Kaizen posts, we introduced the zoho-crm skill.md and explored how it can help with Zoho CRM APIs, Client Scripts, complex COQL queries, and Skill to build Queries. In this

• Zoho notebook No login

  Hello Team, I recently downloaded and installed the latest version of zoho notebook for windows. But I am not able to login without providing any sso or email address. Did you removed the no login feature for this app? This is just a windows app. can

• Search is completely broken for PC

  I go to note, try to search for "mtga" and it shows 702 results (it's a big note), highlights all letters "m" and I can't find anything. Same for "gathering" - it thinkgs I think only for "g". Looking for "zoho" shows as if I searched only for "z". Using

• Losing description after merging tickets

  Hello, We merge tickets when they are about the same topic from the same client. It happens sometimes. We recently noticed that after the merger only the description from the master ticket is left in a thread. And the slave-ticket description is erased.

• Behind a great customer experience is a team

  Hi everyone, October is here, and the first week marks the Customer service week. Each year, we thank our customer service professionals. Service professionals are all around us, even in our own workplaces: They handle essential administrative work like

• What's New - August and September 2026 | Zoho Backstage

  Hello everyone, August and September bring a fresh wave of intelligence to Zoho Backstage. AI takes the spotlight with tools that help you create content, support attendees, and manage how AI fits into your events. Alongside AI, we’ve refined email communication,

• Images Not Displaying Properly in Zoho Desk

  Hi, We are experiencing an issue with image rendering in Zoho Desk, affecting both incoming and outgoing emails. Images embedded in email conversations are not displaying properly. Instead, we see broken image icons or empty placeholders where the images

• Announcing the new SKILL.md for Zoho CRM and the updated OAS repository!

  We are introducing a new zoho-crm skill to make working with Zoho CRM Developer tools (like APIs, functions, widgets, client scripts, queries etc) easier and faster, with the help of AI in your preferred AI harness like Claude Code, Codex, Cursor, VSCode

• Widget Height were destroyed

  All widget heights in the Details Canvas view are broken. Moderation Update: This is a known issue with widget heights in Client Script popups and flyouts. A fix is in progress, and this post will be updated once it's released.

• Zoho Projects - Has something changed regarding to linking Projects and Deals via the API?

  I am trying to link Zoho Projects to Deals in CRM using the API, but each time, the Deal Contact is added as a Client user and receives an email invite asking them to join the organisation. These are internal projects and the customer should not have

• How long does it take to review campaigns?

  Hello Zoho, i scheduled my campaigns yesterday afternoon and it has been under review for over how many hours and those campaigns were supposed to be out yesterday so new ones can go out today. Is there something i'm doing wrong?

• Convert MSG to PDF in Bulk with Attachment Free

  Microsoft Outlook saves each email as an MSG file, keeping with it key information like the sender, the recipient, the subject, the date, the body of the message, and any attachments. It can, however, be quite difficult to access a large number of MSG

• Using Mail Merge Template to Print Documents with One Subform Record's Fields per Document

  Hello, We have a Mail Merge template created in Zoho Writer which is not able to perform the functionality which is currently required to automate the documentation task portion of our process. The CRM module we are primarily using is based on a "Loans"

• Convert MBOX to PST File Using Free Methods

  MBOX is a mailbox format that is used by various email clients and services, such as Thunderbird, Apple Mail, Eudora, and Google Takeout. Yet, in order to be able to open your MBOX emails in Microsoft Outlook, you have to convert them from MBOX to PST

• Zoho Community Digest - September 2026 | Part 4

  Hi everyone, and welcome back! The final week of September brought a revamped Books integration in Zoho CRM and Zoho Key Service for LLM access without API keys, Zoho Creator's audit trail becoming a full enterprise-grade auditing system, and Zoho One

• Automate weekly reports from Channels | Zoho Cliq AI use case #01

  Scrolling endlessly through numerous threads in a Cliq channel and manually counting how many have been closed, how long people took to reply, and who actually participated can be tedious. After 20 minutes, you might have a number that no one can verify.

• Apple Mail to Outlook Converter - Export Apple Mac Mail to PST File

  Apple Mail is a well-known email application among people who use Macs, whereas Microsoft Outlook is widely employed for both personal and business use. Should you decide to move from Apple Mail to Outlook, you'll probably need to transfer your mailbox

• Creator -Dynamic addition of Customer Portal User Licenses

  Hi, We have a Creator App for B2C about to get released. We are experiencing the Portal User license challenge. Unable to predict the number of users who will download and Subscribe. When a User is about Subscribe and the portal licenses does not exists,

• Zoho Tables is now part of Zoho One!

  Hey everyone! We've got some exciting news to share. Zoho Tables is now included in your Zoho One subscription at no extra cost. If you're already on Zoho One, head to your app list, add Zoho Tables, and get started right away. You don't need a separate

• How to import PST files in Office 365?

  You can import PST files into Office 365 using native Outlook applications, the Microsoft Purview network upload method, or specialized third-party migration tools. Choosing the right method depends on whether you are migrating a single mailbox or executing

• [Webinar] Zoho CommunitySpaces MCP

  New to MCP, or just exploring what's possible? Join us for a webinar on Zoho MCP and your community. We'll show you how Zoho CommunitySpaces and Zoho MCP work together, what your AI co-host can do inside your community, and how to get started. Register

• Solution to Import OST File into Office 365.

  In today’s digital workplace, organizations are rapidly moving toward cloud-based email systems for better flexibility, security, and collaboration. Microsoft 365 has become one of the most widely used platforms for business communication. A common requirement

• How can I show an uploaded image larger/full size in a Zoho Forms response PDF?

  Hi,  I noticed that in a response PDF for Forms, the uploaded image is shown in a tiny box as seen in my screenshot. Is there any way I can make that appear larger in the response PDF? thanks.

• 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

• Meet PageHealth by Zoho PageSense: a free way to find out what's holding your page back

  We all know the feeling. You've spent days tweaking headlines, moving buttons, and rewriting copy, and you're still not sure why visitors aren't sticking around. Revamping your website is hard, and figuring out what's actually getting in the way is even

• Export Exchange Online Archive Mailboxes to PST for Microsft 365 Users

  With Microsoft 365 it is possible to archive older emails in Exchange Online in order to keep the main mailbox tidy. If however you need to export the archived emails to a PST file for the purposes of backup, migration, compliance or for use offline,

• Task Module quick create settings

  Hello, requesting the Task module receive a quick create customization settings. We have added custom fields and have to select "More fields" at the bottom of the pop-up to access the custom fields.

• Your employees changed departments. Did your IT systems notice?

  An employee joins your Marketing team. HR adds their details to the organization's user directory. The IT admin, in turn, creates their Zoho One account, assigns the required applications, adds them to groups and provides access based on the organization's

• AI Calling

  Hi, does anyone have experience or any thoughts on AI Calling. Getting AI to make calls on behalf of your company. We are looking into it, but are finding it difficult to get info and providers that can assist.

• Next Page