If you manage spreadsheets, formatting can make your life much easier. LibreOffice offers various ways to format your spreadsheets, which includes doing it manually or using the autoformatting tool. You might think the formatting options in autoformatting are rather limited. If so, you can create custom formatting that can be added to the autoformatting tool to make LibreOffice Calc even more user friendly and flexible.

I’ll walk you through the process of formatting and autoformatting a LibreOffice Calc document. Then, I’ll cover how to add your own formatting to the autoformatting tool, as well as how to use themes. In order to follow along, you’ll need the latest version of LibreOffice and possess a basic understanding of spreadsheets.

Basic formatting

If you already know how to format a spreadsheet, skip to the next section.

Once your spreadsheet is laid out, you want to look at it in terms of elements. You can break down your spreadsheet in terms of columns, rows, and blocks of similar data.

Let’s say you have five columns of data: Mon, Tues, Wed, Thurs, Fri. If you fill these columns with data, that data become difficult to read when it’s basically a block of white. You can separate the columns by giving every other column a different background color (Figure A).
Figure A

With and without formatting on a simple block of data.

To make the format changes you see in Figure A, follow these steps:

  1. Enter your data.
  2. Select a section of data to format (a row, a column, or a group of related cells).
  3. Right-click the section.
  4. Select Format Cells.
  5. From the formatting window, format it to your preference.
  6. Click the OK button.

Repeat these steps until you have everything formatted exactly how you want. Keep in mind that too much formatting can be worse than too little formatting.


If you don’t want to deal with formatting your spreadsheet one block of data at a time, you can use autoformatting. Autoformatting uses predefined formatting rules that enable you to create your data and then quickly and easily format the data with a simple tool.

To use the autoformatting tool, your data must meet these minimum requirements (otherwise, you’ll need to revisit your spreadsheet’s design):

  • Three columns
  • Three rows
  • Column headers
  • Row headers

Here’s how to format with autoformatting:

  1. Create your data (remembering the minimum requirements above).
  2. Select all of the cells you want to autoformat.
  3. Go to Format | AutoFormat.
  4. From the new window select the Format style you want to use.
  5. Click the OK button. Your data should instantly be formatted and look much more presentable.

If you look through the selections and don’t find a formatting option that you like, follow these steps to add to the tool:

  1. Create your data.
  2. Use the autoformatting tool and select the formatting that is close to what you want.
  3. Use the cell formatting to modify the format to exactly fit your needs.
  4. Go to Formatting | AutoFormat.
  5. Click the Add button.
  6. Give your new format a unique name.
  7. Click OK.

You have officially saved a custom format to the autoformatting tool. When you go back to the autoformat selections, you should see your newly created format available for use (Figure B).
Figure B

You see two custom formats: BOOKS and BOOKS2. (Click the image to enlarge.)


If the autoformatting tool doesn’t do the trick or you simply don’t want to spend time customizing it, you can use the built-in themes for spreadsheets. Note: Themes cannot be edited in any way; also, you cannot add themes to LibreOffice Calc.

To use a theme, follow these steps:

  1. Go to View | Toolbars | Tools.
  2. From the new toolbar (Figure C) click the Themes button.
  3. From the resulting pop-up, select the theme you want to use.
  4. Click the OK button.

Figure C

The new theme should be immediately applied to your spreadsheet. (Click the image to enlarge.)