Dynamic Data Bar
Charts and Visualization

Multicolor Filling Bars in Excel Cells Without using Chart

In the article, you will learn how to create Multicolor Filling Bars in Excel Cells without using Chart. You can use such visualization to display the project completion status with red color for delayed and green color for on time. Or you can use this trick to show the service level or Quality score with Red, Amber, and Green color with filling bars.

Download the Free Project Management Dashboard

Dynamic Data bars for Project Completion Status-

Dynamic Data bars for Project Completion Status
Dynamic Data bars for Project Completion Status

Dynamic Data bars for Service Level with RAG-

Dynamic Data bars for Service Level with RAG
Dynamic Data bars for Service Level with RAG

Dynamic Data bars for Quality Score with RAG-

Dynamic Data bars for Quality Score with RAG-
Dynamic Data bars for Quality Score with RAG-

Watch the step by step video tutorial:

we have used two types of conditional formatting to create such data bars.

  1. Fill Background color
  2. Data Bar

In the status column (Column D), we put the formula “=100%-C2” and fill it down.

conditional formatting to create multicolor data bars
conditional formatting to create multicolor data bars

First conditional formatting we put to fill the background color on the base of Column B.

  • For “On Time”, we put green color.
Conditional formatting for Green Background
Conditional formatting for Green Background
  • For “Delayed”, we put red color.
Conditional formatting for Green Background
Conditional formatting for Green Background

Then we put the Data Bar Conditional formatting with below setting-

 

  • Check Show Bar only.
  • Take “Type” as “Number” for Minimum and Maximum.
  • Put 0 for minimum and 1 for maximum.
  • Select color as white.
  • Select Bar Direction Right-to-Left

 

Data Bar Conditional formatting
Data Bar Conditional formatting

 

  • Click on OK

 

Project Completion Status
Project Completion Status

 

Click here to download the practice files.

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 10 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com