LibreOffice Calc: Cell Styles

version 4.1.6.2

Our next topic is Cell Styles. If you are already familiar with Styles from Writer, think of Cell Styles as the equivalent of Writer’s Paragraph Styles. Just as a single Writer document can have a variety of Paragraph Styles applied to different paragraphs (e.g. Headings, Lists, Paragraphs), a single spreadsheet can have multiple Cell Styles. And the same arguments for using Styles also apply. If you have consistently used Cell Styles in your spreadsheet, you can update the appearance easily just by changing the Style instead of needing to go through the file looking for every cell that needs to be adjusted. And by using Styles you can apply a large number of formatting choices to many cells with just a few mouse clicks. So it really does pay to learn how to use Cell Styles.

Using Styles

Calc comes with a minimum of built-in Styles:

  • Default
  • Heading
  • Heading 1
  • Result
  • Result 2

You can apply any of these Styles by first opening the Styles and Formatting window (if you don’t already have it opened and anchored to the left as I suggested), selecting the cell (or group of cells) to which you wish to apply the Style, then double-clicking on the Style in the Styles and Formatting window. This is much the same as applying a Style in Writer. If you do that in a new spreadsheet with each of the built-in Styles, here is what you would get:

Calc Cell Styles illustrated

Calc Cell Styles illustrated

 

 

 

 

 

 

 

From this it is easy to see that the Default Cell Style looks like what you get when you open a spreadsheet and just type in something. Heading looks like it is larger, Bold, and Italic. Heading 1 is also larger, Bold, and Italic, but also rotated 90 degrees so that it reads sideways. Result and Result 2 look the same, and appear to be bold, italic, but about the same size as Default.

There is another way to apply styles, though, that you have to dig for as it is not visible out-of-the-box, and that is the Apply Style button. To make this visible, you first need to go to a blank spot on the Formatting Toolbar (this is the toolbar that has the font selector), right-click to bring up a context-sensitive menu, and select Visible Buttons–>Apply Style.

Getting the Apply Styles button

Getting the Apply Styles button

 

 

 

 

 

 

This will add a drop-down Style button just like the one that is default in Writer, which will appear on the left side of the Formatting Toolbar:

The Apply Style Drop-Down Button

The Apply Style Drop-Down Button

 

 

 

 

 

To use this, just highlight or select the cells you want to apply the Style to, then select the style from the drop-down. It is handy enough that I keep mine on the toolbar, but it does not show all of the Styles in my experience, so I always keep my Styles and Formatting window open and docked on the left.

Finally, there is the Fill Format mode, which can be very handy when applying a style to widely scattered cells. To use this, have your Styles and Formatting window open, click on the Style you want to apply, then click on the Fill Format button. This is located on the top of the Styles and Formatting window, right next to the New Style from Selection button:

Fill Format button in Calc

Fill Format button in Calc

 

 

 

 

 

 

This will turn your cursor into a graphic of a bucket pouring out the liquid of your imagination. When it looks like this, every cell you click on will have the selected Cell Style applied. When you are done, clicking the Fill Format button again turns it off.

Cell Style Properties

As with Page Styles, there is a Properties window for each Cell Style, and you can set a lot of options here. To get this Properties window for an existing Style, right-click on it in the Styles and Formatting window to select it, and in the pop-up menu select Modify. You will get this window:

Calc Cell Style Properties window

Calc Cell Style Properties window

 

 

 

 

 

 

 

 

This is very similar to the one we saw with Page Styles.

  • Organizer – As with Page Styles, any time you are looking at a built-in Style you cannot change anything on the first tab. But if you are creating a new Style you can name it, link it, etc. As before, I advise against linking styles if you are not an expert on it.
  • Numbers – This sets the Cell Format as a Number, Percent, Currency, Date, Time, Scientific, Fraction, Boolean Value, and Text. We covered all of these options in one of our first tutorials, LibreOffice Calc: Cells so refer back to that if you need a refresher on the subject.
  • Font – This is where you can choose the Font family (e.g. Arial, Helvetica, etc.), the font style (e.g. bold, italic, etc.) and the Font size for your cell.
  • Font effects – You can make your font be a color, underlined, or any of several other effects.
  • Alignment – You can set right-aligned, center, and left aligned here, but with spreadsheet cells you have an additional option, to set the vertical alignment to top, bottom or middle. You can also set the option to automatically wrap text here, which is handy when you are using a spreadsheet for a text-heavy use.
  • Borders – This lets you set borders for the cells. You can set them to one side, or several, or all four, and you can choose a line style, weight, and color.
  • Background – You can put a background color in any cell.
  • Cell protection – If you first apply protection to the current sheet, you can then protect a cell (or cells) this will stop people from changing the contents. You can also hide your formulas, or prevent cells from being printed.

Many of these tabs have other options that are rarely used, and I don’t plan to cover them in this tutorial lest it get even longer.

Creating Styles

To really take advantage of the power of Cell Styles you will want to create some. The built-in Styles are few and rudimentary, and won’t get you far. But when you do this, remember the rule we have discussed so often before, that Styles only persist if they are saved in a Template. If you are working on a spreadsheet and get the idea for a Style you can certainly create it. And it will stay in that spreadsheet forever. But if you want to use it again you need to save it inside of a Template for future use. I plan to do an example of creating a Template with various styles to demonstrate the proper approach as my next tutorial in this series, but for now I just want to run through your alternatives.

Create a new Style in the Styles and Formatting window

With the window open, make sure you have selected the Cell Styles by clicking the very first button. Then right-click in the window, select “New”, and your Cell Style properties window will open. Go through each tab, make your settings as necessary, then click OK to save it.

Create a new Style from a Selection

If you find it easier to set up your Style in the spreadsheet, get a cell to look the way you want it. Add a background color if you like, set the font, font size, font style effects, alignment, and so on. You will want to have some text in the cell to see the full results, but this won’t matter. When it looks the way you like, click the New Style from Selection button, which is right next to the Fill Format button. You should get a window for entering a name.

Creating a Style from Selection

Creating a Style from Selection

 

 

 

 

 

 

 

Just give your new Style a name, and it will appear in your Styles and Formatting Window. Note that it will not pick up any of the actual text you used, just the settings.

Modify an existing Style

The last approach is to modify an existing Style. There are two ways to do this:

  • Open your Styles and Formatting window, click on Cell Styles, then right-click on an existing style, and select Modify. This will open the Cell Style Properties window. Go through each tab, and make the necessary changes to create the Style you need.
  • Select a cell that is using the Style you want modify. Make your changes in the cell itself until you like how it looks. Then click the Update Style button, which is on the other side of the New Style from Selection button. Again, it will not pick up the text, just the settings.

Copying a Style

Sometimes you want to copy a Style from one cell to another, even to one that is in a different Spreadsheet file altogether. To do this, select the cell, and copy it to the clipboard. Go to the cell where you want this Style to be copied, select it, and go to Edit–>Paste Special. The selection you want to make here is Formats, and nothing else should be selected. This will copy all of the Style definition without affecting the contents. You need to make sure the destination cell is selected and has a black line around it. This will also add the Style to your Styles and Formatting window. This is a good way to copy a Style you have created to a Template in case you forgot to do this in a Template. Just open the Template and do this type of copy operation, then save the Template.

Next up: Creating a Template with Styles!

Listen to the audio version of this post on Hacker Public Radio!

CC BY-SA 4.0 LibreOffice Calc: Cell Styles by Kevin O'Brien is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.