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

• Cliq File Upload

  While uploading large file like 500MB, it takes time, that fines. But if you resize window or move window in other screen, that uploading disappears. After upload complete & sent it will be visible

• Resizing a Record Template Background Inage

  Hi everyone, I have an issue which I can't seem to resolve: Basically, I'm designing a record template in certificate form. I've specified A5 landscape. I've set my background image the same dimensions with total pixels at 443,520. Whatever I try, when

• 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,

• Zia Actions: AI-powered Workflow Automation for Faster and Smarter Execution

  Hello everyone, Workflows got a notch better with AI-based actions. Actions such as field extraction, prediction, auto reply, and content generation facilitate quick execution with improved speed and accuracy. Zia can intercept useful details in newly

• Constant color of a legend value

  It would be nice if we can set a constant color/pattern to a value when creating a chart. We would often use the same value in different graph options and I always have to copy the color that we've set to a certain value from a previous graph to make

• What's New in Zoho POS - October 2025

  Hello everyone, Welcome to Zoho POS's monthly updates, where we share our latest feature updates, releases, changes, and more. Let’s take a look at how October went. Process returns for refunds, exchanges, or offer store credit Returns and exchanges can

• Loan and purchase

  My husband is lending me mobey to buy a vehicle intersst free ... I need to know how to record the cash receipt and how I pay it back... the money is for a vehicle do I just post the invoice for that as I normally would usung the loan money to pay for

• Zoho Connect Module in Zoho Trident

  Hi I really like where Zoho Trident is going. Having Mail and Cliq in one place is especially powerful. However, Zoho Connect really needs to be included to make this a true communication and collaboration hub. I would like to request that Zoho Connect

• Zoho FSM API Delete Record

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

• Feature enhancement: Highlight rows based on a cell value

  Hello Sheet users, We're excited to announce a new feature enhacement, shaped directly by your valuable feedback! As you might know, conditional formatting is a great tool for anyone dealing with large data sets. Previously, if you’ve ever wanted to draw

• File Field Validation

  Hello all, We are tracking our customer NDA agreements in our CRM and have created 2 fields to do so, an execution date field and a file upload field. I want to create a validation rule to ensure that when the execution date field is populated that the

• 100 record view limitation

  I have just migrated from another CRM and am starting in ZOHOcrm with over 5000 contacts. It seems that my searches and sorts are limited to 100 live records....or am I missing something. This seems to be very limiting...in a lot of scenarios (mass email,

• Fillable template with dynamic tables?

  Is there a way to build a fillable template so that users can add rows to a table? To describe what I'm trying to accomplish the table has 3 sections; a header row, some number of rows with custom information, and a summary row with totals. I can't figure

• ZUG Meet-ups are back - Across India (December 2025)

  The Zoho User Group (ZUG) meet-ups are back, and this time, we’re travelling across India to reconnect with our amazing community! From Chennai to Delhi, Bengaluru to Mumbai, we can’t wait to meet you all in person and talk everything Zoho SalesIQ, automation,

• Categorise Attachments

  We take ID, proof of address, right to work documentation and more.  I can upload a single file in to field, but we often receive multiple files for each category e.g. someone may send a separate file for the front and back of their national ID card.  My team don't have time to manipulate the files in order to upload them as a single file. The options, as far as I can tell, would be to create additional fields on attachments in order to categorise what the file is, or to be able to upload single

• Scheduling a meeting for just a 1:1 phone call

  My business is B2C and many of my customer's don't want to engage in an online meeting for what can be handled in a regular phone call. I am trying to create a new meeting invitation, but there is no venue optoin for "phone call". How are other's handling

• Need more details on API Usage Dashboard

  Hi Team, We have implemented Zoho Expense for a client and has done some integrations with well known third party ERP via api. Recently we have noticed a huge spike in the API consumption. But we couldn't get the root cause for the same. I accept there

• Power of Automation:: Automating SLA Timelines for First Response & Resolution for Issues module.

  Hello Everyone, Ever wished SLAs could update automatically based on issue severity i.e no manual tracking, no missed timelines? That is exactly what one of our customers, Alex, wanted to achieve in the Issues module. So, we have setup a simple automation

• Finding missing records

  I have a challenge and I am not really sure where to start with it. I can't find any similar threads on here, can anyone help: I have two forms, FormA and FormB. Both forms have records that contain a field called Job_Number. What I am trying to achieve

• Power of Automation :: Quick way to associate your Projects with Zoho CRM

  A custom function is a software code that can be used to automate a process and this allows you to automate a notification, call a webhook, or perform logic immediately after a workflow rule is triggered. This feature helps to automate complex tasks and

• Free webinar! Build smarter apps with Zoho Sign and Zoho Creator

  Hello, Bring the power of digital signatures to the apps you build in Zoho Creator! Connect Zoho Sign as a microservice and enable seamless e-signature workflows in your applications. This integration allows you to automate signing tasks using Deluge.

• Tables improvement ideas / features

  Heya, I've been using Zoho Tables for a few months now and wanted to post some features that I think will be greatly beneficial for the tool: 1. Ability to delete a record in automation or move a record in automation. - Usecase: I move a record from one

• 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

• Move orders scan ISBN

  Hi We have ISBN setup to be searched in items zoho but move orders dissent recognize the ISBN is there q missing configuration? regards, JS

• What's New - October 2025 | Zoho Backstage

  Hey everyone! We’ve been busy rolling out a host of upgrades for Zoho Backstage. While some major features are still going through final rounds of testing to make your event experience smooth as butter, here’s what was new and improved in October 2025.

• Zoho Analytics - Feature Request For Time Based Data Source Fetch

  Hi Analytics Team, I have a client using Zoho CRM and they want a weekly report at 4:30pm every Friday, emailed to the sales team showing a pie chart of Closed Won Deals for that week. This is easy to achieve in Analytics but not so easy to ensure the

• 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());

• 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 People Attendance Regularization – Wrong Total Hours Displayed

  While using Zoho People, I observed that the attendance regularization is showing wrong total hours when applied to past dates. For example, if a check-in is added at 10:00 AM and check-out at 6:00 PM for a previous date, the system sometimes calculates

• Add Flexible Recurrence Options for Meeting Scheduling in Zoho Cliq (e.g., Every 2 Weeks)

  Hello Zoho Cliq Team, We hope you are doing well. Currently, when scheduling a meeting inside Zoho Cliq, the recurrence options are limited to Daily, Weekly, Monthly, and Yearly. There is no ability to set a meeting to occur every X weeks — for example,

• AI generated meeting notes associated to Account or Deal

  As our organization works to improve efficiency we are looking for a solution to leverage AI to generate meeting notes and then add those notes to a CRM record such as an Account or Deal. I see Zoho has a Notebook AI offering that talks about the ability

• Subform Disabled Fields Should Remain Disabled on Edit/View

  Currently, when we disable a subform field using on user input or on add new row, it works perfectly during the initial data entry. However, when the record is saved and reopened for viewing or editing, these disabled fields become editable again. This

• How do you print a refund check to customer?

  Maybe this is a dumb question, but how does anyone print a refund check to a customer? We cant find anywhere to either just print a check and pick a customer, or where to do so from a credit note.

• Enable Screen Recording in Zoho WorkDrive Mobile Apps (Android & iOS)

  Hi Zoho WorkDrive Team, How are you? We are enthusiastic Zoho One users and rely heavily on Zoho WorkDrive for internal collaboration and content sharing. The screen-recording feature in the WorkDrive web app (similar to Loom) is extremely useful- however,

• What is Resolution Time in Business Hours

  HI, What is the formula used to find the total time spent by an agent on a particular ticket? How is Resolution Time in Business Hours calculated in Zohodesk? As we need to find out the time spent on the ticket's solution by an agent we seek your assistance

• 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

• Hide "Section" headers when using a form as a subform in "List view"

  When using a form as a subform and setting the "View Type" to "List View" it is not possible to hide the section headers. This can become an issue if I have a section which is not applicable to this subform and I hide the fields within that section and

• 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

• Add additional field to quick search results

  IN the advanced search, we can add any field to the columns. In the regular search results (before you press enter, there is no option to modify the results. It would be super useful to include a custom field where it currently displays the pipleine

• Books API Receiving an Error that Doesn't Make Sense when Creating Credit Note - trying to use 'ignore_auto_number_generation' argument

  Hello, I'm working on a newly created routine and I'm getting an error that doesn't make sense when trying to create a new Credit Note. Here is my POST request. Endpoint: https://www.zohoapis.com/books/v3/creditnotes?organization_id=########## Body:     {

• Next Page