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

• Zoho desk desktop application

  does zoho desk has a destop applicaion?

• Ability to re-order Workflow Rules

  We really need the ability to re-order workflow rules within departments.  Either the ability to drag/drop them into a different order or something.

• Create an article template

  I have a question concern the Knolwedge Database from Zoho Desk. There is any possibility to create an article template ? Exemple of what I research : TODAY : I want to create a lot af articles with the same baseline > I create one article > I copy/paste

• Persisting email signals to contact timeline?

  Hello there. Regarding the email signals that are logged when a contact opens an email, am I correct that these signals are not logged to a contact's timeline? Ideally, I would like to have this context available for future follow-ups, if the contact

• Change of Blog Author

  Hi, I am creating the blog post on behalf of my colleague. When I publish the post, it is showing my name as author of the post which is not intended and needs to be changed to my colleague's name. How can I change the name of the author in the blogs?? Thanks, Ramanan

• Zoho FSM API Delete Record

  Hi FSM Team, It would be great if you could delete a record via API. Thank you,

• Instant Messaging Update | Now migrate your WhatsApp Business phonenumber from another BSP to Zoho Desk | Dec'23

  Hi All, Everything seems to move at lightning speed these days, and time is certainly flying by for the IM team. But we are delighted at how much ground we covered this year. 🚀 For one, we enabled WhatsApp business phone number migration so that you

• Upload ticket attachments via Drag-&-Drop

  Hello, if you want to upload a file to the ticket attachment you need to click the button and use the file browser to select and upload the desired file. In many cases, it would be much more efficient if you could simply drag the file to the browser window...

• Can we disable add to cart in Zoho Commerce?

  Hello, Our sales happen on the phone. During the call the customer is directed to our website to inspect the products together with the sales rep. So we need a way to present our inventory. I figured Zoho Commerce is a good fit for this use case. However

• Introducing WhatsApp integration in Bigin

  Greetings! In today's business landscape, messaging apps play a significant role in customer operations. Customers can engage with businesses, seek support, ask questions, receive personalized recommendations, read reviews, and even make purchases—all

• Zoho One account closure vs deactivation

  I wonder what are the best practices and guidelines around deactivating vs deleting Zoho accounts in organisations? Any practical considerations?

• Global Search placement in the new UI

  Having a hard time with the global search placement in the UI redesign. Surely I can't be the only one. Previously global search placement was perfect. A bar at the top/center of the page. Exactly where you would expect it to be. Since the new UI has

• Schedule Zoho CRM reports only on Business Days

  Hello, Is it possible to schedule reports only on business days ? We currently get daily sales reports on weekend which has no value since the sales team doesn't work on weekends. Thanks

• How to install Widget in inventory module

  Hi， I am trying to install a app into Sales Order Module related list, however there is no button allow me to do that. May I ask how to install widget to inventory module related list?

• How to track a contact or customer's past product purchases in Bigin Premiere?

  Hello there. I am the sole user of Bigin for a small company (chess club), and would like to track current and past purchases made by a contact (player) or company (family which includes the player). Players may register for multiple tournaments, take

• LENTITUD EN RECIBIR MAIL

  Buenas tardes, Estamos experimentando lentitud en la recepción de correos electrónicos.

• Items should display under specific warehouse

  I have configured the multi warehouse but it show all the items under all warehouse which is not correct according to our business logic, so i want that items should only display under that specific warehouse not under all the warehouses not even with zero quantity. Some items should be common but not all so is there any option for that purpose so i can specific the items to its warehouse. Regards

• Partial refunds

  I am trying to process refund for a one item invoice, however the refund is partial: i am getting this error while creating credit note, can anyone share some wisdom about this

• Best practice importing items and matching assemblies

  Hi, I was wondering what would be the best practice to import items and composite items (assemblies) From my backup, what should I import first? The items or the composite items? I am on Zoho one, using inventory and books. Kind regards, Sabine

• Bulk Fill In & Edit PO/Bill/SO/Invoice

  Hello, I am adding stock in bulk on a PO, the system is automatically populating the Rate (price) and Tax from the item data. Problem is that the bill rate is different from the rate on the item data, so I have to manually erase each and enter the price.

• Separate Items & Services

  Hi, please separate items and services into different categories. Thank you

• 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

• Allocating inventory to specific SO's

  Is there a way that allocate inventory to a specific sales order? For example, let's say we have 90 items in stock. Customer 1 orders 100 items. This allocates all 90 items to their order, and they have a back order for the remaining 10 items which could

• Cancelled Transfer order problem

  Hello, We've canceled a transfer order, and we can't add the related items to a new Transfer Order. The system tells us that the bin doesn't have the required quantity, but when we check the item, it indicates that there are 2 units in the bin. It also

• Creating a Chart from a Report

  In Zoho Analytics, is it possible to create a chart from a Pivot View report? We are looking to use Zoho Analytics to replace Excel for Sales reports and would like to be able to show both the table and the chart together.

• Client Portal ZOHO ONE

  Dear Zoho one is fantastic option for companies but it seems to me that it is still an aggregation of aps let me explain I have zoho books with client portal so client access their invoice then I have zoho project with client portal so they can access their project but not their invoice without another URL another LOGIN Are you planning in creating a beautiful UI portal for client so we can control access to client in one location to multiple aps at least unify project and invoice aps that would

• Email Insights included in Bigin emals are marked as SPAM everywhere

  Today I noticed that email recipients who use Office 365 never receive emails sent from Bigin. Further examination showed that all Email Insights links in email headers are marked as spam/phishing by Office 365. Example screen included. The problem is

• Data Import | Zoho Analytics Custom Query Window Size

  Please increase the window size for the Custom Query Data Import. It's impossible to work with such a small query window.

• Name changed in settings for mailbox but still not changed when typed in To field

  In the email account secretary@ i have updaetd the new staff members details but the old members name still appears when I type secretary@ in the To field. I cant work out where Zoho is finding the old name from. I have deleted the browser cache. If I

• Cannot add my name to my domain name

  I want to have My name@mydomain.com and it says my name is linked to another account already. Please fix it since I do not have another account.

• Invoice status on write-off is "Paid" - how do I change this to "Written off"

  HI guys, I want to write off a couple of outstanding invoices, but when I do this, the status of the invoices shows as "Paid". Clearly this is not the case and I need to be able to see that they are written off in the customer's history. Is there a way

• Establishing new Zoho email account on laptop

  Good Morning: I am very long time Outlook business user and decided to try your email service last night and had established an account. I am trying to verify my account; how do I establish my Zoho email account on my laptop? I opened the account with

• unable to send message reason 550 5.4.6 unusual sending activity

  My email account can't send message. It shows unable to send message reason 550 5.4.6 unusual sending activity detected

• how to add email to existing organization i w

  I am already registered my organization and i have an email id. I need one more email id but i can't find anywhere .i want the cheapest email id . how to add ?

• e-mail bloqueado

  Estou com meu e-mail lucas@peplus.me bloqueado, preciso desbloquear para retorno de usos em minhas atividades.

• zoho labels api not working

  We're using n8n to automte email reply using zoho api. I'm facing issue with label api. I added the required scopes but its not working. i followed zoho api documentation but didn't work. also, where do i find/how do i create zoho oauth token mentioneeed

• Desk DMARC forwarding failure for some senders

  I am not receiving important emails into Desk, because of DMARC errors. Here's what's happening: 1. email is sent from customer e.g. john@doe.com, to my email address, e.g info@acme.com 2. email is delivered successfully to info@acme.com (a shared inbox

• Streams/Shared email doesn't show up in windows trident app. It works fine on MAC. Is there any difference between 2 install ?

  I can see streams/share email boxs on my MAC version of trident app but i can't see them in windows version of trident app. Is there any difference between 2 install? I try to find setting but not able to see any setting to add stream/share email boxes.

• add zoho account

  How to add a zoho mail to previous zoho account? I have two

• Zoho Desk Mobile App Year-End Roundup - 2025

  Dear Zoho Desk users, Greetings! As you gear up for the festive season, we are excited to share a quick journey into all that is released in 2025! Zia's generative AI capabilities Zia insights can be highly beneficial in helping agents manage daily support

• Next Page