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


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.

Non-numeric data in dataset

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.

Eliminate first non-numeric data to fix conditional formatting data bar percentage not working

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

Avoid non-numeric value to fix Excel conditional formatting data bar percentage not working

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.

Launching New Formatting Rules dialog box

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

Set the Percent option in the Data Bar

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

Choosing Percent formatting to fix Excel conditional formatting data bar percentage not working

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.

Launching New Formatting Rules dialog box

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

Applying Automatic formatting to fix Excel conditional formatting data bar percentage not working

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.

Paste the dataset

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

Opening the New formatting Rules dialog box from the drop down arrow of the Conditional Formatting

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

Set the Percent option in the Data Bar

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

  • At last, check the Show Bar Only option.

Select suitable option to display only data bar

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

Excel conditional formatting data bar percentage not working

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!


Related Articles

Soumik Dutta

Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo