# [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.

📚 Note:

All the operations of this article are accomplished by using the Microsoft Office 365 application.

### 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.

### 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.

## 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

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

Get FREE Advanced Excel Exercises with Solutions!
Soumik Dutta

Soumik Dutta, having earned a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a key role as an Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Soumik not only skillfully addresses complex challenges but also demonstrates enthusiasm and expertise in gracefully navigating tough situations, underscoring his unwavering commitment to consistently deliver exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF