Selecting and Pasting Special in Excel

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!