Saquib Ahmad Shuvo
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
6 Methods to Fill Cell with Color Based on Percentage in Excel
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 the 10 percent to 15 percent of sales.
📌 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-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.
- 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.
Finally, you will get the following output which is highlighted based on criteria:
Read More: How to Highlight a Cell in Excel (5 Methods)
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.
📌 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 required cell in the Value option.
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 (5 Ways)
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.
📌 Steps:
- Firstly, select the range of cells D5:D10, Go to the Home tab, select Conditional Formatting, and finally select Highlight Cells Rules.
- 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.
- Finally, the cells D9 and D10 of column D are highlighted in a different color because their values are greater than 20 percent.
Read More: How to Compare Two Excel Sheets and Highlight Differences (7 Ways)
Similar Readings:
- How to Highlight Selected Cells in Excel (5 Easy Ways)
- Highlight Selected Text in Excel (8 Ways)
- How to Highlight Cells in Excel Based on Value (9 Methods)
- Highlight Cells That Contain Text from a List in Excel (7 Easy Ways)
- How to Highlight Cell Using the If Statement in Excel (7 Ways)
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%.
📌 Steps:
- Firstly, select the range of cells E5:E10, Go to the Home tab, select Conditional Formatting, and finally select Highlight Cells Rules.
- 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.
The cells E5:E8 in column E are highlighted in a different color because their values are less than 20 percent.
Read More: How to Highlight Cells Based on Text in Excel [2 Methods]
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.
- 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.
- 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.
Read More: How to Fill Color in Excel Cell Using Formula (5 Easy Ways)
6. Fill Cell with Color Using ‘Data Bars’ Option in Excel
You have to follow the following steps to fill cell color using the Data Bars option.
📌 Steps:
- Firstly, select the range of cells D5:D10, go to the Home tab, select Conditional Formatting, and finally select Data Bars.
- Next, select your desired Gradient Fill.
Finally, you will get the following output based on your criteria.
Read More: Excel Formula Based on Cell Color (5 Examples)
💬 Things to Remember
- If you enter a formula in the conditional formatting box, you should carefully enter the formula.
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.
Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!
