14. Paste Special

 
Subtitles Enabled

Sign up for a free trial to access more free content.

Free trial

Overview

Excel has multiple Paste options which you'll need to know how to use. This lesson covers Paste Formats, Paste Formulas and Paste Values.

Summary

  1. Paste Special (00:04)

    In Excel, we can use the simple Paste command Ctrl + V. However, Excel also offers a range of Paste Special commands as well. To view the options, you can press Alt, E, S to open the Paste Special window.

  2. Paste Formats (01:05)

    Paste Formats pastes the cell formatting from one cell to another. The shortcut for Paste Formats is Alt, E, S, T. In this case, the delivery date column is in the short form. We use Paste Formats to copy the more readable format from the Order Date column into the Delivery Date column.

  3. Paste Formulas (01:48)

    Paste Formulas pastes the formula used in one cell into another cell. The Paste Formulas shortcut is Alt, E, S, F. Here we use it to copy the delivery date formula for the first row and paste it for the remaining rows.

    This does not copy formats. To copy formulas and formats, one option is to use Ctrl + V. Another option is to use auto fill by clicking the bottom right corner of the cell to be copied.

  4. Paste Values (02:26)

    Pasting values pastes the value of one cell to another, ignoring formulas and formats. Its shortcut is Alt, E, S, V. Paste values is often used where the formula in a column or row is not consistent, and this inconsistency could create errors later on.

    Here, we use Paste Values to replace a column of date formulas with a column of date values.

Transcript

In the previous lesson, we learned how to group and hide cells in an Excel sheet.

In this lesson, we'll learn how to use the various Paste Special options available in Excel.

In most text-based programs, such as PowerPoint and Microsoft Word, we have simple cut, copy and paste commands.

We can use the shortcuts Control + C to copy, Control + V to paste and Control + X to cut. However, in Excel, things are more complicated. When you copy or cut cells, there are multiple paste options. You can paste formulas, paste values, paste formats and even paste commands. Let's start by selecting a cell and copying with Control + C and I'll press Alt + E + S to open Paste Special.

Here you can see all of the paste options available in Excel. The three you'll use most often are paste formulas with Alt + E + S + F, paste values with Alt + E + S + V and paste formats with Alt + E + S + T.

Understanding the different pace options will make your life a lot easier in Excel. Let's now explore these options quickly.

I'm going to escape from Paste Special and I'm going to write a simple formula for delivery date for each order, which happens three days after an order is made. To calculate the delivery date, I'll write equals to create a formula and add three to the order date.

This tells me that the delivery will occur on the 3rd of April, 2012 and it shows this in the short form. However, I prefer the order date format, so I'll copy with Control + C and I'll paste with Alt + E + S + T.

And that gives me my delivery date in a much more readable format. To calculate the delivery date for the remaining orders, I'll copy with Control + C, select all of the cells and paste with Alt + E + S + F, which will paste formulas.

This correctly calculates the delivery date for me but does not put it in the right format. To paste both the formula and the formats we can use Control + V or we can use autofill by simply clicking on the bottom right-hand corner.

And this will automatically calculate our formula and apply the format for all of our remaining cells in that particular column.

In addition to pasting formats and pasting formulas, it's also important to know how to paste values. Pasting values is normally used to break the dependency of a cell on its existing formula.

Let's say that some of our delivery dates take four days rather than three.

I don't want to manually change some values in a column and then leave others as a formula because this has the potential to create errors further down the line. Instead, I'd like to make all the delivery date values a constant and I can do this by pasting values. Let's start by changing the delivery date on four identified orders. So I'll press F2 to jump back into the formula and replace the three with a four and I'll do this for the three remaining orders off-camera.

Now we need to convert the delivery dates into constants because we currently have inconsistent formulas within our column. I'll select the data with Control + Shift + Down arrow and copy with Control + C. In the adjacent column, I'll paste values with Alt + E + S + V.

As you can see, we have some strange formatting applied in that new column, so I'll also paste formats with Alt + E + S + T.

In this new column, you can see that we have constants and not formulas, which is exactly what I want.

And I'll simply cut and paste this new column on top of our old delivery dates. So Control + Shift + Down arrow, Control + X to cut, Control + V to paste.

And now we have constants in our delivery date column.

Anytime you have a column which has more than one formula calculating data, try to use paste values to convert this column into a series of constants because consistent data within a column is much less likely to cause errors in the future.

Excel Excel for Business Analytics Learning Plan
Data Manipulation and Formatting