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

• Support Nested JavaScript Variables in PageSense Pop-up Targeting

  Hello Zoho PageSense Team, I hope you're doing well. I’d like to request a feature enhancement for the PageSense pop-up targeting functionality. Current Limitation: Currently, PageSense pop-ups can be triggered using simple JavaScript variables. However,

• Item images

  Can we get an "On hover" expanded image for items please ?

• Cannot Invert Axis for Rankings

  Hi there I want to be able to create a ranking graph in Analytics/BI, with 1 at the top of the Y axis, but I am unable to invert the axis. Super simple example in Excel below. Higher rankings need to be higher up on the graph to give the correct visual

• How to copy value from a single line field into a picklist field within a module's subform?

  Hello there, I have a single line field in a module's subform. I would like the value in the field to automatically update a picklist field within the same subform (both have items with the same names). Is this possible via function? Unfortunately, workflows

• Committed Stock and To Be Received Stock via API?

  Is it possible to retrieve Committed Stock and/or To Be Received Stock for an Item via the API? I want to use this information for calculating the amount of inventory needed to be purchased.

• Creating Email template that attaches file uploaded in specific field.

  If there's a way to do this using Zoho CRM's built-in features, then this has eluded me! I'm looking to create a workflow that automatically sends an email upon execution, and that email includes an attachment uploaded in a specific field. Email templates

• Marketer’s Space - Automate a Personalized Holiday Workflow with Zoho Campaigns

  Hello marketers, Welcome back to another post in Marketer’s Space! From Thanksgiving through Christmas, you have a flurry of opportunities to connect with your audience. In this post, we’ll see how you can plan an entire month-long automated workflow

• Items Below Reorder Point Report?

  Is there a way to run a report of Items that are below the Reorder Point? I don't see this as a specific report, nor can I figure out how to customize any of the other stock reports to give me this information. Please tell me I'm missing something s

• Zoho Inventory Custom Field Update

  Hello All, In this post I am describing how can we Update the Custom Field Value in Zoho Inventory. // Get Org ID orgid = organization.get("organization_id"); // Field Value resvp = ifnull(item.get("purchase_rate"),null); // Record ID iid = item.get("item_id");

• Sorting columns in Zoho Projects

  Hi, In project management best practice, sorting columns (ascending, descending) is an important tool. Sorting dates to see the order of tasks starting, sorting on priority or even on planned hours is a must for an efficient project control. Currently,

• Zoho CRM - Calendar Cards View - Let Users Decide What Is Displayed On Calendar Entries

  Imagine planning your week of face-to-face meetings across three counties. You’re trying to group appointments by location to make the best use of your time, but Zoho CRM’s calendar doesn’t show where each meeting is happening. You’re left trying to remember

• Explore the Redesigned Quotes Module in Zoho Billing

  Dear users, We’re excited to introduce a refreshed look for the Quotes details page in Zoho Billing! This update brings you a more efficient user interface experience without changing your existing workflow. We've enhanced layouts with organized tabs,

• Updating custom fields in Zoho Projects

  Hi I am wondering if anyone has experience with custom fields in Zoho Projects. I am struggling to update the field using either deluge or the api endpoint. My code is: //custom_Map = map(); custom_Map = {"UDF_DOUBLE_1":"0.27"}; update_Map = map(); update_Map.put("custom_fields",custom_Map.toList());

• Zoho Inventory - How to pay a supplier up front then receive multiple deliveries

  How do we manage situations where we pay a supplier up front, then the receive the products in increments? Example Workflow: Create Purchase Order > Receive Bill for full amount > Receive Items 2 or more deliveries. Currently, once a Bill is created against

• Pin multiple columns and adjust column widths in CRM subforms

  Hello all, Subforms act as secondary forms or tables in which you can associate multiple line items to a primary record and thereby ensure more structured and comprehensive data organization. We've made some recent enhancements to subforms. Here's what's

• Zoho Books | Product updates | November 2025

  Hello users, We’ve rolled out new features and enhancements in Zoho Books. From translating email notification templates to the new transaction locking restrictions, explore the updates designed to enhance your bookkeeping experience. Making Tax Digital

• How can I add or change the active customer in Zoho Projects?

  I'm trying to change the customer in an active Zoho project. Is it possible to add or change the customer?

• Good news! Calendar in Zoho CRM gets a face lift

  Dear Customers, We are delighted to unveil the revamped calendar UI in Zoho CRM. With a complete visual overhaul aligned with CRM for Everyone, the calendar now offers a more intuitive and flexible scheduling experience. What’s new? Distinguish activities

• Almacenamiento

  Hola, Quisiera saber como podría hacer para bajar el almacenamiento de 5gb a mis usuarios, en otras palabras los quiero ir limitando de la cuota real, y luego ir agregando poco a poco la cantidad hasta llegar a los 5gb que me dan en el plan free. 

• What happens when someone clicks 'unsubscribe' in a zoho CRM email

  Hello, As per, i am going round in circles trying to find an answer to these 2 question. I have an email template that has an unsubscribe link in the footer in zoho CRM. First, what happens when someone clicks it - where does the contact get unsubscribed

• Nimble enhancements to WhatsApp for Business integration in Zoho CRM: Enjoy context and clarity in business messaging

  Dear Customers, We hope you're well! WhatsApp for business is a renowned business messaging platform that takes your business closer to your customers; it gives your business the power of personalized outreach. Using the WhatsApp for Business integration

• Zoho CRM - Scheduled Reports Which Contain Chart

  Hi Zoho CRM Team, I'm requesting that the Report Export and Scheduling feature be enhanced to include a chart, if one has been created on a report. At the moment I have a report which shows Sales This Week by Deal Owner and a pie chart at the top of the

• Ability to Create and Manage YouTube Video Chapters in Zoho Social

  Hi Zoho Social Team, How are you? We would like to request an enhancement to Zoho Social that would allow users to create and manage YouTube video chapters directly within the platform. Currently, Zoho Social does not support manually adding chapters

• Can I add Conditional merge tags on my Templates?

  Hi I was wondering if I can use Conditional Mail Merge tags inside my Email templates/Quotes etc within the CRM? In spanish and in our business we use gender and academic degree salutations , ie: Dr., Dra., Sr., Srta., so the beginning of an email / letter

• Cadences

  I have just started using Cadences for follow-up up email pipeline. Is it just me or do you find the functionality very basic? For example, it will tell me (if I go looking for it) if someone has replied to a follow-up and been unenrolled; but it won't

• Canvas View in Zoho Recruit

  Is it possible or would it be possible to have the new 'Canvas View' in Zoho Recruit?

• Zoho Inventory - Move Orders

  Quick question about Move Orders... Why is there no status to say something like "Draft", "In Progress" and "Completed", similar to Transfer Orders? I'm assuming that when something needs to be moved it should be planned in Inventory, executed and then

• Kaizen #215 - Workflow APIs - Part 3 - Create and Update Workflow Rules

  Welcome back to another week of Kaizen! Over the last couple of weeks, we’ve joined Zylker Cloud Services as they review and improve their workflows. In Part 1, we discovered and audited their sprawling workflow landscape. In Part 2, we learned how to

• DataPrep Bigquery Connection failed

  Hello everybody, I want to create a connnection beetwen Bigquery and Dataprep but when I try to connect my project I got this error Loading tables has failed. Table list fetched from the data source expired.

• Issue in Zoho People Regularization – Incorrect Hour Calculation

  I have noticed that when applying attendance regularization in Zoho People for previous dates, the total working hours are not calculated correctly. For example, even if the check-in is 10:00 AM and check-out is 6:00 PM, the system shows an incorrect

• Zoho Analytics Export API

  Hi Team, I’m working on some integration tasks and wanted to confirm if it’s possible to retrieve a Zoho Analytics table as JSON data using a Deluge script. I’ve already stored my custom data from multiple sources and combined it into a single source.

• Free Webinar : Unlock AI driven business insights with Zoho Inventory + Zoho Analytics

  Are you tired of switching between apps and exporting data to build customized reports? Say hello to smarter & streamlined insights! Join us for this exclusive webinar where we explore the power of the Zoho Inventory–Zoho Analytics integration. Learn

• Allow Multiple usage units to items while adding them to sales/purchase transactions

  The usage unit of items added in zoho books are static right now and can not be changed. But certain items are received or sold in multiple usage units. One example is fabric. It can be bought in Meters, inches, kgs or other units. Another example would

• PO receive quantities

  At last, Zoho has finally got around to allowing us to receive a larger qty than recorded in the PO :-) Saves us all from editing the PO's before receiving larger quantities ( usual for us ) ! It's still in "beta" but available upon request, I've tested

• Invalid URL error when embedded sending url into iframe for my website when using in another region

  Hi team, My site is currently working on integrating your signature feature as part of the system functionality, it's working great but recently there's been a problem like this: After successfully creating the document, i will embed a sending url into

• Analytics : How to share to an external client ?

  We have a use case where a client wants a portal so that several of his users can view dashboards that we have created for them in Zoho Analytics. They are not part of our company or Zoho One account. The clients want the ability to have user specific,

• Reference Deal Categories in Deluge

  Hello, Is there a way to reference Deal Category in deluge functions? So for our Deals, we have several different WON stages in a pipeline.  Rather than type each stage into our functions, we'd like to be able to reference the Deal Category.  Similar

• Payroll In Canada

  Hi, When can we expect to have payroll in Canada with books 

• Automatic Portal invite

  We have numerous customers we move through a blueprint in deals, when they get to a certain point we need to give them portal access, how can this be done through deluge or a workflow?

• Users Name & Email in Reports

  Hi, I would like to show the Users Name from their Zoho Acount in All Entries/Reports as well as the current Account Email. Thanks Dan

• Next Page