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:
- Healthy Zone - Low returns
- Warning Zone - Rising returns
- 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
- Open your Pivot Table.
- Creating Return Rate Formula:
- Click Add Aggregate Formula.
- Enter Formula name as Return Rate.
- Define the metric as below:
- Click Save.
- Set up your pivot table as shown below.
- Columns: Month
- Rows: Product Category
- Data: Sales (USD), Return Rate (%)
- Hide the Return Rate % column from the pivot as shown below.
- Click Visuals and select Only Data Bars.
- Right-click on any Sales cell and select Conditional Formatting.
- In the Conditional Formatting dialog, under Based On, choose Return Rate (%).
- Define three conditions based on the following zones:
- Critical Risk - Set the condition as Greater than or Equal to 10 and choose Red fill in Additional Formatting options.
- Warning Zone - Set the condition as Between 5 to 10 and choose Amber fill.
- Healthy Zone - Set the condition as Less Than or Equal To 5 to 10 and choose Amber fill.
- Click OK to save the conditions.
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.
What you should see
- Green Sales Bars where return rates are low → sustainable business.
- Amber Bars where returns are rising → early warning.
- 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
- 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.
- 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.
- Keep colors intuitive: Stick to natural associations: Green = Healthy, Red = Risk, Orange = Caution. This makes insights instantly recognizable for everyone.
- 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.
- 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
- Avoid overlapping rules: Overlaps can confuse users. Keep each condition distinct to avoid conflicting colors on the same cell.
- 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.
- 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
- Help Documentation
- Help Videos
Access your files securely from anywhere
Centralize Knowledge. Transform Learning.
All-in-one knowledge management and training platform for your employees and customers.
New to Zoho Recruit?
Zoho Developer Community
New to Zoho LandingPage?
Zoho LandingPage Resources
New to Zoho Survey?
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Bigin?
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
New to Zoho TeamInbox?
Zoho TeamInbox Resources
Zoho CRM Plus Resources
Zoho Books Resources
Zoho Subscriptions Resources
Zoho Projects Resources
Zoho Sprints Resources
Qntrl Resources
Zoho Creator Resources
Zoho CRM Resources
Design. Discuss. Deliver.
Zoho Show Resources
Get Started. Write Away!
Writer is a powerful online word processor, designed for collaborative work.
Zoho CRM コンテンツ
-
オンラインヘルプ
-
Webセミナー
-
機能活用動画
-
よくある質問
-
Ebook
-
-
Zoho Campaigns
- Zoho サービスのWebセミナー
その他のサービス コンテンツ
Nederlandse Hulpbronnen
ご検討中の方
Recent Topics
Zoho Books - Payment Gateway - Revolut
Hi Books Team, My feature request if to include the popular platform Revolut as a payment collection option on invoices in Zoho Books. Please upvote if you are also looking for this option.Zoho Books | Product updates | January 2026
Hello users, We’ve rolled out new features and enhancements in Zoho Books. From e-filing Form 1099 directly with the IRS to corporation tax support, explore the updates designed to enhance your bookkeeping experience. E-File Form 1099 Directly With theKaizen #233 - Generating AI-powered Follow-up Emails Using CRM Functions and Widgets
Hey everyone! Welcome back to another interesting post in the Kaizen series! Sales teams regularly capture interaction notes in CRM after speaking with prospects. However, drafting a follow-up email that reflects the conversation context can be repetitiveConnect Bank in Zoho Books
Can I connect UOB or Ariwallex in Zoho Books?Using MPN across multiple SKUs and inventory tracking
I have several different SKU's that share a common MPN and would like to track inventory by MPN. SKU1 has MPN1 assigned SKU2 has MPN1 assigned Here is an example If I start with 5 of MPN 1 in stock I want each SKU1 and SKU2 to show as 5 in stock, If IExtend Zoho Canvas Customization to Zoho Creator Forms and Reports
Currently, Zoho Canvas allows users to design and customize the UI of Zoho CRM modules with a much better visual experience. This helps organizations create cleaner layouts, improve usability, and design interfaces that match their workflows. However,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 instantlyFeature Enhancement Request – Text Formatting Options in Item Description (Zoho Books/Quotes Module)
Dear Zoho Development Team, Greetings from Radiant360 Integrated Technical Services LLC. We would like to bring to your attention a functional limitation we've encountered within the Item Table / Quote Description section of Zoho Books (and Zoho CRM Quotes).ZOHO Books Query
Good day, Can someone please advise. I recently migrated from ZOHO Invoice to ZOHO Books. No that I want to use the inventory on Books I cant as all my items have transaction history. The person I spoke to at ZOHO said I need to create a new Company profileBest 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 haveZOHO.CRM.UI.Record.open not working properly
I have a Zoho CRM Widget and in it I have a block where it will open the blocks Meeting like below block.addEventListener("click", () => { ZOHO.CRM.UI.Record.open({ Entity: "Events", RecordID: meeting.id }).catch(err => { console.error("Open record failed:",inventory removal at packing list or shipment.
currently our system is set to remove inventory at invoice. This is creating an inventory nightmare? Is it possible to change the settings to remove the item from inventory at either the packing slip stage or shipping the item.How to add employee and not invite them to log in?
I want to add 50 employees, but invite them only when everything will be configured and ready. Is it possible? Should we create employee profiles and then convert them later? Thank you,How is Your eCommerce Experience w/Zoho Inventory?
First off, I'm SUPER grateful for the advent of Zoho Inventory and now the Zoho Commerce Suite. Overall, Inventory is a great product, especially for customers without an eCommerce presence. For eCommerce companies (especially those shipping more than ~10 packages/day), however, there are certain drawbacks that keep my clients from moving over to Zoho Inventory: Cons: 1. Invoice + Package Creation from Shopify/Other eCommerce Integrations: Zoho Inventory makes the somewhat perplexing decision toAbility to Use Both AND and OR When Creating Rules (Advanced Conditions)
I'd like to be able to use more complicated logic when setting up rules. E.g. in Zoho Mail, I can choose "Advanced conditions (AND/OR) to create a rule that can be applied to multiple subject lines from the same sender. But in Zoho TeamInbox, I will haveZoho Desktop App- Unable to Minimize/Freezes
I'm having issues with my Zoho Mail desktop app (PC). When go on my desktop and open the app this is what happens: - Unable to minimize and close app (in the screenshot attached you can see at the top right there is no option to minimize/close) - UnableZoho Invoice Zapier Integration
Is there still a way to use Zapier with Zoho Invoice? I've read online that that migrated to Zoho Books or Billing but since I am just using Invoice I can not find a Zapier Connection anymore.Conect chat of salesiq with zoho cliq
Is there any way to answer from zoho cliq the chat of salesiq initiated by customers?Bug: OAuth 2.0 State Parameter fails with Pipe Delimiters (RFC 6749 Non-Compliance)
I've discovered a bug in how Zoho's API Console handles the OAuth 2.0 authorization flow when the state parameter contains pipe characters (|), and I'm hoping the Zoho team can address this in a future update. The Issue Zoho's OAuth 2.0 implementationZobot with Plugs
Hello, I am having a problem with Zobot using Plugs. Here is my current flow: When I run the flow, I should immediately see the messages from the initial cards (Send Message cards), then after running the plug, and finally, see the messages after thePrint a document from Zoho Writer via Zoho Creator
If i use the code below i can get writer to create a new document or email it to me but i want to be able to print it directly from the browser and not have to send it via email and then print. Below is the code im using. Attached options form zoho writerIs it possible to use HTML formatting in SMS messages sent from Zoho?
Hi everyone, I have a question regarding sending SMS from Zoho When we send emails from Zoho, we can include HTML code to format the message (like adding links, styling, etc.). Is it possible to do something similar when sending SMS messages through ZohoTraining for How can i Operate the Software Features
need to Help for Training of the ZOHO Billing SoftwareAutomating Vendor Contract Management between Zoho Contracts and Zoho Books using Zoho Flow
Managing vendor agreements across procurement and finance systems often requires manually downloading executed contracts and attaching them to vendor records. This can become time-consuming and difficult to manage when dealing with a large number of contracts.Problem applying batch operation error keeps looping that error
Problem applying batch operation error keeps looping that error an wont loginShared Email Sending Duplicate Copies of Emails
Hi there, We recently have moved our info@ email to Team Inbox to trial. We set this channel up using the Microsoft setup which works fine and have 3 users. I am wanting to know: 1. When I send a new email, it will create a copy of that email and sendmerhaba
merhaba sosyal medya paketimiz mevcut ama yorumları göremiyoruz ve o yüzden cevap veremiyoruz destek rica ediyoruz.Teaminbox not working
We couldn't send or receive any mail within the team inbox. Displaying error 'Unable to process this request.'Related lists New option in missing
hi I have created quite a few modules and added as related lists to my main module. Some have new, some dont I can not see why?Cliq and ToDo integrations?
I'm a bit surprised not to find any way to open a Cliq chat for the current thread, or to create a Zoho Mail ToDo from a thread. Are these on the roadmap?Reply-to names are mangled
Hello, I'm seeing an odd behavior in replies. Steps to reproduce: 1. Click reply to an email from "John Doe <doe.john@example.com> in TeamInbox Expected outcome: TO field pre-filled with "John Doe <doe.john@example.com>" Actual outcome: TO field pre-filledI CANT UPGRADE MY FREE ACCOUNT
I TRY TO UPGRADE MY FREE ACCOUNT AND I COULD NOT UPGRADE IT CAN SOMEBODY TELL ME WHY? AND I HAVE THE MONEY SO.Level up your ASO game with tags & categories in store reviews
Introducing tags and categories in Apptics' store reviews Dear Apptics community, If your app is listed on the Play Store or App Store, you already know how important store reviews and ratings are. They’re one of the most direct signals of user sentimentIncluding attachments with estimates
How can attachments be included when an estimate is sent/emailed and when downloaded as a .pdf? Generally speaking, attachments should be included as part of an estimate package. Ultimately, this is also true for work orders and invoices.Adding VENDOR SKU to PURCHASE ORDERS
how can we add the Vendor SKU when issuing a Purchase Order , so the PO shows the Supplier SKU and our own Internal SKU , which is what we want to receive into the system .Possible to freely prompt/query CRM data using Zia?
Is it possible to prompt Zia to query on any information stored in the CRM, especially on the data stored in custom text fields? My use case is the people in my organisation have entered lots of text in custom text fields to capture information from anRestrict employees to take only one day holiday from a multi-day festival holiday
Hi everyone, I have a requirement related to Optional/Festival Holidays in Zoho People. For example, in the month of May there are three optional holiday dates: May 11, May 12, and May 13. Employees can choose one of these days as their optional holiday.Cannot modify colours in invoice email template
I have tried switching browsers... but I cannot change the (pretty horrible) default colours in the preset email when sending an invoice... the blue banner, red outstanding total and the bright green button... I can change other things but not the colours?Cannot find zpuid for Zoho Projects user
I'm using the Zoho Projects v3 API to create a task. The task is created successfully, but in order to assign the task owner, the "Create a Task" API also requires the zpuid of the task owner. Unfortunately I cannot find any user-related API calls thatPrint a document from Zoho Writer via Zoho Creator
If i use the code below i can get writer to create a new document or email it to me but i want to be able to print it directly from the browser and not have to send it via email and then print. Below is the code im using. Attached options form zoho writerNext Page