Quick Guide to LibreOffice Calc

Introduction

This is actually a list of tips & tricks I learned while using LibreOffice Calc.

Import CSV file into a new file

  1. Create a new Calc file
  2. File > Open > myfile.csv
  3. By default, all columns are "Standard", ie. numbers. To turn some into eg. Text, (SHIFT-)click them in the Text Import dialog.

Apply same formula to all rows below

… while updating the row #?

Use $ to reference absolute row/colum numbers, eg. =C2/$C$284, when pasted in the rows below, will turn into =C3/C284, =C4/C284 etc.

By default, cell numbers are relative, ie. LO updates them as you paste/move them around, while the $ sign makes them absolue, ie. they won't change.

How to convert all links to hyperlinks

Let's say the first row contains "http://www.acme.com" in the column:

  1. Write the formula in the first cell: =HYPERLINK(C1)
  2. Shift+Enter
  3. Ctrl+Shift+End to select the column to the last cell
  4. Ctrl+D to copy the formula in the remaining cells.
  5. Ctrl+Click to open the link in the default browser.

Insert rows through SHIFT+Ins

Paste data by moving existing cells down

Aggregating cells

Rotating rows and columns

In case you want to turn rows into columns, and columns into rows because there are too many columns, making it hard to view:

  1. Select the rows
  2. Edit > Cut
  3. Click where you want the data to move to
  4. Edit > Paste Special > Paste Special… : Check "Paste All" and "Transpose" > OK.

Freezing rows

To keep the first row(s) in place while scrolling up/down:

  1. Click on a cell below the row you want to freeze
  2. View > Freeze Rows and Columns

Moving Cells

To empty cells:

  1. Click on cell
  2. Click and release SHIFT
  3. Move mouse

Without overwriting existing data

  1. Select cells with mouse + ALT
  2. Move, and hit ALT

SHIFT+INS to insert new rows

  1. Tools > Customize
  2. Tab "Keyboard"
  3. Select desired shortcut key
  4. In category "Insert", select command "Insert rows"
  5. Click on "Modify"
  6. OK to close

Working with an SQLite database

Connector for LibreOffice? Use application meant for SQLite?

Resources