As an example, if you took the small table below and wanted to copy and paste the calculated values on row 4, you’d need to deal with the fact that the formula will change – offsetting the D and the 2 reference to wherever you paste it (eg if you pasted the copy into E4, the formula would be =E2-E3) – normally, a powerful and useful function, but a potential nuisance.
An older UI for selecting the options is available if you click on the Paste Special… command at the bottom of the pop-out, or by pressing CTRL+ALT+V to pop out the Special dialog. One of the more particularly useful features of Paste Special in Excel is the Transpose option – if you select and Copy a row of data then Paste / Transpose it, the data is rearranged as a column (and vice versa). Great news in many cases, but if you want to paste cells and keep the original formulae (without resorting to using absolute references formula references using $ in the formula itself, eg setting =$D$2-$D$3), there are no default options to transpose the orientation of the cells but not change the formulae. One trick if you ever find yourself in this position, is to bulk change the formulas so they won’t get modified when you paste the cells; do a Find & Replace to change = to something like #=. After pasting and transposing, reverse the process to restore the formula
It’s an edge case but could save you lots of time if you need to do it. For most of us, getting to grips with shortcut keys in Excel would make things more productive – as well as numerous combos of CTRL-something, there are simple keys (like pressing F4, which repeats the very last command … so if you’ve just coloured a cell yellow, move the cursor to another cell and hit F4 to make that one yellow too… if you’re doing very repetitive things, this can save so much time). There are also more complex sequences; press the ALT key in Excel (and other Office apps, too) to see the key combos that A little bit of legacy/history – press ALT-E then S to jump to the Paste Special menu – why E? Even though it’s long gone, really old versions of Excel had an Edit menu, and the commands on any menu – in any application – that have an underscore under a letter (like Paste Special) are highlighting the key you can press to jump to that command. So ALT E / S used to be the combo to get Paste Special circa Excel 2003, and it still exists today. |