Create custom numeric display formats

Custom number formats change the display, not the stored value. Use explicit positive, negative and zero sections to make a report readable, then verify the underlying arithmetic before relying on what the cells appear to say.

Scope and values to test

Use Apache OpenOffice Calc 4.1 desktop with English (USA) number-format language for this exercise. The detailed format-code behavior is grounded in the AOO 4.1.16 help source. Decimal and grouping separators vary with language, so do not paste these English codes blindly into another locale. No native execution is claimed.

Before entering the fixture, inspect Tools > Options > OpenOffice Calc > Calculate and ensure Precision as shown is off in the practice setup. Keep it off: if enabled, displayed rounding can affect calculation values and invalidate the checks below.

Enter these values in A1:A4: 1234.5, -1234.5, 0, and 2.345. In B1:B4 enter =A1, =A2, =A3 and =A4 respectively. Keep column B in a plain number format with at least three decimals. It will serve as a comparison column showing that custom display does not rewrite the values.

This task extends ordinary number formatting by defining display-code sections, rather than repairing numeric identifiers or text imports.

Define the three numeric cases

1. Select A1:A4 and choose Format > Cells, then the Numbers tab.
2. Set the number-format Language to English (USA) for this fixture.
3. In Format code enter #,##0.00;[RED](#,##0.00);"—" and confirm.
4. Widen column A enough to see the result without clipping.

The first section formats positive values, the second negative values and the third zero. Expected displays are 1,234.50, (1,234.50) in red, an em dash for zero, and 2.35. Parentheses provide a non-color indication of negativity, so the report does not depend on a reader distinguishing red.

The help documents that with only two numeric sections, the first applies to positive values and zero. Including the third section is therefore meaningful, not decorative punctuation. The quoted dash is literal display text; it is not a minus sign stored in the cell.

Prove that rounding is visual

Enter =A4*1000 in D1. The expected result is 2345, not 2350, because A4 still contains 2.345 even though it displays 2.35. Inspect the input line and compare B4 to verify this distinction.

Change A4 to 2.344. It should display 2.34 and D1 should become 2344. Restore it to 2.345 afterward. A format code is not a ROUND formula, and the visible precision must not be used as evidence that calculations have been rounded to cents.

Change A3 from zero to -0.5. It should switch to the negative section and display (0.50) in red. Restore A3 to 0 and confirm the dash returns. Test both sides of zero rather than checking only a convenient positive number.

Add a unit without turning numbers into text

In F1 enter 3.5. Apply the custom numeric format 0.00 "meters". It should display 3.50 meters, while =F1*2 in F2 returns 7. The quotation marks define literal text in the format; typing “3.50 meters” directly into the cell would be a different data-entry operation.

For a separate text-format example, enter December in H1 and apply "Total for "@. The @ placeholder displays the existing text after the literal prefix. This is separate from the three numeric cases above; the version-tagged help describes numeric sections and text formatting independently, so this exercise does not rely on an undocumented four-section combination.

Recover and save

If a format looks wrong, reopen Format Cells and inspect the code and language. Check semicolons, matching quotes and decimal separators before changing the source data. Apply a normal Number format to return to a plain display; that should reveal the same underlying values.

Save as ODS, close and reopen, then verify the negative indicator, zero dash, 2345 calculation and unit-bearing display. For external exports, check the actual output: a text or CSV export may use displayed text, and recipients cannot infer original precision from a PDF.

Official references

Apache OpenOffice 4.1.16 number-format code help, captured 20 October 2026; Calc Guide, Chapter 2. Formatting and persistence checks remain reader exercises, not native-test results.