How to use Paste Special in Excel

Paste special is a very good tool to have for a variety of reasons. Let’s cover the most common use cases:

Paste Values

download

Here we have a table with days worked at Company X, sales per employee, and a calculated field with Sales per day. Column D is calculated with an easy formula of dividing Column C by Column B. Easy, right? But what if our table has thousands of rows with formulas far more complicated?

By leaving in formulas that have already yielded their desired result, we risk slowing down Excel. Also, if you want to paste these values into another sheet or workbook, Excel will still try to calculate “=C2/B2” on the new workbook, when you only want to paste the result. Paste Values to the rescue.

Steps:

  • Select Column D.
  • Press “control + c” to copy

paste-special2

  • Select the top cell you’re going to replace (D2 in our case)

paste-special3

  • Control + Alt + V to bring up Paste Special

paste-special4

  • Press “V” on your keyboard to select the Values radio button.
  • Click OK

paste-special5

As you can see, the cells no longer have a formula in them, but only the resulting Value of that formula.

Paste Special – Transpose

Transpose is used when you want to flip data on its axis.

paste-special6

Let’s say you wanted to take the headers from row 1 above and list them in a new column.

paste-special7

In the picture above, I did the following:

  • Copy all of row 1
  • Click cell I1
  • Paste special
  • Transpose

Everything is now flipped vertically.

But that’s not all. Transpose can also flip multiple rows into columns and vice versa.

paste-special8

In the image above I’ve selected 5 rows, copied them, and transposed it on H1. The use cases for Transpose are often very specific and it may be a feature that you do not use as often as some of the others, but when you really need it, it’s a fantastic tool.

Text to Columns – Excel

Text to Columns is a very fun feature of Excel. This feature will help you break apart cells that have information that you’d rather be in more than one cell. For example, if you had “John Smith” in one cell, but you were building a list of first and last names in your company, you’d want “John” in one cell and “Smith” in another. Text to Columns is the answer.

text to columns 1

Here we have a list of first and last names that we’d like to separate. As they sit now, they’re both in the same cell.

text to columns 2

We added a new blank column by right clicking on column C and clicking “insert.”

text to columns 3

Next, we simply have to select all of the text we’d like to separate, go to our “data” ribbon and click “Text to Columns.”

Leave “delimited” chosen and click “Next.”

text to columns 4

Check the “Space” delimiter box and notice that the preview cuts the names just where we’d like. Press Finish.

download (4)

Now they’re in separate columns! In the picture above, I’ve renamed columns B and C to “First Name” and “Last Name” to complete the task.