How to Add Data Bars in Excel (2 Easy Methods)

Data bars are the graphical representation of values of a dataset in excel. They add an upgraded dimension to your worksheet in terms of presentation, clarification of information and lots more. In this article, you will learn how to add data bars in excel with 2 easy methods. Let’s go ahead.


Download Workbook

Get the sample workbook here and practice.


2 Easy Methods to Add Data Bars in Excel

To illustrate the 2 methods to add data bars, we have prepared a dataset. It shows the profit of 7 companies in the year 2020. We will illustrate this data through data bars here which will benefit us to get a graphical comparison.

How to Add Data Bars in Excel

Let’s look at the following methods to add data bars:

Method 1: Add Data Bars with Conditional Formatting in Excel

Conditional Formatting is a useful tool that helps excel users change the appearance of a dataset based on certain conditions or criteria such as minimum or maximum numbers, range of values etc. Here, we will see the first method to add data bars with Conditional Formatting in excel. To conduct this, follow the steps below:

  • First, select the range (C5:C11) of your datasheet.

Add Data Bars with Conditional Formatting

  • Secondly, on the Home tab, go to the Styles section and click on Conditional Formatting.

Add Data Bars with Conditional Formatting

  • Then, select Data Bars from the Conditional Formatting drop-down section.

Add Data Bars with Conditional Formatting

  • After that, select any type of bar style from the options here.

Add Data Bars with Conditional Formatting

  • That’s it, you have successfully added data bars to your dataset. It is shown inside the range that you selected in the beginning.

  • Now, we will check if the data bar is working or not.
  • For example, we changed the value in cell C8. You can see that the bar length changed automatically according to its value.

Note: Data Bars are also applicable for negative values. It will just show the bar red in the opposite direction like this:

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


Method 2: Use Excel VBA Macro to Add Data Bars

Another process to add data bars in excel is to use excel VBA Macro. In this method, we will be doing conditional formatting just like before but in the Format Conditions in the Macro. Let’s follow the steps below to add data bars in the same dataset:

  • Initially, press Alt+F11 or select the Visual Basic feature from the Developer tab.

Excel VBA Macro to Add Data Bars

  • Then, go to Insert and select Module.

  • After that, a new blank page will appear.
  • Insert this VBA code inside this page.
Sub databarsfc()
Dim dtrg As Range
Dim dtdb As databar
Set dtrg = Range("C5", Range("C11").End(xlDown))
Set dtdb = dtrg.FormatConditions.AddDatabar
With db
    'pos bar formatted with blue gradient & blue border
    .BarColor.Color = vbBlue
    .BarFillType = xlDataBarFillGradient
    .BarBorder.Type = xlDataBarBorderSolid
    .BarBorder.Color.Color = vbBlue
    'the axis set automatically and coloured red
    .AxisPosition = xlDataBarAxisAutomatic
    .AxisColor.Color = vbRed
    'the neg bar formatted with red gradient and red border
    With .NegativeBarFormat
        .ColorType = xlDataBarColor
        .Color.Color = vbRed
        .BorderColorType = xlDataBarColor
        .BorderColor.Color = vbRed
    End With
End With
End Sub

Excel VBA Macro to Add Data Bars

  • Now, press the F5 key on your keyboard or select the RunSub button from the upper layer.

  • You will be directed to the Macros window.
  • Here, click on Run.

Excel VBA Macro to Add Data Bars

  • Finally, we have completed the task to add data bars in excel.

  • You can customize these data bars by adding more commands for conditional formatting in the VBA macro. The opportunity expands to changing colors, value, appearance etc.

Note: Here is another option of VBA code for you.

Sub DataBarFormatting()
Dim DataRange As Range
Set DataRange = Range(“C5, C11”)
DataRange.FormatConditions.Delete
DataRange.FormatConditions.AddDatabar
End Sub

Excel VBA Macro to Add Data Bars

Read More: How to Add Blue Data Bar in Excel (3 Easy Ways)


How to Customize Added Data Bars in Excel

In this section, we will see a quick guide on how to customize added data bars on conditional formatting in excel. Let’s follow the steps:

  • First, complete Method 1 as described above.
  • After that, select the whole range of datasets with data bars.

Customize Added Data Bars in Excel

  • Then, go to Conditional Formatting from the Home tab and select Manage Rules.

  • Here, click on Edit Rule.

Customize Added Data Bars in Excel

  • Finally, you can see that a new Edit Formatting Rule window appeared.

Customize Added Data Bars in Excel

  • In this window, customize your data bar according to your preference in terms of color, value, appearance etc.

Read More: [Solved]: Data Bars Not Working in Excel (3 Possible Solutions)


How to Clear Data Bars in Excel

After adding data bars, you may need to get back to the previous look. For this just go through the process below:

  • Select Clear Rules from the drop-down section of Conditional Formatting.

  • Now, choose any of the options to clear data bars depending on your preference.

Read More: How to Remove Data Bars in Excel (3 Simple Ways)


Things to Remember

  • Data Bars are applicable for values, not any data in text format.
  • It is different from Charts because Data Bars work only through the horizontal axis.
  • It would be better if you work with around 100 datasets because it may cause difficulty in interpreting the data bar in excel.
  • In the case of working with VBA code, keep in mind that, it will always keep the lowest value cell blank. It means there will be no data bar for the least value.

Conclusion

So, in this article, we have seen 2 methods to add data bars in excel. We also explored the possibilities of customization. Hope this was a helpful one. Waiting for your generous suggestions.


Related Articles

Guria

Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo