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

• What’s New in Zoho Analytics – September 2025

  Hello Users!! In this month’s update, we’re raising the bar across multiple touchpoints, from how you bring in data, plan and track projects to how you design and brand your dashboards. We’ve added the all-new Gantt chart for project visualization, expanded

• Announcing Agentic AI - Ask Zia!

  We are delighted to roll out the new agentic AI capabilities in Ask Zia, where every stage of the BI workflow is assisted by AI. With a human-in-the-loop approach, Ask Zia ensures that you’re in command of the decision, while AI handles the complexity.

• Invitation-Based User Access in Zoho Analytics

  Hello everyone, We’re rolling out an important update on how users are added to your Zoho Analytics Organization and Workspaces. Previously, when admins added users, they were automatically added to the organization. Moving forward, to improve security

• Zoholics Europe 2025: Your Ultimate Data Analysis (Zoho Analytics) Workshop Experience

  Why should you attend? This year, Zoholics Europe 2025 is putting data analysis centre stage. With a dedicated workshop designed to answer all your data-related questions, you’ll gain practical skills, real-time solutions, and expert insights that you

• Recent Topics

• If there’s a batch at the end, the ‘mark as inactive’ function doesn’t work.

  when my batch has a date to batch end and I "mark as inactive" he batch does not obey the status and reactivates the batch.

• Seamless multilingual messaging and tone control in Zia IM Composer

  Hello everyone! Managing customer conversations across multiple languages can be time-consuming. Using Zia in the Instant Messaging (IM) Composer helps you overcome this by providing tools to translate content, adjust your tone, and refine your messages

• Advanced Deluge help needed with custom function and COQL limits

  I have a scheduled function that takes a snapshot of specific metrics and saves them to a custom module. However I'm struggling with the limits of COQL. I attach my full code, but an example of the issues I'm having is that it's erroring with a 'Limit

• Tip of the Week - Conditional Formatting Chart

  Conditional formatting allows you to highlight your data series based on a pattern or a trend in your data. This makes it easy for you to identify when your data reaches certain values or when it deviates from the trend.  Zoho Analytics allows you to

• Zoho Learn - AI Translate

  Hi Learn team, I had a situation today where I was providing training to a client team and I had written articles into their Zoho Learn to support their learning. I realised that one of the team members was a non-native English speaker and they were struggling

• Payroll In Canada

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

• Power of Automation :: Auto-Sync Custom Date Field Between Related Tasks via Blueprint Transition

  Hello Everyone, 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

• Introducing auto-upgrade in Zoho LandingPage

  Hi everyone, We’re excited to share an upcoming update to help you get the most out of your landing pages — the auto-upgrade option, launching on October 22, 2025. Your landing pages are designed to bring in leads, and we want to make sure you never miss

• Workdrive comment links stopped working.

  I have marked a PDF file with 95 comments, they have worked for a while. When i clicked on them they brought me to the correct page and showed me the outline that i had drew on the plans. The comments included the page number of the outline, but now it

• 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

• How to Bulk-Update Sales Orders in CRM

  Hi - I need to bulk update existing sales orders with dates from our ERP of when the sales orders were created. I made a date field on the Sales Order module where I want to insert that data. I can't Mass Update because I am not updating the fields to

• Zoho ToDo in Cliq

  Our organization utilizes Zoho ToDo in the Zoho Mail Desktop app. Is there a way for these to show up in Cliq Desktop app as well?

• 【Zoho CRM】サンドボックス機能のアップデート：カスタムビューが利用可能になりました。

  ユーザーの皆さま、こんにちは。コミュニティチームの中野です。 今回は「Zoho CRM アップデート情報」の中から、サンドボックス機能のアップデートをご紹介します。 目次 1. カスタムビューとは 2. 今回の機能アップデートについて 1. カスタムビューとは カスタムビューは、ユーザーが設定した条件に基づいてデータをフィルタリング・整理し、 重要な情報へ効率的にアクセスできるようにする機能です。 「過去15日間の見込み客」や「受注間近の商談」、「特定の優先度レベルが設定された案件」など 条件を指定してわずか数クリックで設定できます。

• Changing an agents email address

  How do you change an agent's email address? I keep getting a red circle even though I am an admin. And on one of my agents he has two email addresses? How is that possible?

• Zoho CRM - Potentials Tab

  Hi! When I create a Deal (Potentials tab) the header looks like this: After a refresh on the page it looks like this: What should I do so that it is displayed like in the second pic without refresh? Also I have a user that as of recently, cannot see this

• Is there a way to automatically add Secondary Contacts (CCs) when creating a new ticket for specific customers?

  Some of our customers want multiple contacts to receive all notifications from our support team. Is there a way to automatically add secondary contacts to a ticket when our support team opens a new ticket and associates it with an account? This would

• Zoho Books | Product updates | October 2025

  Hello users, We’ve rolled out new features and enhancements in Zoho Books. From iOS 26 updates to viewing reports as charts, explore the updates designed to enhance your bookkeeping experience. Zoho Books Updates for Apple Devices At WWDC 2025, Apple

• Unable to create embed code for resource of workdrive using API

  Hello Team, I am trying to create embed code for a resource using workdrive api in powershell, however facing some issues with injecting data in body. Followed Doc: https://workdrive.zoho.com/apidocs/v1/filefoldersharing/shareeveryone Please help, below

• Feature Request - Insert URL Links in Folders

  I would love to see the ability to create simple URL links with titles in WorkDrive. or perhaps a WorkDrive extension to allow it. Example use case: A team is working on a project and there is project folder in WordDrive. The team uses LucidChart to create

• not able to convert pdf to jpg and other forms and vice versa.

  i want to change my pdf to jpg, word, etc and some times jpg to pdf. i don't know how to do in this.

• Zoho CRM custom fields not showing in zoho creator

  Hi Team, I have created a Products form with Zoho CRM integration and connected it to Products module of CRM. But when I see the reports of Products in Zoho creator then I am not able to see custom fields of Products module. Only standard fields of Products

• Enable / show scroll bar when Mega Menu is opened

  Hey there I am using the mega menu add-on and experience a "flicker" whenever the mega menu opens. The reason is, that the scrollbar, which has a width of a few pixels, stops showing when the mega menu opens. As the scrollbar disappears the whole page

• Shared Mailboxes Disappeared

  Zoho Mail users in our company haven't been able to see their Shared Mailboxes for the past few hours. I've checked with App and Web Access, but they can't access them. When I send emails to their addresses, I get no error messages. They're still visible

• Reports: Custom Search Function Fields

  Hi Zoho, Hope you'll add this into your roadmap. Issue: For the past 2yrs our global team been complaining and was brought to our attention recently that it's a time consuming process looking/scrolling down. Use-case: This form is a service report with

• Zoho Inventory - Composite Items - Assembly - Single Line Item Quantity of One

  Hi Zoho Inventory Team, Please consider relaxing the system rules which prevent an assembly items from consisting of a single line item and outputting a quantity of 1. A client I'm currently working with sells cosmetics and offers testers of their products

• How can I transfer data from Production to Development environment?

  Hi, I am using Creator V6 and would like to bring all the data in production to the Development and Testing environments? Is there an easy way of doing that or I have to export and import each table?

• Add "Reset MFA" Option for Zoho Creator Client Portal Users

  Hello Zoho Creator Team, We hope you are doing well. We would like to request an important enhancement related to Multi-Factor Authentication (MFA) for client portal users in Zoho Creator. Currently, Creator allows us to enforce MFA for portal users,

• New portal SAML authentication error: User not found

  Dears, Has anyone else been experiencing this lately? I am creating a new portal authenticated by SAML (Entra ID). I followed the same process as I did with other portals, but any new portal created after this always encounters this error. I’ve been reporting

• Devis et facture multi page

  Bonjour, je suis sur Zoho invoice et je rencontre un problème sur mes devis et factures lorsqu'ils dépassent 1 page. je me retrouve souvent avec des lignes coupées ou le sous total page 1 et le total page 2. j'aimerai savoir s'il existe une possibilité

• Rotate an Image in Workdrive Image Editor

  I don't know if I'm just missing something, but my team needs a way to rotate images in Workdrive and save them at that new orientation. For example one of our ground crew members will take photos of job sites vertically (9:16) on his phone and upload

• 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

• Help with Filtering Records, HTML Pages, and Automatic File Uploads in Zoho Creator

  Hi Zoho Creator Community, I’m building a Zoho Creator application and need guidance on a few features I’ve been struggling with. I want to implement them safely and efficiently, and I’d appreciate any examples, tips, or best practices. I want users to

• How to Send Email from within a custom module (with or without an email template)

  It is possible to send an email from the Deals module. However, I can't find a way to send an email from any of our custom modules. I have tried adding an email field to the modules (even though we don't really want one or need it there). That doesn't

• Convert invoice from zoho to xml with all details

  How to convert an Invoice to XML format with all details

• Shopify Extension no longer working in CRM

  Zoho CRM and Extension are no longer working properly. We have used Shopify Basic for years. All of the sudden it stopped working, then started working again, and now no longer working again. There are several ways for Personal Information to sync from

• Add Comprehensive Accessibility Features to Zoho Writer

  Hello Zoho Writer Team, We hope you are doing well. We would like to submit a feature request to enhance Zoho Writer with a full set of accessibility tools, similar to the accessibility options already available in the Zoho Desk agent interface. 🚧 Current

• Signature issue

  Problem: The signature does not appear when replying or forwarding an email. solve issue: settintgs/Signature Check option place a signature above the content with quotation marks

• Feature Suggestion for Zoho Websites – Inspired by Squarespace Systems

  Dear Zoho Team, I’m a Zoho user and also a Squarespace Platinum Circle member, and I recently noticed the launch of Zoho Websites in India. I wanted to share some ideas for features that could enhance the platform for professional users and agencies.

• Custom View - Sort by Custom Field

  I created a custom field for our Engineering team to know which tickets to work first by numbering them.  I created a custom view to general data which includes the Engineering Priority.  However, I cannot sort the Engineering Priority column ascending

• 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

• Next Page