Quick Guide to LibreOffice Calc
This is actually a list of tips & tricks I learned while using LibreOffice
Import CSV file into a new file
- Create a new Calc file
- File > Open > myfile.csv
- 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:
- Write the formula in the first cell: =HYPERLINK(C1)
- Ctrl+Shift+End to select the column to the last cell
- Ctrl+D to copy the formula in the remaining cells.
- Ctrl+Click to open the link in the default browser.
Insert rows through SHIFT+Ins
Paste data by moving existing cells down
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:
- Select the rows
- Edit > Cut
- Click where you want the data to move to
- Edit > Paste Special > Paste Special… : Check "Paste All"
and "Transpose" > OK.
To keep the first row(s) in place while scrolling up/down:
- Click on a cell below the row you want to freeze
- View > Freeze Rows and Columns
To empty cells:
- Click on cell
- Click and release SHIFT
- Move mouse
Without overwriting existing data
- Select cells with mouse + ALT
- Move, and hit ALT
SHIFT+INS to insert new rows
- Tools > Customize
- Tab "Keyboard"
- Select desired shortcut key
- In category "Insert", select command "Insert rows"
- Click on "Modify"
- OK to close
Working with an SQLite database
Connector for LibreOffice? Use application meant for SQLite?