How to Fill Cell with Color Based on Percentage in Excel (6 Methods)

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for some special tricks to fill the cell with color based on percentage in Excel, you’ve come to the right place. In Microsoft Excel, there are numerous ways to fill the cell with color based on percentage. In this article, we’ll discuss six methods to fill the cell with color based on the percentage. Let’s follow the complete guide to learn all of this.


Fill Cell with Color Based on Percentage in Excel: 6 Methods

We have here a dataset that contains the percentage of UCB Company’s sales from different months. The main objective is to fill cells with color based on percentage.

In the following section, we will use 6 methods to fill cells with color based on the percentage.


1. Using Formula to Fill Cell with Color Based on Percentage

The following steps should be followed in order to fill the sales column with color that represents 10 percent to 15 percent of sales.

Using Formula to Fill Cell with Color Based on Percentage

📌 Steps:

  • Firstly, select the range of cells C5:C10. Go to the Home tab, select Conditional Formatting, and finally select New Rule.

Using Formula to Fill Cell with Color Based on Percentage

  • When the New formatting Rule dialog box appears, select the option-Use a formula to define which cells to format.
  • Next, in the Format values where this formula is true box, you have to insert the following formula:

=AND(D5>10%,D5<15%)

Here, D5 is the first cell of the percentage column.

Using Formula to Fill Cell with Color Based on Percentage

  • Now, click on Format, when the Format cells dialogue box appears, you have to fill your desired color by selecting the Fill Click on OK.

Using Formula to Fill Cell with Color Based on Percentage

Finally, you will get the following output which is highlighted based on the criteria:


2. Fill Cell with Color Based on Cell Value in Excel

In order to fill the sales column with the color that represents the lowest percentage of sales, follow these steps.

Fill Cell with Color Based on Cell Value in Excel

📌 Steps:

  • Firstly, select the range of cells C5:C10. Go to the Home tab, select Conditional Formatting and finally select New Rule.

  • When the New formatting Rule dialog box appears, select the option Format all cells based on their values.
  • In the Format style option select 2-Color Scale.
  • When the Minimum and Maximum option appears, type select the required cell in the Value option.

Fill Cell with Color Based on Cell Value in Excel

In column C, cell C8 is highlighted in a different color because it has the lowest value among all cells:

Read More: How to Change Cell Color Based on a Value in Excel


3. Using ‘Greater Than’ Option to Fill Cell with Color Based on Percentage

The following steps should be followed in order to fill the percentage column with a color that represents greater than 20 percent of sales.

Fill Cell with Color Based on Cell Value in Excel

📌 Steps:

  • Firstly, select the range of cells D5:D10, Go to the Home tab, select Conditional Formatting, and finally select Highlight Cells Rules.

Using Greater Than Option to Fill Cell with Color Based on Percentage

  • Next, select the Greater Than option.

  • When the Greater Than dialogue box appears, you have to fill your desired color and type 20% on Format cells that are Greater Than Click on OK.

Using Greater Than Option to Fill Cell with Color Based on Percentage

  • Finally, the cells D9 and D10 of column D are highlighted in a different color because their values are greater than 20 percent.


4. ‘Text That Contains’ Option to Fill Cell with Color in Excel

These steps will fill the month results column with a color that indicates a bad month of sales when the cell value is less than 20%.

Text That Contains Option to Fill Cell with Color in Excel

📌 Steps:

  • Firstly, select the range of cells E5:E10, Go to the Home tab, select Conditional Formatting, and finally select Highlight Cells Rules.

Text That Contains Option to Fill Cell with Color in Excel

  • Next, select the “Text That contains” option.

  • When the Text That contains a dialog box appears, you have to fill your desired color and type Bad Month on the Format cells that contain the text.  Click on OK.

Text That Contains Option to Fill Cell with Color in Excel

The cells E5:E8 in column E are highlighted in a different color because their values are less than 20 percent.


5. Using ‘Top/Bottom’ Rules Option

Following these steps will fill the percentage column with a color that indicates the lowest and highest sales percentages.

📌 Steps:

  • Firstly, select the range of cells D5:D10, Go to the Home tab, select Conditional Formatting, and finally select Top/Bottom Rules.

Using Top/Bottom Rules Option

  • Next, select the “Top10%” option.

  • When the Top 10% dialogue box appears, you have to fill in your desired color. Click on OK.

  • Next, select the “Bottom 10%” option if you want to get the lowest percentage value.

Using Top/Bottom Rules Option

  • When the Bottom 10% dialogue box appears, you have to fill in your desired color. Click on OK.

Finally, in column D, the cells D8 and D10 are highlighted in different colors because their values are the lowest and highest among all sales.

Using Top/Bottom Rules Option

Read More: How to Fill Color in Excel Cell Using Formula


6. Fill Cell with Color Using ‘Data Bars’ Option in Excel

You have to follow the following steps to fill the cell color using the Data Bars option.

Fill Cell with Color Using Data Bars Option in Excel

📌 Steps:

  • Firstly, select the range of cells D5:D10, go to the Home tab, select Conditional Formatting, and finally select Data Bars.

Fill Cell with Color Using Data Bars Option in Excel

  • Next, select your desired Gradient Fill.

Finally, you will get the following output based on your criteria.


💬 Things to Remember

If you enter a formula in the conditional formatting box, you should carefully enter the formula.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

That’s the end of today’s session. I strongly believe that from now you may fill the cell with color based on percentage. If you have any queries or recommendations, please share them in the comments section below.


Related Articles


<< Go Back to Color Cell in ExcelExcel Cell FormatLearn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Saquib Ahmad Bhuiyan, holding a BSc degree in Naval Architecture & Marine Engineering from Bangladesh University of Engineering and Technology, skillfully integrates engineering expertise with a passion for Excel. He provides solutions to complex issues as an Excel & VBA Content Developer for ExcelDemy Forum. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, Rhinoceros,... Read Full Bio

4 Comments
  1. Is there a way to use the Data Bars then when 100% is reached the color of the cell changes? Like having a checklist that each item is either a yes or no, then when the whole row is yes?

    Countif(a2:j2″yes)/counta(a1:j1)
    then conditional formatting…
    ??

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto May 2, 2023 at 1:02 PM

      Hello DOUGLAS FORMAN,

      Thanks for reaching out and sharing your exciting query. When a given condition is met, we can use data bars and conditional formatting to change the color of a cell at a time. I can help you with Excel VBA code that loops through the K column and applies your given formula. Latterly, it utilizes an If Conditional to format each cell. When iterating, if the cell contains 1, that means 100%Yes” It formats the fill color of the cell as Green. On the other hand, it uses a Data Bars feature. To get a better understanding of the issue, I am going to share the Workbook used in exploring your problem.

      Excel VBA Code:

      
      Sub DatabarAndConditionalFormatting()
      
          Dim lastRow As Long
          lastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
          
          Dim rng As Range
          Set rng = Range("K2:K" & lastRow)
          
          For Each cell In rng
              cell.Formula = "=IFERROR(COUNTIF(B" & cell.Row & ":J" & cell.Row & ",""Yes"")/COUNTA($B$1:$J$1),0)"
              cell.NumberFormat = "0.00%"
              If cell.Value = 1 Then
                  cell.Interior.Color = RGB(0, 255, 0)
              Else
                  cell.FormatConditions.AddDatabar
                  cell.FormatConditions(cell.FormatConditions.Count).ShowValue = True
                  
                   With cell.FormatConditions(1).BarColor
                      .Color = 13012579
                      .TintAndShade = 0
                  End With
              End If
          Next cell
      
      End Sub
      

      INPUT: INPUT-DOUGLAS FORMAN

      OUTPUT: OUTPUT-DOUGLAS FORMAN

      WORKBOOK: Data Bars And Conditional Formatting

      Feel free to contact us again with any other inquiries or concerns.

      Regards
      Lutfor Rahman Shimanto
      ExcelDemy

  2. can you do this with vertical databars.
    or cells
    I am looking to fill a cell (or better a range of cells) partially vertically (based on percentages)
    See it as a tank filling with liquid
    I managed to do it with sparklines in a range of cells, but then not the entire range is filled (there are white spaces on the side visible)

    • Hi Sjoerd,

      Thanks for reaching out to us. Unfortunately, Conditional Formatting cannot create a vertical progress bar. But, using Sparkline can help you achieve your goal. You mentioned using Sparkline, but we didn’t understand what you meant by “not the entire range is filled.” Could you please clarify that?

      Here’s a sample picture of a vertical progress bar that we created using Sparkline.
      Vertical Progress Bar Using Sparkline
      Were you referring to the extra space on the two sides of the column, as marked in the picture? If so, we are currently unaware of any methods to eliminate the space. Please let us know your thoughts.

      Regards
      Aniruddah
      Team Exceldemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo