[Fixed]: Conditional Formatting in Data Bar Percentage Not Working in Excel

The conditional formatting data bar is an amazing feature of Microsoft Excel to visualize the data-changing pattern. Using these data bars, we can display our data in multiple formats. The Percentage format is one of them. However, sometimes the conditional formatting data bar of the percentage format doesn’t work effectively. In this article, we are going to demonstrate 5 quick solutions to fix the conditional formatting in the data bar percentage that is not working in Excel. If you are also curious about it, download our practice workbook and follow us.

When Conditional Formatting in Data Bar Percentage Is Not Working in Excel: 5 Quick Fixes

To demonstrate the solutions, we consider a dataset of 10 employees of a company and their salaries. So, our dataset is in the range of cells B5:C14.

1. Avoid Non-Numeric Value

To display the dataset with the conditional formatting percentage format, we have to avoid the presence of non-numeric data in our dataset. You may notice that in the Income column, there is some trapped non-numeric value. As a result, we cannot apply the conditional formatting data bar with percentages.

The procedure to set the data formatting to a numeric value is given below:

📌 Steps:

• First of all, select cell C8.
• Now, eliminate the trapped non-numeric entity from the cell.
• Press Enter.

• Similarly, remove the non-numeric value from cell C12.

• After that, apply the conditional formatting data bar with percentage formatting, and you will get the result.

Thus, we can say that our procedure works perfectly, and we are able to fix the problem of conditional formatting data bar percentage that is not working for non-numerical values.

2. Choosing Percent Formatting

Setting the data bar type as Percent format, we can show the conditional formatting data bar with the percentage. The steps of this approach are given as follows:

📌 Steps:

• First, select the range of cells C5:C14.
• Now, click on the drop-down arrow of the Conditional Formatting > Data Bars option and choose the More Rules option, located in the Home tab.

• As a result, a small dialog box called New Formatting Rules will appear.
• Then, in the Type option, click on the drop-down arrow of the Minimum option and change the option from Automatic to Percent.
• Similarly, change the Type option for the Maximum value from Automatic to Percent.
• The Minimum and the Maximum values will be automatically set by Excel. Here, the Minimum value of 0 will be the lowest value of the dataset, and the Maximum value of 100 will be the highest value of the dataset.

• After that, select the Fill type according to your desire. We choose the Gradient Fill option.

• At last, click on OK.
• You will get the data bar with the percentage format.

Hence, we can say that our approach works effectively, and we are able to solve the problem.

Read More: How to Use Data Bars with Percentage in Excel

3. Applying Automatic Formatting

Besides the Percent format, we can also use the Automatic format to show the data bar with the percentage. The procedure is almost similar to the previous method. Yet, we are describing the process in step-by-step:

📌 Steps:

• First, select the range of cells C5:C14.
• After that, in the Home tab, click on the drop-down arrow of the Conditional Formatting > Data Bars option and choose the More Rules option.

• As a result, a small dialog box called New Formatting Rules will appear.
• Now, keep the Type option for both Minimum and Maximum in the Automatic option.
• Then, click on the drop-down arrow of the Fill option and choose it according to your desire. We choose the Gradient Fill option.

• Finally, click on OK.
• You will figure out the data bar with the percentage format.

Therefore, we can say that our method works precisely, and we are able to resolve the issue that is conditional formatting data bar with the percentage formatting not working in Excel.

4. Show Data Bar Separately

We can also show the data bar separately so that we can easily visualize the data changing pattern. The steps of this method are explained below:

📌 Steps:

• Firstly, select the range of cells C5:C14 and press ‘Ctrl+C’ to copy the data.

• Now, select cell D5 and press ‘Ctrl+V’ to paste the data.

• After that, select the range of cell D5:D14.
• Then, in the Home tab, click on the drop-down arrow of the Conditional Formatting > Data Bars option and choose the More Rules option.

• As a result, a small dialog box called New Formatting Rules will appear.
• Next, in the Type option, click on the drop-down arrow of the Minimum option and change the option from Automatic to Percent.
• Similarly, change the Type option for the Maximum value from Automatic to Percent.
• Excel will automatically set the Minimum and the Maximum values. Here, the Minimum value of 0 will be the lowest value of the dataset, and the Maximum value of 100 will be the highest value of the dataset.

• After that, select the Fill type according to your desire. We choose the Gradient Fill option.

• At last, check the Show Bar Only option.

• Finally, click OK.
• You will notice that the data will disappear, and only the data bar will be displayed in the percentage format.

So, we can say that we are able to fix the trouble caused by the Excel conditional formatting data bar.

5. Re-installing Updated Version of Excel

Re-installing the updated version is the ultimate way to resolve this issue. The updated version of Excel neglects the cell format of the Number group, and it automatically sets the cell formatting by the cell entities. As a result, the cell formatting doesn’t affect much, and the conditional formatting data bar with the percentage format can easily be applied to the dataset.

Download Practice Workbook

Download this practice workbook for practice while you are reading this article.

Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to fix the problem of the Excel conditional formatting data bar with the percentage format not working. Please share any further queries or recommendations with us in the comments section below if you have any other questions or suggestions.

Related Articles

• How to Add Data Bars in Excel
• How to Add Solid Fill Data Bars in Excel
• How to Add Blue Data Bar in Excel
• How to Define Maximum Data Bars Value in Excel
• [Solved]: Data Bars Not Working in Excel
• How to Remove Data Bars in Excel
• Conditional Formatting with Data Bars Based on Another Cell in Excel
• Conditional Formatting Data Bars Different Colors

<< Go Back to Data Bars | Conditional Formatting | Learn Excel