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

• New and improved API resources for Zoho Sign Developers

  Hello, Developers community! We know that for you, an API's real value depends on how quickly, easily, and reliably you can integrate the it with your application. That's why we're excited to talk about the recent improvements to our developer resources,

• stock

  bom/bse : stock details or price =STOCK(C14;"price") not showing issue is #N/A! kindly resolve this problem

• Project Approval Process Workflow

  Issue: When a new Project is created, there is lack of process/workflow that provides their manager a process to approve or review. Suggestion/Idea: Similar to “Workflow”, we need “Workflow” ability at Project level, where based on the criteria of the

• Marketing Tip #5: Improve store speed with optimized images

  Slow-loading websites can turn visitors away. One of the biggest culprits? Large, uncompressed images. By optimizing your images, your store loads faster and creates a smoother shopping experience leading to higher sales. It also indirectly improves SEO.

• Why can I not choose Unearned Revenue as an account on items?

  Hello, I do not understand why we don't have the ability to code an item to unearned revenue. It is not an uncommon situation to have in business. I understand that there is the Retainer invoice as an option, however that doesn't really work for us. Our

• Form Submission Emails

  Is there a current delay with submission emails being sent? We've had 10-20 forms completed today but only a handful of emails.

• Rules not working properly

  I created a rule to display certain fields on certain states. But it seems to be not working. It hides the fields even when I selected California, (which is a state that should show the fields when selected)

• Notebook font size issue

  If I copy something from somewhere and paste it in my notebook, the font size becomes smaller.

• Sign in process is beyond stupid. I'd rather plug my phone into USB and copy files than sign in to this POS.

  792 clicks and fields to fill in just to get into a 3rd rate app is too stupid for me.

• Ampersand in URL parameter // EncodeURL does not work

  Hi Zoho, I have a url link with a parameter. The parameter is including ampersand in some cases (Can be "H&M" or "P&I") When trying to use %26 instead of "&"  (the result I get using EncodeURL()) I get H%26M instead of H&M in the parameter field. How can I solve this? Thanks! Ravid

• how can we create in zoho crm a new contact when the looup does not find a similar existing one

  In forms/integrations/zoho crm / ne w record tab, contact name is to be mapped with my form contact name. When I go in biew edit/lookup configuration, I don t get the options (help dedicated page simply repeat the same info you get in the app) and does

• Directory Websites

  Directories are a good website category to gain search engine traffic. Directories for a professional service category as an example can help their members in search results over their individual website. It would be nice to have a directory template

• Manage Task on Mobile

  How do we manage our task on mobile devices? It seems that there should be a standalone mobile app to handle the new task features. The new features released in regards to Task management are great by the way! Now we need to bring that all together in

• Set Default Payment Method & Default account

  Hi, I would like to know how to set the default payment method and default bank account when recording payments in zoho books. At present we have to change these fields everytime we record a payment, which leads to potential error and as we have a very

• Customer Portal on Zoho Desk

  Hi, I'd like to know more about the items below I found when setting up the Customer Portal on Zoho Desk. Could someone help me explaining these in details? Especially the 2nd and 3rd point. Thanking you in advance! Permissions Customers can sign up for Customer Portal Customers can view tickets of their organization (contacts) Customers must register to access Customer Portal Display Community in Customer Self Service portal

• Computer Showing Offline in Unattended Access

  I have a computer that was connected to the internet but showing offline in Assist. I tried uninstalling the program and deleting it from Zoho Assist the reinstalling and it still does not show up. I have been a user for several months and am not pleased with the lack of connectivity with Assist. If this continues I will have to find another product. The computer I reinstalled it on is not even showing up in Assist now. The name is NYRVLI-PC. Thanks

• Closing Accounting Periods - Invoice/Posting dates

  Hi, I have seen in another thread but I'm unsure on how the 'transaction locking' works with regards to new and old transactions. When producing monthly accounts if I close December 24 accounts on 8th Jan 25 will transaction locking prevent me from posting

• Zoho CRM Portal Error

  Hi, We’re experiencing an issue with the Zoho CRM Portal. When we try to access it, we receive an HTTPS connection error: net::ERR_CERT_COMMON_NAME_INVALID. If we proceed past that, we then get a 400 Bad Request error. Could you please help us resolve

• Can we do Image swatches for color variants?

  We want to do something like the attached screenshot on our new zoho store. We need image swatches instead of normal text selection. We want to user to select an image as color option. Is this doable? I don't see any option on zoho backend. Please h

• Integrating Zoho CRM EmbeddedApp SDK with Next.js — Initialization and Data Fetching Issues

  You can get an idea from my code I have given in end: First, I worked on a React project and tried the same thing — it worked. My goal was to import the Zoho script and then load contacts using the Zoho Widget SDK, which was successful in React. Now,

• Script Editor not an option

  I am trying to apply a script to a sheet and Script Editor is not an option. I don't want to go outside Sheets to do this (like Creator) if it can be done inside Sheets.

• monetizing the courses

  Can I add a price for course enrollment ?

• Can we add zoho books features like invoices estemates etc on our zohocommerce website. When our customer login with their account they can able to see all books features in one place on zohocommerce?

  Can we add zoho books features like invoices estemates etc on our zohocommerce website. When our customer login with their account they can able to see all books features in one place on zohocommerce?

• Copy paste from word document deletes random spaces

  Hello Dear Zoho Team, When copying from a word document into Notebook, often I face a problem of the program deleting random spaces between words, the document become terribly faulty, eventhough it is perfect in its original source document (and without

• 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

• Download a file from within a zoho creator widget

  I have a widget running in Zoho Creator , it displays uploaded documents in a table file, and I have added a download link in the view. ( The widget is created with html, css and javascript). I do not succeed in getting the download working. Do I have

• Taxes for EU B2B Transactions

  Currently, ZC doesn't seem to have a procedure for validating VAT numbers of businesses purchasing in another EU state, and removing local VAT is valid. This is essential for all inter EU B2B trade.

• Unable to Receive Emails on Zoho Mail After Office 365 Coexistence Setup – Error: 553 Relaying Disallowed

  Hello, My domain name is bigniter.com, and I’ve been using Zoho Mail as my email service provider without any issues. Recently, I followed the steps outlined in the Zoho documentation to enable Coexistence with Office 365: 🔗 https://www.zoho.com/mail/help/adminconsole/coexistence-with-office365.html#multi-server

• CRM Related list table in Zoho analytics

  In Zoho Analytics, where can I view the tables created from zoho crm related lists? For example, in my Zoho CRM setup, I have added the Product module as a related list in the Lead module, and also the Lead module as a related list in the Product module.

• Candidate Registration/Invitation

  It would be great to include the 'invite' candidate functionality into some of the automation functions - ether through a custom function trigger or webhook or accessible through an email template.  Currently there is no way to add this functionality into any workflows or blueprint steps which, I find limits the ability to invite candidates to engage with us directly through our candidate portal. 

• [Free Webinar] Learning Table Series - Creator for Infrastructure Management | A Partner-driven collaborative session

  Hello Everyone! We’re excited to invite you to another edition of Learning Table Series, where we showcase how Zoho Creator empowers industries with innovative and automated solutions. About the Learning Table Series The Learning Table Series is a free,

• Where we can change the icon in social preview

  Hi, we changed our logo, and the image that appear in preview (ex : when we post a appointment link somewhere) is still our old logo. I did change our logo in the org setting. https://bookings.zoho.com/app/#/home/dashboard/settings/basic-info?clview=false

• 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

• I have error AS101 when I try to add paypal@mydomain.com to Zoho

  Please help me with this. I tried to call the help line 4 times but don't get any response.

• VAT on Multiple Expenses

  Firstly, might I say I am based in Ireland, and I'm not an accountant, and therefore my understanding of procedures could be somewhat limited. Scenario. An employee pays for items throughout a month, and therefore their claim for expenses includes multiple items - some of the items are VAT deductible (eg, an IT product) and some are not (eg, a client lunch). Discovery The odd thing in ZBooks, is that whilst it seems possible to add a VAT rate to a one-off item of expense, it is not possible to add

• Detect and ignore bots in visitors

  The SalesIQ visitor numbers are basically useless to us because there is no bot detection. We get the same bots coming in from the same countries looking at the same pages every day. It can't be that difficult to tell the difference between an actual

• Agent assignment filter?

  Godo day, We are starting to play with FSM to see if it's going to work for our needs. Now so far we have found that it's very restrcitve in the field department you you have layout rules or can't even hide fields depending on the users roles. We can't

• DKIM Now Mandatory - Changes to Zoho Forms Email Policies

  Hello Zoho Forms Users, This post is to inform you about an important update regarding the authentication of all email domains in your Zoho Forms account. This year, we are doubling down on our commitment to deliver a secure, seamless, and empowering

• In arattai received message can't be deleted

  The issue has been noticed in following: arattai app (Android) arattai app (Window) arattai web While the message posted by me may be deleted, the ones received from others can't be. The item <Delete> change to <Report> when the message is a received

• is it possible to add more than one Whatsapp Phone Number to be integrated to Zoho CRM?

  so I have successfully added one Whatsapp number like this from this User Interface it seems I can't add a new Whatsapp Number. I need to add a new Whatsapp Number so I can control the lead assignment if a chat sent to Whatsapp Phone Number 1 then assign

• Next Page