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

• Automate the file import step

  Hello everyone, I have a Sales - 'Account' category, and currently import the file to update it as follows: Import Accounts - From File - Update existing Accounts only - select and match the field the CRM. Since we have been using Microsoft 365 SharePoint.

• Data Import

  Hello Latha, Is there any option to enable data import option in Equipment module? Best regards, Chethiya.

• Unable to Access /crm/v7/taxes – OAuth Scope Mismatch in Zoho CRM API

  I am currently integrating Zoho CRM (v7 API) with our system and I need clarification regarding the Taxes API and OAuth scopes. Context: We are creating Quotes via the API (/crm/v7/Quotes) Each quote contains line items with taxes (e.g., TVA 19%, 10%,

• Email from CRM being Blocked or Marked as Spam by Google (and maybe more)

  In the past 24 hours we’ve noticed that emails sent via Zoho CRM are being blocked or flagged as phishing, particularly by Google. The issue seems to occur specifically when emails contain links. URLs like www.domain.com or www.example.com are automatically

• Approval Workflow Not Triggering When Status Updated via Custom Button

  Hi Team, I’m facing an issue with an approval workflow in my application. I have a workflow that updates a record’s Status field from “Pending” to “Waiting for Approval.” I have configured an Approval Workflow with the condition: Status = "Waiting for

• Marketing Tip #29: Make it easier to shop by grouping products into collections

  A well-organized store helps customers find what they want faster, which directly improves conversions. When products are grouped into clear categories or collections, shoppers don’t have to scroll endlessly or search manually. It also makes your store

• 【無料/オンライン】4/30開催｜Zoho ワークアウト（オンライン勉強会）

  ユーザーの皆さま、こんにちは。 コミュニティグループの中野です。 4月開催の「Zoho ワークアウト」のご案内です。 本イベントは、Zohoユーザー同士で交流しながら 設定・運用・活用に関する課題解決を目指すオンライン勉強会です。 「設定を進めたいけれど、一人だと手が止まってしまう」 「他社がどう活用しているのか知りたい」 「相談できる相手がおらず、運用に悩んでいる」 そんな方におすすめのイベントです。 ▶︎参加登録はこちら（無料） https://us02web.zoom.us/meeting/register/qIH1ut8aTnq-R7-21eF3BQ

• WhatsApp Calling Integration via Zoho Desk

  Dear Zoho Desk Team, I would like to request a feature that allows users to call WhatsApp numbers directly via Zoho Desk. This integration would enable sending and receiving calls to and from WhatsApp numbers over the internet, without the need for traditional

• Zoho Desk - Support Plan by Time Log Hours

  Hi Desk Team, When creating a Support Plan, it would be great to have an option under Plan Type for Time Based Plan. This would be based on the number of work logs created for a customer account. It's very common to offer a support contract which has

• Help Center and SEO: Any Benefit to My Domain-Mapped Website Ranking?

  First of, I love the Help Center which I've just decided to integrate into my website to replace its old-fashioned FAQs. So much more to achieve there now! Lots of new benefits to the site visitors and to me in terms of organizing and delivering all the

• How to set custom Sales Order numbers

  I am trying to create Sales Orders with data from Jotform submissions. Auto number generation is disabled within Books. Whereas the flow Input recognizes the number (40732 in this example), the Output does not. How can I fix this? I'd like the number

• What's New in Zoho Inventory | January - March 2026

  Hello users, The first quarter of 2026 has been dynamic! We’ve shipped a powerful set of updates in Zoho Inventory to enhance item control, improve warehouse efficiency, expand integration and reporting capabilities. From a unified item creation experience

• Zoho Commerce - Mobile App Order Creation

  Hi Commerce team, I would like to suggest a feature for the mobile app that would make it much easier to create and process orders on the go. Problem Currently, while it is possible to create a Sales Order in Zoho Inventory, the process is not optimised

• Inventory "Bulk Actions" button - add more fields to "Bulk Update > Select a field"

  Can we not get a lot more actions that are commonly used by customers into the "More Actions" button on the Inventory list? More fields listed in the Bulk Update > Select A Field? Possible Bulk update Fields Preferred Supplier ( to quickly move items

• Zoho Mail Android app update: UI revamp

  Hello everyone! We are excited to share that the first phase of the Zoho Mail Android UI revamp is now live. In this update, we have redesigned navigation bar at the bottom to quickly access the Email, Calendar, Contacts, and Settings modules. Also, the

• Users I've shared the sheet with cannot use the Custom Functions

  Hi, I have a Zoho Sheet worksheet that I shared to 2 colleagues, giving them full access: In that worksheet, I created a button with a custom Deluge function and it works flawlessly for me: For those I shared the worksheet to, when they click the button,

• Zoho books - Project Module - Itemised expenses

  Hi All, I heavily use the projects function in Zoho books and can work for one client for successive weeks, providing labour and incurring the occasional general expenses.  As an example, during the one purchase, I purchase 10 widgets and of these 10, 

• Urgent Security Feature Request – Add MFA to Zoho Projects Client Portal Hello Zoho Projects Team,

  Hello Zoho Projects Team, We hope you are doing well. We would like to submit an urgent security enhancement request regarding the Zoho Projects Client Portal. At this time, as far as we are aware, there is no Multi-Factor Authentication (MFA) available

• Full Module-Level Access Control for Custom Profiles in Zoho Projects

  Hello Zoho Projects Team, We hope you are doing well. We would like to submit a feature request regarding access control limitations in custom user profiles within Zoho Projects. Current Behavior: We created a custom profile intended for support agents,

• How to save custom report for future use ?

  Dear, How to save custom report for future use ? Thanks & Regards Shamnad 94460055258

• Need to be able to save Customized Reports

  There are several standard reports in zoho books.  Each of these can be 'customized' with various parameters - BUT, these cannot be saved for re-use later. In several of the zoho modules you can customize a search list (eg for Estimates or Invoices etc)

• Split Bills/Expsense between multiple projects and/or clients

  I need to be able to split vendor invoices/ expenses between multiple clients. Entering the bill multiple times is not only time consuming, it defeats the purpose of having a unquie identifity bill number and will allow for possible duplicated entry.  Below is an example from Quickbooks Desktop. Splitting costs over various projects is a common job costing function that I am very sad and surprised is not an option in Zoho Books. Unless I am missing it somewhere? Thanks for your help!

• Vendor bills cannot be assigned to a customer or a customer project?

  I'm confused on how to handle outsourced contractor expenses on a customer project.  Between my business and the contractor, the invoice they send me fits obviously into Zoho Books as a Bill. However, I need to be able record those expenses to the client

• A few suggestions for Zoho Books

  Hello. I've been evaluating Zoho Books for a small law firm practice. The interface is solid and I like the integration of accounting and time tracking, which is hard to find among cloud based services. I have a few suggestions that would be very helpful in determining whether we can adopt Zoho Books: 1. Be able to assign bills to clients/projects. Quickbooks desktop allows this and is very helpful in keeping track of bills that haven't been paid, but will be invoiced to clients as an expense. Creating

• Book project costs to tasks

  Hi all, New to zoho but far from new to this sort of platform. I've been scouring the web for a suitable platform for my growing business. I'm currently using Xero and WorkFlow Max but it lacks a key need. I signed up to zoho projects and books to test

• Project expenses in Zoho Books

  Just the way timesheet is used to calculate labour cost for a particular project, how do i record other expenses against a project such as materials and consumables used for the project? So that under report, I can view the total amount expended on each

• Supplier Purchase Orders applied to a Project

  We are finding it difficult to decide the best way track project expenses in Zoho books and would like to know how other users are handling this task. We provide Professional Services and engage 3rd party Suppliers on most projects and provide them with

• Anyone using Books to track Project Profitability? If so, I could use some guidance

  Hello Zoho Community. I am a recent subscriber to Zoho, and its part of an ongoing evaluation.  My company (and my clients) have extensive project-tracking needs.  The Projects module seems to be good from a project management standpoint, but I am perplexed

• Deluge Error: "Data type of argument of function 'get' did not match required type [BIGINT]" when working with Lookup fields

  Hi everyone, I’m facing an issue in Zoho CRM Deluge while working with lookup fields. Setup: Module: Monthly Bills Field: Customer (Lookup → Accounts) Module: Laundry Orders Field: Laundry_Account_Name (Lookup → Accounts) Field: Expected_Revenue (Currency)

• date & datetime client script getInput types

  Please add date & datetime as available types for the getInput client script function. https://www.zohocrm.dev/explore/client-script/clientapi/Client#getInput

• Opt-out from mailing list means can't email at all??

  It seems that if a contact unsubscribes from a mailing list the only way to email them is to uncheck the email opt-out box first, then re-check after the email has been sent. I've been through a chat with support and they confirmed this. This seems bizarre. Many of my clients don't want to receive a monthly newsletter but they definitely want to be in email communication with me, often on a daily basis. Any thoughts out there?

• Email Opt Out Question

  Has the problem where if a customer is emailed opt out prevents you sending standard emails? For me this feature is simply to stop any email marketing and should not block people from receiving emails via Zoho mobile, which makes no sense.

• Multi-currency and Products

  One of the main reasons I have gone down the Zoho route is because I need multi-currency support. However, I find that products can only be priced in the home currency, We sell to the US and UK. However, we maintain different price lists for each. There

• Zoho TeamInbox stuck / unusable – possible orphaned organization?

  Hi all, I’m running into an issue with Zoho TeamInbox under Zoho One Trial and wondering if anyone has seen this before. We’re trying to set up a shared inbox for orders (orders@), but TeamInbox appears to be in a broken state: • The app does not load

• Audio transcript not working in Zoho notebook

  Greetings all! I'm enjoying the Zoho notebook as I tested out over other notebook applications. I would really love the audio transcripts to work as that would save us a lot of time so that we can utilize the notes from an audio recording. Currently when

• Re: Application Architecture in Zoho Creator — A Platform-Specific Deep Dive

  A recent community post on application architecture made some excellent points about planning architecture early in Zoho Creator projects. The core message is right — Creator applications have a habit of growing organically into maintenance nightmares,

• Schedule Timeout 5 minutes vs. stated 15 minutes

  I am running into a function run timeout error after 5 minutes for my schedules. The Functions - Limits documents states it should be 15 minutes: Functions - Limits | Online Help - Zoho CRM. What should it actually be? Due to the 5 minute timeout, I'm

• Cliq iOS can't see shared screen

  Hello, I had this morning a video call with a colleague. She is using Cliq Desktop MacOS and wanted to share her screen with me. I'm on iPad. I noticed, while she shared her screen, I could only see her video, but not the shared screen... Does Cliq iOS is able to display shared screen, or is it somewhere else to be found ? Regards

• Placeholders in Ticket Templates

  We should be able to use placeholders in ticket templates. When we create a new ticket, our description field is shown to the client in the email they receive.  It would be very handy to be able to personalize that description field in our ticket templates to pull in the name of the client that the ticket is for. Using them in the subject field as well, so we can auto populate Account Names, etc. 

• Favorite Views list sort order?

  Is there a way to rearrange the Favorite Views list?

• Next Page