Excel for Developers

Excel is a deep product if you're willing to get a bit technical. Some tips for developers on using Excel.

Relative vs. absolute references – When you copy and paste a reference, Excel will translate that reference to the new coordinates (relative reference). To keep the reference the same when it's moved, use absolute references. Denoted by a $ before the coordinate, e.g., $A$4 is an absolute reference to the value in A4, $A4 keeps the column fixed while A$4 keeps the row fixed. CMD+T or F4 as a shortcut to toggle between relative/absolute/mixed.

Paste values/Paste special – Fixes your #REF! issues; paste dereferenced values instead of references. There are various types of paste, such as paste formatting, paste values, etc.

Index Match instead of VLOOKUP – VLOOKUP is slow and requires you to reference a column by index that can't be named. Index match is quicker because it only works on the relevant arrays and can be used with named references.

Named references – You can name cells as variables and reference them in formulas. This makes it much easier to reason about complicated formulas.

Named Tables – Like references, you can define tables in Excel and name them. Tables have the added benefit that they correctly handle aggregation functions when new rows are added – summations, averages, etc. ranges are automatically updated.

Toggle calculation mode – You can turn calculation mode on or off for workbooks that are especially computation-heavy.

R1C1 Notation – Use number-based indexing instead of alpha-numeric indexing. This changes column identifiers to R1C1 instead of alpha-numeric A1.

Macros, VBA, and extensions – Of course, there's always the escape hatch of writing real code that gets called from Excel. You can record macros, write code in VBA, or even write code in various .NET languages. There's even a C API that you can call directly.