98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Data bars in Excel - Condition formatting | Excel forum
Data bars in Excel - Condition formatting | Excel forum
Resolved · High Priority · Version 2010
Wed 13 Jul 2016, 10:34Delegate 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:57Trainer 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:47Delegate 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:57Trainer 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:48Delegate 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
=====================================================================
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
