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 <Ctrl t>
.
Structured references are relative to the table:
- Cell in same row:
Arrow key left/right
- Column:
<Ctrl spacebar>
- Row:
Alt spacebar
Easy row shortcuts:
- Add:
<Ctrl-Shift-+>
- Remove:
<Ctrl-minus>
- Swap: Cut, then
<Ctrl-Shift-+>
- Move: select row with
Alt spacebar
or 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:
INDEX
OFFSET
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
Current date: Ctrl ;
Sum all cells above: Alt =
Open cell properties: Ctrl 1
Formatting: Ctrl Shift (number)
. Examples:
- Integer:
`
- Decimal:
1
- Time:
2
- Date:
3
- Surround with border:
7
7. Alternatives to nested IF
s
OR
AND
XOR
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?
https://www.mrexcel.com/forum/excel-questions/725361-sql-connections-tables-within-same-excel-spreadsheet.html#post3582850
https://stackoverflow.com/a/40924619
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 *IF
or *IFS
functions, or using matrix algebra?
Dynamic named ranges: Would an Excel Table be a better solution? (Not always!)
Next steps
- Train your speed with ModelOff practice questions
- Try Mesh, a spreadsheet that writes code. Works with local data! No IT permissions required!