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:

Easy row shortcuts:

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

Current date: Ctrl ;

Sum all cells above: Alt =

Open cell properties: Ctrl 1

Formatting: Ctrl Shift (number). Examples:

7. Alternatives to nested IFs

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