Highlight Exceptions with Conditional Formatting in OpenOffice Calc

Use a named style to make unusually large readings stand out, then make the rule safe for a mixed column containing blanks and text. A conditional style changes the display when the condition changes; it does not replace the reading.

Start with an auditable sample

Create a blank spreadsheet. Keep the first sheet named Sheet1. Enter this complete fixture; B7 must be genuinely empty, not a space or the word “blank”. Enter B8 with a leading apostrophe so that it is text: type '130 (the apostrophe is not displayed).

Row	A: Reading	B: Value
1	Reading	Value
2	Dock	92
3	Loft	105
4	Yard	130
5	Gate	87
6	Boundary	100
7	Missing	leave empty
8	Imported text	‘130

Save as reading-rules.ods with File → Save As, choosing the ODF spreadsheet format. Use this small disposable workbook before trying a rule on operational data.

Create the style and numeric threshold

1. Open Format → Styles and Formatting and choose the Cell Styles list. Right-click in the list and choose New.
2. On Organizer, name the style OverLimit. On Background, choose a pale yellow; on Font, choose bold. Keep dark text so the values remain readable. Confirm with OK. Do not apply this style manually to the entire table: the condition must control it.
3. Check Tools → Cell Contents → AutoCalculate. The official AOO guide requires automatic calculation for conditional formatting.
4. Select only B2:B6. Choose Format → Conditional Formatting. Enable the first condition, select Cell value is, choose greater than, and enter 100 in the value field. Choose OverLimit as the Cell style, then confirm.
5. Inspect the five numeric cells. B3 and B4 should be highlighted. B2, B5 and B6 should retain their normal appearance. Equality is excluded: 100 is not greater than 100.

Change B3 to 99: its highlight should disappear. Change B6 to 101: its highlight should appear. Restore B3 to 105 and B6 to 100 by typing those values. If nothing changes, check AutoCalculate, the selected range, and whether the numbers are really numeric before editing colors.

Advanced case: guard the rule against text and blanks

A plain comparison is not a reliable way to distinguish imported numeric text from genuine numbers. Replace the rule with an explicit numeric test, using one anchor cell to avoid an ambiguous active-cell reference.

1. Select B2 alone and reopen Format → Conditional Formatting.
2. Change the first condition to Formula is and enter AND(ISNUMBER(B2);B2>100). Choose the same OverLimit style and confirm. Leave additional conditions disabled for this exercise.
3. Copy B2. Select B3:B8 and choose Edit → Paste Special. In Selection, clear all content options and choose Formats only. Confirm. This copies the conditional format while preserving each destination value; relative B2 references adjust to the destination row.
4. Check that B3 and B4 alone are highlighted. B6 equals the threshold, B7 is blank, and B8 contains text, so all three must fail the guarded condition. Inspect B8 in the input line and use =ISNUMBER(B8) in D8 if needed; the result must be FALSE.
5. Replace the text in B8 with the actual number 130. B8 should now highlight. Restore the original text by typing '130; the highlight should disappear again.

Do not copy ordinary cell contents over B3:B8 during this test. If you do, immediately undo that paste before making more edits, or rebuild the eight-row sample. The formula uses semicolons in Calc syntax. It is written for B2; a formula entered with another cell active must refer to that cell instead. The AOO guide explicitly warns that a dragged multi-cell selection can leave its last cell active.

Save, reopen and recover

Save and close the ODS file, then reopen it. Check the two highlighted numeric values, the unhighlighted text value, and B2’s formula rule. Change B4 temporarily to 100 and verify that its style disappears, then restore 130 and save. These are checks for you to perform, rather than reported native test results.

To remove the rule from this sample, select B2:B8, open Format → Conditional Formatting, disable the conditions and confirm. The original values should remain. To recover a wrongly changed value after reopening, reenter it from the fixture or restore a saved copy; a previous session’s Undo history is not a backup.

Limits and troubleshooting

This exercise uses AOO’s style-based conditions, not a LibreOffice color-scale or icon-set workflow. An imported Excel rule may need reconstruction. Bold and shading are supplementary cues: retain labels and visible numbers so the exception is understandable without color. A manually applied yellow background can obscure whether the conditional rule is working, so use an otherwise plain sheet. Save the native ODS master before experimenting with other formats.

Version scope and first-party references

Apache OpenOffice Calc 4.1 desktop, English menu names; documentation checked 2026-10-19. No native build or operating system was tested for this exercise. The expected numbers were calculated independently; appearance, UI interactions and persistence remain reader checks. No screenshot is presented as evidence of running this fixture.

Sources consulted on 2026-10-19:

• Apache OpenOffice Using Conditional Formatting .
• Apache OpenOffice Creating a New Style Using the Style Dialog .
• Apache OpenOffice Using the Styles and Formatting Window .
• Apache OpenOffice AOO41CG.pdf .