10 LibreOffice Calc
Calc is the LibreOffice spreadsheet and data plotting 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 the data or creates charts from the data to present it graphically. By using pivot tables, you can combine, analyze or compare larger amounts of data.
As with the entire LibreOffice suite, Calc can be used across a variety of platforms. It provides several exchange formats (including export to PDF documents), and can also read and save files in Microsoft Excel format. Its interoperability is constantly ameliorated.
This chapter can only introduce some very basic Calc functionality. For more information and for complete instructions, look at the LibreOffice help or at the sources listed in Section 8.10, “For More Information”.
Note: VBA Macros
Calc can process many VBA macros in Excel documents. However, support for VBA macros is not yet complete. When opening an Excel spreadsheet that makes heavy use of macros, you might discover that some do not work.
10.1 Creating a New Document #
There are two ways to create a new Calc document:
From Scratch. To create a document from scratch, click › › and a new empty Calc document is created.
Templates. To use a template, click › › and open . You can see a list of Spreadsheet templates. Select the one that fits your needs and your new document is created based on the style of your selected template.
Access the individual sheets by clicking the respective tabs at the bottom of the window.
Enter data in the cells as desired. To adjust the appearance, either use the toolbar or the menu—or define styles as described in Section 10.2, “Using Formatting and Styles in Calc”. Use the menu or the relevant buttons in the toolbar to print and save your document.
10.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 10.1: Creating a Style #
Click › .
In the window, click either the or the icon from the top of the window.
Right-click anywhere in the list of styles in the window. Then click .
Specify a name for your style and use the various tabs to set the desired formatting options.
Click .
Procedure 10.2: Modifying a Style #
Click › .
In the window, click either the or the icon.
Right-click the name of the style you want to change, then click .
Change the desired formatting options.
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 window.
10.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 in your spreadsheet, do the following:
Procedure 10.3: Inserting New Sheets #
Select › from the main menu. A dialog opens.
Decide whether the new sheet should be positioned before or after the selected sheet.
If you want to create a new sheet, make sure the radio button is activated. Enter the number of sheets and the sheet name. Skip the next step.
If you want to import a sheet from another file, do the following otherwise skip this step:
Select and click .
Select the file name and confirm with . All the sheet names are now displayed in the list.
Select the sheet names you want to import by holding the Shift key.
Confirm with to import the sheet names you selected.
To rename a sheet, right-click a sheet in the sheet tab and select . To rename, you can also double-click the sheet tab.
To delete the current sheet, select › › and confirm with . It is possible to delete more than one sheet by holding the Shift key and selecting the sheets you want to delete in the sheet tab. Right-click and choose and the same dialog appears. Confirm with .
10.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: Enable AutoCalculate
Before you apply conditional formatting, choose › › . You should see a check mark in front of .
Procedure 10.4: Using Conditional Formatting #
Define a style first. This style is applied to each cell when your condition is true. Use › or press F11. For more information, see Procedure 10.1, “Creating a Style”. Confirm with .
Select the cell range where you want to apply your condition.
Select › › from the main menu. A dialog opens.
You now see a template for a new condition. Conditions can operate in multiple modes:
The condition tests if a cell matches a certain value. Beside the first pull-down menu, you can select an operator such as , , or .
The condition tests if a certain formula returns true.
The condition tests if a certain date value is reached.
This mode allows creating data visualizations that, similarly to , depend on the value of a cell. However, the difference here is that you can use one condition to apply an entire range of styles.
For this example, keep the default: .
Select an operator and the value of the cell you want to test for.
Choose the style you want to apply when this condition is true or click to define a new appearance.
If you need additional conditions, click . Then repeat the previous steps.
Confirm with . Now the style of your cells has changed.
10.5 Grouping and Ungrouping Cells #
Grouping a cell range helps to fold your spreadsheet into parts. This makes your 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 10.5: Grouping a Selected Cell Range #
Select a cell range in your spreadsheet.
Select › › . A dialog appears.
Decide if you want to group your selected range as row or as column. Confirm with .
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 › › . The line in the margin disappears. The innermost group is always deleted first.
10.6 Freezing Rows or Columns as Headers #
If you have a spreadsheet with lots of data, scrolling makes your header usually 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 10.6: Freezing a Single Row or Column #
To create a frozen area before a row, click the header of the row (
1,2,3, ...).To create a frozen area above a column, click the header of the column (
A,B,C, ...).Select › . A dark line appears, indicating which area is frozen.
It is also possible to freeze both rows and columns:
Procedure 10.7: Freezing Row and Column #
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.
Select › . A dark line appears, indicating which area is frozen.
To unfreeze, select › . The check mark in the menu then disappears.