Paste Special Formats

What the heck is “paste special” anyway? Well you probably already know about regular paste. Pasting is when you place something on your spreadsheet that you’ve previously copied or cut from somewhere else. If you wanted to make a duplicate of something, you would copy and paste (CTRL+C and CTRL+V). If you wanted to move something, you would cut and paste (CTRL+X and CTRL+V).

With regular paste, you are pasting the selection as is. Meaning if the selection you copied (or cut) was a value, you’re pasting the value. If it was a formula, you’re pasting the formula. And if it was formatted to look to a certain way, you’re pasting the formatting too.

But what if you don’t want to paste your selection exactly as is, and you want to paste only certain aspects of it? That’s where paste special comes in. With paste special, you can specify which parts you actually want to paste. You can choose to paste formulas, values, formats, comments, validation, and much more. You probably don’t need to know about all of these paste special options. 90% of the time when I use paste special, I paste either formats, formulas, or values.

In my series of Paste Special posts, I’ll walk you through the top three types of paste special. I’ll use the same data set for all three, the simple budget example below.

Download my simple budget example to follow along.

Let’s get started with the first one, paste special formats.


Paste Special Formats

Paste special formats allows you to paste only the formatting of something, without actually pasting what’s inside of it. In other words, you are pasting the colors, fonts, and borders of a cell, but you are not pasting the value or formula inside the cell.

In our budget example, let’s add a DIFFERENCE column in column F, next to the ACTUAL in column E. I want my difference column to look the same as the other columns, with the green headers and yellow rows, so I’m going to use paste special formats.

Just for illustrative purposes, I’m going to show you what the difference between paste regular and paste special formats looks like. If I copied column E, and pasted (regular) into column F, this is what it would look like. You would have an exact replica of the ACTUAL column.

Paste Regular:



This is not what we want. We want our new column to look the same as the ACTUAL column, but we don’t want the content inside. With paste special formats, our example would look like this. Which is what we want.

Paste Special Formats:



To create our new column using paste special formats, first select the ACTUAL column by putting your cursor on the letter E.

Then right-click, and click Copy (or press CTRL+C).

Then put your cursor on the letter F since that is where we will want to paste. Right-click again, scroll over to where it says Paste Special, and click Paste Special. Alternatively, you can press ALT, H, V, S.

When the Paste Special menu pops up, select formats and hit OK.

Your budget summary now looks like this.

See how the formatting was pasted, but none of the values were? That’s what paste special formats does.

Let’s fix the border on the left side of the column, and type DIFFERENCE in our column header.

Our budget is now ready for the next part. In my next post, I’ll show you how to use paste special formulas.