You Suck at Excel

  • Excel uses R1C1 mode internally. Columns are numbered, not lettered, and relative references are much more clearly differentiated from absolute references.
  • Use a $ to lock a relative reference on a given axis, like $A1or $R$5.
  • Paste values pastes without formatting or formulas.
  • & for string concatenation.
  • Ctrl+D to fill downward.
  • Select a range before entering data, so the Enter key moves you exactly where you want to go.
  • ⌘A selects outwards until it hits white space, so it’s a good way to select tables.
  • Ctrl+backtick switches between formulas being hidden/visible.
  • ' to insert text (tell Excel not to interpret the following as a number, for example).
  • Name cells to avoid intractable formulas.
  • vlookup is slow! Use index(match()) instead.
  • Use tables instead of loose groupings of rows.
  • Goal seek allows you to set a value by changing something else.
  • Use pivot tables to answer arbitrary questions or perform summarization on a table of data.

Lots of useful nuggets in here, but there’s a weird adversarial, edgy vibe going on. Maybe some sort of inside joke about Google Sheets?

Edit