Last Updated: 20/08/2026
Working with spreadsheets in LibreOffice Calc already makes you aware of how easily rows of numbers might turn into a wall of noise. Conditional formatting solves this by automatically altering a cell’s color, icon, or shading depending on rules you specify. Let LibreOffice Calc highlight the key values for you rather than manually scanning a column of salaries, sales data, or dates.
In this tutorial, we’ll walk through five practical types of conditional formatting in LibreOffice Calc: general conditions, colour scales, data bars, icon sets, and date-based rules – using a simple wages spreadsheet as our working example.
Getting Started
Open LibreOffice Calc and make a fresh blank spreadsheet. For this instance, we have a basic dataset consisting of one column with employee names and another with weekly wages. Right-click the column → Format Cells → Currency to first format the wages column as Currency so the values show correctly before applying any formatting rules.
1. General Condition Formatting (Highlight Cells by Value)
The most often used application is highlighting cells depending on a numerical threshold.
- Choose the cells’ range you wish to format.
- Use Format → Conditional → Condition.
- Enter a number (e.g., 2000) and choose Cell value → Less than. Leave out currency symbols; just the numerical value.
- Click New Style, name it something evocative (e.g., “Less than 2000”), and choose a background colour yellow, for instance.
- Click Add to stack extra conditions.
- Less than 3000 → blue
- Less than five thousand → green.
Order matters. From least to largest threshold, LibreOffice assesses conditions in the sequence listed. This guarantees that the “less than 2000” rule catches a value like 1,800 instead of allowing something more general to pass through. Once applied, altering any value in the range instantly changes its colour in real time.
2. Colour Scale Formatting
Without defining particular thresholds, colour scales provide you an at-a-glance heat map of your data.
- Select your data range.
- Go to Format → Conditional → Colour Scale.
- LibreOffice assigns the lowest value to red, the middle value to yellow, and the highest value to green, fading smoothly between them (the midpoint sits at 50%).
- Click OK.
This is ideal for quickly visualising highs, lows, and mid-range values across a dataset – for example, comparing wages, sales performance, or scores at a glance.
3. Data Bar Formatting
Data bars turn each cell into a mini bar graph, giving you a visual sense of relative magnitude directly inside the cell.
- Select your data range.
- Go to Format → Conditional → Data Bar.
- Leave the default settings, or explore More Options to customise the display – for example, checking “Display bar only” to hide the numeric value and show just the bar.
The bar length is calculated relative to the minimum and maximum values in your selected range. If your highest value doubles, a value at half that amount will fill roughly 50% of the bar. Data bars also support negative values, which display as bars extending in the opposite direction (often shown in red by default, though you can customise this).
4. Icon Set Formatting
Icon sets apply visual indicators – like traffic lights, arrows, stars, or flags – based on percentage or value thresholds.
- Format your cells as Number (not currency) if you’re tracking counts, like weekly sales totals.
- Select the range and go to Format → Conditional → Icon Set.
- Choose an icon style (e.g., 3 Traffic Lights).
- Define your thresholds – for example: ≥ 90% (green), ≥ 50% (yellow), ≥ 5% (red).
- Click OK.
As you update the underlying values, the icons automatically shift to reflect the new thresholds – useful for quick performance dashboards.
5. Date-Based Conditional Formatting
You can also format cells based on dates relative to today – a great way to flag overdue items or highlight recent activity.
- Enter dates into a column (e.g., payment dates).
- Select the range and go to Format → Conditional → Condition, then choose Date is.
- Set one rule for “Last week” with a yellow style, and another for “This week” with a green style.
- Click OK.
Because this is calculated relative to the current date, the formatting updates automatically every time you open the file – no manual re-checking required.
Bonus Tips
- Copying data without formatting: If you copy cells with conditional formatting applied and want to paste just the values, use Paste Special instead of a standard paste. This strips the conditional formatting rules from the pasted cells.
- Editing existing styles: To change a colour you’ve already applied (e.g., swap blue for orange), go to Format → Conditional → Manage, select the relevant rule, and click Edit. Alternatively, go to Styles → Manage Styles, find your custom style name (e.g., “Last Week” or “Less than 3000”), and modify it directly – this updates every cell using that style at once.
Conclusion
Conditional formatting is one of the most powerful – and underused – features in LibreOffice Calc. Whether you’re colour-coding wages by threshold, building a heat map with colour scales, visualising performance with data bars, flagging KPIs with icon sets, or tracking deadlines with date rules, these tools turn a plain spreadsheet into an at-a-glance visual report.
Experiment with combining different formatting types across your own datasets – once you’re comfortable with the basics, you’ll find countless ways to apply conditional formatting to make your data easier to read and act on.
