Thu 26 Apr 2018

Excel

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
F4
toggle between relative and absolute reference types for rows and columns
Enter
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.

Don't use VLOOKUP. Instead use INDEX and MATCH INDEX(result_column, MATCH(value, join_column)).

Tables

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: MyTable[#MyRow].

It will fill down formula columns automatically.

You can add a total row, and choose which statistics are used.

Formatting

It's a good idea to format your input cells and calculated cells differently. There are built-in styles for this.

Pivot Tables

Apparantly nobody knows about these? But they're really easy.

You can double click on a pivot table cell to drill down into it.