# 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!