Percentage bar with conditional formatting
23 May, 2011
Hi,
I have a report with a profit % column that I would like to display visually within the report.
As I understand it I have two options...
1] Format column as a percentage bar
Pros
- allows negative values
Cons
- 100% maximum
- unable to do conditional formatting
2] Use Bar conditional formatting
Pros
- ability to customize colours
- no maximum
Cons
- doesn't display negative percentages
So is there away to have a bar of some sorts that will be able to do negative and positive values with conditional formatting i.e. I choose the colour change points and colours ?
The 100% max on the percentage bar can be dealt with if I could somehow customize the colours/conditionally format the display.
Thanks for the help
Robin
I have a report with a profit % column that I would like to display visually within the report.
As I understand it I have two options...
1] Format column as a percentage bar
Pros
- allows negative values
Cons
- 100% maximum
- unable to do conditional formatting
2] Use Bar conditional formatting
Pros
- ability to customize colours
- no maximum
Cons
- doesn't display negative percentages
So is there away to have a bar of some sorts that will be able to do negative and positive values with conditional formatting i.e. I choose the colour change points and colours ?
The 100% max on the percentage bar can be dealt with if I could somehow customize the colours/conditionally format the display.
Thanks for the help
Robin
Hi Robin,
1]You should be able to use conditional formatting with a percentage bar.
Though you will need to choose 'bar' as display type.
See example below:
2]When using a bar chart you should be able to display negative values.
You may need to change the lower bound axis value via the 'Scale' menu:
Let me know if this doesn't give you the results you were after.
Regards,
David
1]You should be able to use conditional formatting with a percentage bar.
Though you will need to choose 'bar' as display type.
See example below:
2]When using a bar chart you should be able to display negative values.
You may need to change the lower bound axis value via the 'Scale' menu:
Let me know if this doesn't give you the results you were after.
Regards,
David
Hi David,
re the percentage bar
Adding the conditional as you suggest completely changes the behaviour as follows...
If I just use a percentage bar the percentages are displayed correctly around the centre of the column i.e. negative percentages go left and positive go right as expected
Adding the following condition to this column
results in a complete change in layout as follows
As opposed to just changing the colours.
Or have I misunderstood ?
Thanks
Robin
re the percentage bar
Adding the conditional as you suggest completely changes the behaviour as follows...
If I just use a percentage bar the percentages are displayed correctly around the centre of the column i.e. negative percentages go left and positive go right as expected
Adding the following condition to this column
results in a complete change in layout as follows
As opposed to just changing the colours.
Or have I misunderstood ?
Thanks
Robin
Hi Robin,
You are correct in that, once you have applied a conditional format, a negative bar will not be displayed in a different direction.
This has been added as an enhancement request ( TASK ID = 104769 ).
Have you considered using the bar chart, and modifying the lower/upper bounds?
Regards,
David
You are correct in that, once you have applied a conditional format, a negative bar will not be displayed in a different direction.
This has been added as an enhancement request ( TASK ID = 104769 ).
Have you considered using the bar chart, and modifying the lower/upper bounds?
Regards,
David
Thanks David,
Will demo the bar chart as well - still at prototyping stage getting the business to decide what they want to see and how.
regards
Robin
Will demo the bar chart as well - still at prototyping stage getting the business to decide what they want to see and how.
regards
Robin