Ten Excel tips for smarties
1. Quick paste special
The standard shortcut to enter the paste special menu is
<Alt h v s>.
It can be reduced to three keystrokes with a legacy shortcut:
<Alt e s>.
< TODO UPDATE WITH CTRL-ALT-V >
Once in the paste special menu, select an option using
<Alt (underlined character)>.
2. Force input as string
Put a single quotation mark (
') at the start of the input.
3. Excel Tables – basics
Create a table with
Structured references are relative to the table:
- Cell in same row:
Arrow key left/right
Easy row shortcuts:
- Swap: Cut, then
- Move: select row with
Alt spacebaror click on left, then drag up or down
4. Excel Tables – calculated columns
Calculated columns extend to all cells in a column of an Excel Table.
Create a calculated column by entering a formula in a single cell in that column.
Even if you delete all rows in the table, the calculated column formula survives behind the scenes and comes back when you add new rows.
5. Use 'range' operator with formulas
You probably know that cell references separated with a colon will represent all cells within the box specified by those two cells. For example,
A1:B3 covers all cells within a box with A1 at the top-left and B3 at the bottom right.
But you can also chain output of formulas returning a range, such as:
This is really handy for some calculated column formulas in Excel tables (can’t use location-based cell refs if all rows are deleted).
6. Shortcuts for common actions
Sum all cells above:
Open cell properties:
Ctrl Shift (number). Examples:
- Surround with border:
7. Alternatives to nested
IFS(in Excel 2016)
Lookup tables can also be useful.
8. Pivot tables
Pivot tables are basically an easy-to-use interface for SQL queries. Know them well.
One handy use is generating a dynamic list of unique items.
// TODO show how to add extra fields / whatever?
9. Power Query / Get and Transform
Collect data from a wide variety of sources, including internet APIs. Join tables easily. Query them with pivot tables or PowerPivot.
Power Query's language, M, can also be used in a pinch to generate data of arbitrary length.
10. Last resorts
Array formulas: Try to not use them. They are hard to understand and error-prone to edit after initial creation. Can you express your query using
*IFS functions, or using matrix algebra?
Dynamic named ranges: Would an Excel Table be a better solution? (Not always!)
- Train your speed with ModelOff practice questions
- Try Mesh, a spreadsheet that writes code. Works with local data! No IT permissions required!