Applies to openSUSE Leap 15.0

11 LibreOffice Calc

Calc is the LibreOffice spreadsheet module. Spreadsheets consist of several sheets, containing cells which can be filled with elements like text, numbers, or formulas. A formula can manipulate data from other cells to generate a value for the cell in which it is inserted. Calc also allows you to define ranges, filter and sort data and creates charts from data to present it graphically. Using pivot tables, you can combine, analyze or compare larger amounts of data.

This chapter can only introduce some very basic Calc functionality. For more information and for complete instructions, see the LibreOffice application help and the sources listed in Section 9.11, “For More Information”.

Note
Note: VBA Macros

Calc can process many VBA macros in Excel documents. However, support for VBA macros is not complete. When opening an Excel spreadsheet that makes heavy use of macros, you might discover that some do not work.

11.1 Creating a New Document

There are two ways to create a new Calc document:

  • From Scratch.  To create a new empty document, click File › New › Spreadsheet.

  • From a Template.  To use a template, click File › New › Templates and set the Filter to Spreadsheets. Choose a template from the list and proceed with Open. Note that by default, LibreOffice does not ship with predefined templates for Calc.

Access the individual sheets by clicking their respective tabs at the bottom of the window.

Enter data in the cells as desired. To adjust the appearance, either use the Formatting toolbar or side bar panel, or use the Format menu—or define styles as described in Section 11.2, “Using Formatting and Styles in Calc”. Use the File menu or the relevant buttons in the toolbar to print and save your document.

11.2 Using Formatting and Styles in Calc

Calc comes with a few built-in cell and page styles to improve the appearance of your spreadsheets and reports. Although these built-in styles are adequate for many uses, you will probably find it useful to create styles for your own frequently used formatting preferences.

Procedure 11.1: Creating a Style
  1. Click Format › Styles › Styles and Formatting or press F11.

  2. At the top of the panel Styles and Formatting, click either Cell Styles (a green cell) or Page Styles (a document).

  3. Right-click anywhere in the list of styles in the panel Styles and Formatting. Then click New.

  4. Specify a name for the style and use the various tabs to set the desired formatting options.

  5. When you are done configuring the style, click OK.

Procedure 11.2: Modifying a Style
  1. Click Format › Styles › Styles and Formatting.

  2. At the top of the panel Styles and Formatting, click either Cell Styles (a green cell) or Page Styles (a document).

  3. Right-click the name of the style you want to change, then click Modify.

  4. Change the desired formatting options.

  5. When you are done configuring the style, click OK.

To apply a style to specific cells, select the cells you want to format. Then double-click the style you want to apply in the Styles and Formatting window.

11.3 Working With Sheets

Sheets are a good method to organize your calculations. For example, if you have a business, accounting might be much clearer if you create a sheet for each month.

To insert a new sheet after the last sheet, click the button + next to the sheet tabs.

To insert one or more new sheets into your spreadsheet from a file or at a specific position at once, do the following:

Procedure 11.3: Inserting New Sheets
  1. Right-click a sheet tab and select Insert Sheet. A dialog opens.

  2. Decide whether the new sheet should be positioned before or after the selected sheet.

  3. To create a new sheet, make sure the New Sheet radio button is activated. Enter the number of sheets and the sheet name. Skip the rest of this step.

    Alternatively, to import a sheet from another file, do the following:

    1. Select From file and click Browse.

    2. Select the file name and confirm with OK. All the sheet names are now displayed in the list.

    3. Select the sheet names you want to import by holding the Shift key and clicking them.

  4. To add the sheet or sheets, confirm with OK.

To rename a sheet, right-click the tab of the sheet and select Rename Sheet. Alternatively, you can also double-click the sheet tab.

To delete one or multiple sheets, do the following: Select the sheet you want to delete. To select more than one sheet, hold down Shift while making the selection. Then right-click the tab of the sheet, choose Delete Sheet and confirm with Yes.

11.4 Conditional Formatting

Conditional formatting is a useful feature to highlight certain values in your spreadsheet. For example, define a condition and if the condition is true, a style is applied to each cell that fulfills this condition.

Note
Note: Enable AutoCalculate

Before you apply conditional formatting, choose Tools › Cell Contents › AutoCalculate. You should see a check mark in front of AutoCalculate.

Procedure 11.4: Using Conditional Formatting
  1. Define a style first. This style is applied to each cell when your condition is true. Use Format › Styles and Formatting or press F11. For more information, see Procedure 11.1, “Creating a Style”. Confirm with OK.

  2. Select the cell range where you want to apply your condition.

  3. Select Format › Conditional Formatting › Condition from the menu. A dialog opens.

  4. You now see a template for a new condition. Conditions can operate in multiple modes:

    Cell value is

    The condition tests if a cell matches a certain value. Next to the first drop-down box, select an operator such as equal to, less than, or greater than.

    Formula is

    The condition tests if a certain formula returns true.

    Date is

    The condition tests if a certain date value is reached.

    All Cells

    This mode allows creating data visualizations that depend on the value of a cell, similarly to Cell value is. However, with All Cells, you can use one condition to apply an entire range of styles.

    The types of styles that can be used are color scales (cell background color), data bars (bars with changing width in the cell) and icon sets (an icon in the cell).

    For example, a color scale allows assigning 0 a black background and 100 a green background. All values in between are calculated automatically. For example, 50 receives a dark green background.

  5. For this example, keep the default: Cell value is.

  6. Select an operator and the value of the cell you want to test for.

  7. Choose the style you want to apply when this condition is true or click New Style to define a new appearance.

  8. If you need additional conditions, click Add. Then repeat the previous steps.

  9. Confirm with OK. Now the style of your cells has changed.

11.5 Grouping and Ungrouping Cells

Grouping a cell range allows hiding parts of a spreadsheet. This makes spreadsheets more readable, as you can hide all the parts you are not currently interested in. It is possible to group rows or columns and nest groups in other groups.

To group a range, proceed as follows:

Procedure 11.5: Grouping a Selected Cell Range
  1. Select a cell range in your spreadsheet.

  2. Select Data › Group and Outline › Group. A dialog appears.

  3. Decide if you want to group your selected range by rows or by columns. Confirm with OK.

After grouping selected cells, a line indicating the grouped cell range appears in the upper-left margin. Fold or unfold the cell range with the + and icons. The numbers at the top left of the margins display the depth of your groups and can be clicked too.

To ungroup a cell range, click into a cell which belongs to a group and select Data › Group and Outline › Ungroup. The line in the margin disappears. The innermost group is always deleted first.

11.6 Freezing Rows or Columns as Headers

If you have a spreadsheet with lots of data, scrolling usually makes the header disappear. LibreOffice can lock rows or columns or both, so they remain fixed as you scroll around.

To freeze a single row or a single column, proceed as follows:

Procedure 11.6: Freezing a Single Row or Column
  1. To create a frozen area before a row, click the header of the row (1, 2, 3, ...).

    Alternatively, to create a frozen area above a column, click the header of the column (A, B, C, ...).

  2. Select View › Freeze Rows and Columns. A dark line appears, indicating the frozen area.

It is also possible to freeze both rows and columns:

Procedure 11.7: Freezing Row and Column
  1. Click into the cell to the right of the column and below the row you want frozen. For example, if your header occupies the space from A1 to B3, click cell C4.

  2. Select View › Freeze Rows and Columns. A dark line appears, indicating which area is frozen.

To unfreeze, select View › Freeze Rows and Columns. The check mark before the menu item disappears.

Print this page