How to use Data Bar Conditional Formatting
Conditional formatting using a Data bar is particularly suitable for providing an immediate visual impact when comparing data. It avoids the need to create a Bar chart, while offering the advantage of an in situ comparison.
This layout is conditional, that is to say that the bar displayed is dependent on the cell value.
This format uses two colors (Light Blue default for "positive" bars and Light Red for "negative" ones).
In addition to the colors you can either keep the calculation Automatic (varies from the minimum value to the maximum value) or choose the method of calculation:
The choices Min and Max are sufficient to themselves as found in the range. Other options need to be specified by a value (Percentile, Value, Percentage) or a cell reference or formula (Formula).
The following example illustrates side by side a Data bar formatting and a Bar chart that could be generated as an alternative way of representing the data:
Note: the data bar "uses" all the width of the column. Re-sizing of bars follow the resizing of the column.
To realize this example, you only need to use the menu entry▸ ▸ without modifying the default options.
The following example shows the effect with the additional option▸ ▸ ▸ to remove the vertical axes. The positive and negative values are aligned on the left and only the color allow to differentiate them:
- A percentile is each of the 99 values which divide the sorted data in 100 equal parts, so that each part represents 1/100 of the sample population. A percentile return the value for a data series going from the smallest to the largest value in a set of data. For Alpha = 25, the percentile means the first quartile = 50 Alpha is the MEDIAN.