Data bars in Excel - Condition formatting | Excel forum

Resolved · High Priority · Version 2010

Wed 13 Jul 2016, 10:34 Delegate Rawnit said...

Rawnit has attended:
No courses

Data bars in Excel - Condition formatting

My data contains both negative and positive values, and I wish to use Data bars (conditional formatting) on it. What I am looking to do is:

- Color of the bar is decided based on the direcion of the value (green for positive and red for negative)
- Length of the bar is decided based on the min and max absolute value in the array
- Both positive and negative values to follow the same direction

Wed 13 Jul 2016, 13:57 Trainer Richard said...

RE: Data bars in Excel - Condition formatting

Hi Rawnit,

If you highlight the cells you want
Click on Home/Conditional Formatting
Data Bars/More Rules

Axis Values are on Automatic Min & Max (Adjust manually if you want to have it show -100 to 100 for example)

Solid Fill - make this Green

Then click on the Negative Value Axis

Fill Colour Red

Axis Settings - None show in a positive direction

Kind regards

Richard Bailey
Microsoft Certified Trainer

Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Thu 14 Jul 2016, 07:47 Delegate Rawnit said...

RE: Data bars in Excel - Condition formatting

Hi Richard - Appreciate your prompt reply. I have tried this already, but there is an issue with the length of the data bars when you choose to show both -ve and +ve bars in the same direction. For ex, for following series if I choose the minimum as -5 and max as +5, and do exactly how you have suggested, then the length of data bars is different in both the cells, even though the magnitude is same, i.e. 3.

3
-3

Fri 15 Jul 2016, 09:57 Trainer Richard said...

RE: Data bars in Excel - Condition formatting

Hi,

You are absolutely right, there is a scaling issue with this feature in Excel. Unfortunately we don't yet have a solution for that within the data bar feature.

Kind regards

Richard

Fri 15 Jul 2016, 11:48 Delegate Rawnit said...

RE: Data bars in Excel - Condition formatting

Hi Richard,

Finally found a way to format the way I wanted via VBA. Pasting it here in case anyone needs. I am not very good at VBA so it might be a bit dirty.

=====================================================================

Public Sub Custom_Data_Bars()
'Removing the existing formatting from the selected cells
Selection.FormatConditions.Delete

Dim cll As Range
Dim cll1 As Range
Dim cf As Databar
Dim Min1, Max1 As Double
Dim a1, a2 As Double

Set cll1 = Selection
Max1 = Application.WorksheetFunction.Max(cll1)

a1 = Application.WorksheetFunction.Max(cll1)
a2 = Application.WorksheetFunction.Min(cll1)

If a1 < 0 Then a1 = -a1
If a2 < 0 Then a2 = -a2
If a1 > a2 Then Max1 = a1 Else Max1 = a2

For Each cll In Selection
'Create an DataBar object
Set cf = cll.FormatConditions.AddDatabar()
If cll > 0 Then
cf.MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=0
cf.MaxPoint.Modify newtype:=xlConditionValueNumber, newvalue:=Max1
cll.FormatConditions(1).BarFillType = xlDataBarFillSolid
cll.FormatConditions(1).Direction = xlLTR
cll.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
cll.FormatConditions(1).BarBorder.Type = xlDataBarBorderNone
cll.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
With cll.FormatConditions(1).BarColor
.Color = 5287936
.TintAndShade = 0
End With

Else
If cll < 0 Then
cf.MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=-Max1
cf.MaxPoint.Modify newtype:=xlConditionValueNumber, newvalue:=0
cll.FormatConditions(1).BarFillType = xlDataBarFillSolid
cll.FormatConditions(1).Direction = xlRTL
cll.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
cll.FormatConditions(1).BarBorder.Type = xlDataBarBorderNone
cll.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
With cll.FormatConditions(1).NegativeBarFormat.Color
.Color = 255
.TintAndShade = 0
End With
Else
cll.FormatConditions.Delete
End If

End If
Next

End Sub

=====================================================================