Last Updated: 18/06/2026
If you’re just getting started with spreadsheets, one of the first skills you need to master is cell formatting. Whether you’re tracking expenses, building a schedule, or organising data, knowing how to properly format cells in LibreOffice Calc makes your spreadsheets easier to read and far more professional. In this tutorial, we’ll walk through the essential formatting options available in LibreOffice Calc – from currency and dates to percentages, numbers, text, and time.
Getting Started
To begin, open LibreOffice Calc. It works just like any other spreadsheet application, with rows and columns ready for your data. The key to working efficiently is learning how to quickly format entire columns or selected cells rather than adjusting each one individually.
To access formatting options, simply highlight a column (by clicking the column letter) or select the cells you want to format, then right-click and choose “Format Cells.” This opens a dialog box with multiple formatting categories to choose from.
Formatting Currency
One of the most common formatting needs is displaying monetary values correctly.
- Select the column or cells you want to format.
- Right-click and choose Format Cells.
- Select Currency from the category list.
- Choose your preferred currency from the drop-down list (e.g., GBP, USD, EUR).
- Set the default language, which determines regional formatting conventions.
- Choose the number of decimal places – by default this is set to two.
Once applied, any numbers you type into that column will automatically display the correct currency symbol. For example, typing 55.80 will show as £55.80.
A useful tip: if you reduce the decimal places to zero, LibreOffice Calc will automatically round the number for display purposes (while still storing the original value). For instance, £55.80 rounds up to £56, since .80 is closer to 1 than to 0.
You can also set negative currency values to display in red, making it easy to spot losses or deductions at a glance. Simply enable this option in the Format Cells dialogue, and any negative number (like -£55.80) will automatically appear in red text.
Formatting Dates
Dates are another area where formatting really matters, especially since date conventions vary by country.
- Select the cells or column you want to format.
- Right-click and select Format Cells, then choose the Date category.
- Browse the list of available formats – you’ll notice options for different regions (UK, US, and beyond), as well as different languages, which change how dates are spelled out (e.g., Italian vs. English).
- Select the format that matches your needs. For UK users, the standard order is day, month, year.
Once your format is set, typing a date like 12/12/2018 will automatically adjust to match your chosen format – for example, displaying as 12/12/18.
Time-saving tip: Use the fill handle (the small square in the bottom-right corner of a selected cell) to drag down and automatically generate a sequence of dates in numerical order – no need to type each one manually.
If you ever see a column full of hash symbols (###) instead of your data, don’t worry – this simply means the column isn’t wide enough to display the content. Just click and drag the column border to widen it.
Formatting Percentages
Percentages are essential for reports, budgets, and performance tracking.
- Select your target cells.
- Right-click and choose Format Cells, then select Percentage.
- Click OK, and any number you enter (like 12) will automatically be converted and displayed as 12.00%.
By default, LibreOffice Calc shows two decimal places. If you’d rather show just one decimal (e.g., 12.5% instead of 12.50%), you can adjust the decimal place setting directly in the Format Cells dialogue.
Rounding behaviour: Just like with currency, reducing decimal places causes LibreOffice Calc to round automatically. For example, 12.6% rounds up to 13% when no decimals are shown, while 12.3% rounds down to 12%.
As with currency, you can also configure negative percentages to appear in red for quick visual identification.
Numbers vs. Text
Here’s a subtle but important distinction: LibreOffice Calc treats numbers and text differently, even when they look similar.
- If you type a plain number (like 4433), it’s stored and treated as a numeric value – meaning it can be used in calculations.
- If you format a cell as Text before typing, the same characters are stored as text information, not numbers – even if they look numeric.
This distinction matters most when working with values that shouldn’t be treated as numbers for calculation purposes – like phone numbers. For example, if you type +44 into a numerically formatted cell, the plus sign will be stripped out. To preserve it, format the cell as Text first, then enter the number. This keeps formatting like leading zeros or plus signs intact.
Formatting Time
LibreOffice Calc also supports detailed time formatting:
- Select your cells, right-click, and choose Format Cells.
- Select the Time category.
- Choose a format that includes hours, minutes, and seconds – with or without AM/PM display.
For example, entering 11:30 will display as 11:30 AM, while a value like 14:30 will automatically convert to 2:30 PM. If you prefer a 24-hour clock without AM/PM labels, simply select that variation from the format list.
Experiment with the different time formats to find the one best suited to the type of data you’re tracking.
Final Thoughts
Mastering cell formatting in LibreOffice Calc – currency, dates, percentages, numbers vs. text, and time – is a foundational skill that will make your spreadsheets clearer, more accurate, and easier to work with. Once you’re comfortable with these basics, you’ll be ready to explore more advanced formatting options, like scientific notation and fractions, in future tutorials.
