Sign in or start a free trial to avail of this feature.
14. Paste Special
Excel has multiple Paste options which you'll need to know how to use. This lesson covers Paste Formats, Paste Formulas and Paste Values.
- Unlike MS Word & MS PowerPoint, Excel has many Paste options for you to use
- To access these options, you need to access the Paste Special dialog box
- The 3 most common Paste options are Paste formats, Paste formulas and Paste values
ALT + E , S , F: Paste formulas
ALT + E , S , T: Paste formats
ALT + E , S , V: Paste values
CTRL + X: Cut selected item(s)
CTRL + C: Copy selected item(s)
CTRL + V: Paste selected item(s)
In most text based programs, such as Power Point and Microsoft Word, we have simple cut, copy and paste commands.
We can use the shortcuts Ctrl + C to copy, Ctrl + V to paste, and Ctrl + 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 comments.
Let's start by selecting a cell and copying with Ctrl + C.
I'll now press Alt + E S to open Paste Special.
Here you can see all of the paste options available in Excel.
The three you will use most often are paste formulas with Alt + D S F, paste values with Alt + D S V, and paste formats with Alt + E S T.
Understanding the different paste 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 a delivery date for each order, which happens three days after an order is made.
To calculate the delivery date, I'll write = to create a formula, and add 3 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 Ctrl + C, and then 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 Ctrl + 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 Ctrl + V, or we can use auto fill by simply clicking in the bottom right hand corner.
And this will automatically calculate our formula, and apply the format for all of the 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 the column, and then leave the others as a formula, because this is the potential to create errors down the road.
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 the four identified orders.
So I'll press F2 to jump back into the formula, and replace the 3 with the 4.
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 Ctrl + Shift + Down Arrow, and copy with Ctrl + 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 Ctrl + Shift + Down Arrow, Ctrl + X to cut, Ctrl + V to paste.
And now we have constants in our delivery day column.
Anytime you have a column which is more than one formula, calculating data, try and 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.