Soumik Dutta
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.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
5 Quick Fixes When Conditional Formatting in Data Bar Percentage Is Not Working in Excel
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.
Read More: Conditional Formatting Data Bars Different Colors
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 Define Maximum Data Bars Value in Excel (6 Easy Ways)
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:
- At 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.
Read More: How to Add Data Bars in Excel (2 Easy Methods)
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.
Read More: [Solved]: Data Bars Not Working in Excel (3 Possible Solutions)
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 that is 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.
Don’t forget to check our website, ExcelDemy, for several Excel-related problems and solutions. Keep learning new methods and keep growing!
