Notes from You Suck at Excel by Joel Spolsky. Well, it turned out that I did suck at Excel, although at least I already knew about named ranges and pivot tables.
Enable R1C1 mode, to get numbered columns as well as rows. You can also use relative references, which is how fill-down actually works.
In Libreoffice, you can turn on R1C1 mode with Tools → Options → LibreOffice Calc → Formula → Formula Syntax.
- text concatenation.
C + d
- the fill-down shortcut.
C + `
- switch between values and formulas mode
- toggle between relative and absolute reference types for rows and columns
- cycle between selected cells (useful for filling in column headers)
C + Enter
- set value in all selected cells
C + a
- select current range
C + ;
- today's date
Usually paste values instead of normal paste. Also remember that paste special lets you do some arithmetic and transpose.
Column headers should be centred.
Don't round money amounts using formatting, round properly, so that things add up properly at the end.
Filldown using the right mouse button gives you more options. This doesn't work in Libreoffice?
Name all your ranges. Duh. In LibreOffice this is Sheet → Named Ranges and Expressions → Define.
VLOOKUP. Instead use INDEX and MATCH
INDEX(result_column, MATCH(value, join_column)).
In Excel this is Select Region → Insert → Tables. This is where LibreOffice falls down: you have to combine several different features and some extensions.
Now when you tab off the last row, you'll get a new row. All the formulas will be filled down automatically.
It will put in a named range for the table. You can get to a column using the @ syntax
MyTable[@MyColumn], and a row using the # syntax:
It will fill down formula columns automatically.
You can add a total row, and choose which statistics are used.
It's a good idea to format your input cells and calculated cells differently. There are built-in styles for this.
Apparantly nobody knows about these? But they're really easy.
You can double click on a pivot table cell to drill down into it.