Time for some “special” excel tips on selecting and pasting!
Excel has quite a few options for selecting data as well as pasting data. These
can really come in handy depending on what you’re trying to select and paste.
And if you’re lazy like me, you like working very hard to find the quickest and
easiest way to do things!
Select Special!
To get to the select special options, hit CTRL + G to bring
up the “Go To” box. Then click the “Special…” button.
The Excel super-user way to bring up this window is to hit
ALT, H, F, D, S (note you do not have to hold ALT down while pressing the other
keys). The Special window looks like this:
There are many useful things you can select with this
window, but my favorites are Visible Cells, Formulas, and Blanks.
Select Visible
Cells (shortcut: ALT, H, F, D, S, Y, Enter)
If you have filters on or rows hidden on purpose, and only
want the visible cells to be selected for copying and pasting, use this
selection method. Let’s say we have a table with some animal types and names:
If for some reason you decided to hide a few rows, and
wanted to copy and paste just what was showing into another sheet, excel tries
to include everything rather than just select what’s visible. (note this is
different than when dealing with filtered items). In order to only select what
is showing, you can use the Select Special - Visible Cells to select and then
copy those cells.
Here’s how it looks when just the visible cells are
selected:
And here’s what it looks like when it’s pasted:
Select Formulas
(ALT, H, F, D, S, F, Enter)
This select special will select only the cells in a region
that contain formulas. It will not select blank cells or cells with just words
or numbers in them. To use this one, highlight all areas that you want to find
formulas within, then use this to select just the cells that contain formulas.
This is useful for figuring out which cells are from formulas versus which are
hardcoded and do not rely on values from other cells. (Side tip: in order to
view all formulas in a spreadsheet, use ALT+~ to reveal all the formulas. Hit
that again to toggle back to regular view).
Select Blanks (ALT,
H, F, D, S, K, Enter)
Use this selection method to only select blank cells in a
range. This could be used if you want to paste a certain value or formula
within only the blank cells in an area, You can copy one cell, use this to
select all blanks, and then paste. You could also use this to highlight blank
cells in a certain range (although conditional formatting also works for that).
Paste Special!
To get to the Paste Special menu, make sure you copy
(CTRL+C) something first, then once it’s in the clipboard, you can pull up the
special menu by
To paste regular, hit CTRL+V. However, to get to the paste
special options, hit CTRL+ALT+V. Two other ways to get there are to hit ALT, H,
V, S, or you could click the dropdown arrow underneath the Paste icon.
Here’s what the paste special popup looks like:
There are many useful things you can paste with this window,
but my favorites are Values, Formulas, and Transpose.
Paste Values (ALT,
H, V, V)
This is useful for turning formulas into the value from the
formula. This can be used if you want to take the result of a formula and paste
that result as a hard value in place of the formula, or somewhere else in the
same workbook or another workbook. For example, if you have a spreadsheet that
has lots of links or formulas that point to other locations, and you want to
share your file with someone else and not have all the formulas break, use this
to paste the values.
Paste Formulas
(ALT, H, V, F)
This paste special is useful when you want to paste just
formulas from one cell or range of cells, but not paste any of the formatting
from the original formulas.
Paste Transposed
(ALT, H, V, T – or – ALT, H, V, S to bring up Paste Special and combine with
other paste modes)
If you are copying cells within a row and want to transpose
them in a column, or vice versa, this is the way to do that. You can just paste
transposed like a normal paste, or combine this with other paste specials.
Example – here I’ve used the Paste Transposed to transpose
the table from Columns into Rows. Note how the formatting copied over
(underlines below Animal and Name). If you want to paste the transposition but
not include formatting, that’s where you would want to combine Transpose with
Value.
Combining Paste Transposed with Paste Value – I’ve selected
the Values radio button, and clicked the Transpose box (quick key stroke way is
ALT, H, V, S, V, E, Enter)
The circled range below represents the results of pasting the original table
transposed and as values.
There are many other fun Select and Paste specials, so play
around with them and see which ones are your favorite!
Enjoy!
