Frequently asked questions - Calc

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

Other languages:

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:

Min
Max
Percentile^[1]
Value
Percent
Formula

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:

On the left, the range with the Data bar formatting, on the right, the Bars type graph

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 Format ▸ Conditional ▸ Data Bar without modifying the default options.

The following example shows the effect with the additional option Format ▸ Conditional ▸ Data Bar ▸ More options... to remove the vertical axes. The positive and negative values are aligned on the left and only the color allow to differentiate them:

Data Bar formatting without vertical axes

Notes:

↑ 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.

Download an example spreadsheet

[1] 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.

[1]