question about data bars and conditional formatting (1 Viewer)

martinr

Registered User.
Local time
Today, 13:34
Joined
Nov 16, 2011
Messages
73
using Excel 2013, I’m trying to use conditional formatting to display data bars inside cells linked to percentage values, so a cell with a value of 10 % is easy to differentiate from a cell with 80 % etc.
When I copy the format to multiple cells (1,000 rows) it automatically sets the range (in the manage rules; 'applies to' setting) to include all of the rows (=$I$3:$I$1012). This distorts the display because there are some outliers (eg over 100%), so the the cells with 100% look about 50%.
Changing the setting for each manually is not possible with so many rows, so how do I set the rule so that each cell always displays the ‘data bar’ based on 0 to 100 range?
(or relative to itself)?
 

Pezzini

Registered User.
Local time
Today, 02:34
Joined
Dec 9, 2015
Messages
20
Try this, highlight all your data, then do conditional formating, hover over the data bar options and then click on "More Rules". In this set Minimum to Number and 0 and Maximum to number and 1. Do this and 100% is full bar, but so is 1000%, but if that's what you're after, this works.
 

Users who are viewing this thread

Top Bottom