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)?
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)?