**How to Copy and Paste in Excel:**

Formulas, Values, and Formatting

Formulas, Values, and Formatting

*Written by co-founder Kasper Langmann, Microsoft Office Specialist.*

At the surface, copying and pasting in Excel seems simple.

You copy some data from a cell to another.

**But there’s so much more to it 🙂**

I’m going to show you the most powerful (and easiest) ways of copying and pasting:

- Formulas
- Values
- Formatting

When you’ve got the hang of it (which you’ll have by the end of this tutorial) your daily work is going to become so much more relaxed.

**So grab a cup of coffee and let’s get right into it!**

Follow along by downloading our sample file right below!

**BONUS: ****Download** the** Copy and Paste Exercise File ** to go along with this post.

## The Basics

Microsoft Excel offers a ton of options when pasting a copied element to the clipboard.

Anything from values to formulas to formatting.

Due to the fact that Excel is not just some word processor, there is a great need for these many options. There is even a way to take a copied row region and transpose it to a column when pasting, and vice versa!

We won’t spend time on those types of options, but be informed nonetheless that these types of options are available.

For this tutorial, we will focus on the most important and most used types of copy and paste operations in Microsoft Excel.

This guide is written for Excel 2016 for Windows, but no matter what version of Excel you’ve got you should be able to follow along 🙂

**The first thing to do?**

Get familiar with the ‘Clipboard’ section of the Home tab on the ribbon.

In this Clipboard section there are 4 elements:

**‘Paste’**which is symbolized by the clipboard and sheet**‘Cut’**which is symbolized by the scissors**‘Copy’**which is symbolized by the two sheets**‘Format Painter’**which is symbolized by the paintbrush

This section of the Home tab is on the far left side of the ribbon just before the Font section.

If we click on the small arrow at the lower left corner of the Clipboard section, it expands to show the most recently copied elements still on the clipboard.

If we click on an element on the clipboard it will highlight that element and there will be a drop-down arrow on the far right with two options to either ‘paste’ or ‘delete’.

*Note that there is also an option to either ‘Paste All’ or ‘Clear All’ at the top of the window.*

## How to Copy and Paste Formulas

There may be situations in which we might prefer to copy and paste a formula from one cell to another so that we don’t have to retype the entire formula all over again.

**I mean, wouldn’t we prefer to be able to just make a few mouse clicks?**

Rather than retyping something like this:

‘=IFERROR(SUM(INDIRECT(“‘”&”Sheet1″&”‘!”&ADDRESS

(MATCH($A4,’Sheet1’!$A:$A,0),3)):INDIRECT(“‘”&”Sheet1″&”‘!”

&ADDRESS(MATCH($A4,’Sheet1’!$A:$A,0)

,COUNTIF(‘Sheet1′!$1:$1,”<“&TODAY())+2))),””)’

Without the ability to paste formulas, Excel’s innovative functions would reach the point of diminishing returns very quickly.With that in mind, let’s get started with how to copy and paste formulas!

We will use a very simplified scenario to make sure we get the concepts we will cover.

See the table below.

**Here we have a table with two columns that contain numeric values and then a third column containing a simple formula. **

Obviously, we want to focus on the on the ‘Formula’ column and how to copy and paste any of these cells while maintaining the integrity of the formulas.

So let’s say we add a new row of values in the ‘Value 1′ and ‘Value 2’ columns and we want to carry down the formula in the ‘Formula’ column.

Our formula is simply an ‘IF’ comparison of the values in each of the value columns that will return whatever values we have chosen for the TRUE or FALSE result.

Clearly, if we have added new data to subsequent rows we need the formula to refer to cell references according to the row and not the same row we are copying from.

Excel allows us to paste formulas and when we do so, the formula automatically will change row references according to the row we paste it to.

The exception to this would be if we had locked the row number of any of the cell references in our formula by preceding it with a ‘$’.

For example, ‘A$1’ would remain ‘A1’ rather than taking on the row number of the cell that we paste the formula into.

As for copying our cell, it is the same method no matter how we want to paste it.

**However, there are actually a couple of methods to copy!**

*First*, we can use the ‘Copy’ selection in the ‘Clipboard’ section of the ‘Home’ tab in the ribbon. This will work just fine.

But for the sake of more efficient workflow, the *second* option is to use the keyboard shortcut ‘Ctrl’ + ‘C’.

It’s simply done by pressing and holding the ‘Ctrl’ key and then pressing the ‘C’ key.This shortcut copies the selection to the clipboard and saves time by eliminating repetitive mouse clicks and movement.

There is actually a *third* option for copying that lies somewhere between the other two methods by right-clicking the mouse when the selection is highlighted.

**A drop down will appear that includes the option to copy.**

This same drop down includes the ‘Paste Options:’ menu that we will be referring to shortly.

Now we can highlight cell ‘D9’ as our selection and press ‘Ctrl’ + ‘C’ to copy to our clipboard.

Then we go to the cell we want to paste it to (‘D10’), and right click.

Again, we will see the same drop down in the previous figure but because our selection is currently copied to the clipboard, ‘Paste Options:’ contains several options.

These options are:

*‘Paste’ – ‘Values’ – ‘Formulas’ – ‘Transpose’ – ‘Formatting’ – ‘Paste Link’*

These are all shown in the list by their respective icons.

**Let’s take a look at what each of these means:**

**‘Paste’**: This simply pastes everything about the copied cell.**‘Values’**: This pastes the value of the copied cell and nothing else.**‘Formulas’**: This pastes the formula from the copied cell.**‘Transpose’**: This pastes the selection in a transposed position which simply means that if the copied selection was a range of cells on a row, that range would be pasted as a column and likewise if the copied selection was a range of cells in a column.**‘Formatting’**: This simply pastes the formatting of the copied cell and no other contents.**‘Paste Link’**: This simply pastes the cell reference(s) of a copied selection and the absolute cell reference of a copied cell.

This is very convenient but we can also click on ‘Paste Special’ beneath these six icons to open the full gamut of possibilities available in the ‘Paste Special’ dialog box.The keyboard shortcut ‘Ctrl’ + ‘Alt’ + ‘V’.

Here we can see the same options in addition to several others.

**But let’s get back to the task of pasting our formula. **

When we select our destination cell (‘D10’), we simply select ‘Formulas’ for our paste method and the cell now contains the formula we copied from our source cell with the cell references in the formula adapted to the new row.

**Awesome, right? **

But what if we had several rows of new data we added in the values column and we wanted to add formulas to the ‘Formula’ column for all those new rows of data?

Even with the additional efficiency the copy and paste offers us, as the need to expand the formulas scales larger with more data, doing so one cell at a time becomes less efficient.

But not to worry!

**The same paste operation works just the same if we select a range of more than one cell. **

So let’s look at this scenario…

We have added five new rows of data to our value columns.

So now we want to make sure we add formulas to the ‘Formula’ column for each of these additional rows of data.

All we need to do is select cell ‘D9’ and press our keyboard shortcut to copy *(remember what that is 😉 ?)*.

Then we need to select the range of cells from ‘D10’ to ‘D14’.

Now let’s pull up the ‘Paste Special’ dialog box by one of the methods we discussed previously and select the ‘Formulas’ radio button.

Now all of the cells in the selected range should have formulas just like the one in the copied cell BUT with all the cell references changed to match the row numbers.

We can validate this by viewing the formula in the Formula Bar above the worksheet while the cell is selected.

In Excel 2016, we can also use the ‘FORMULATEXT’ function to show the formula in the cell.

The syntax is simple.

The ‘FORMULATEXT’ function only takes a cell reference and in this case, it would be a cell in the ‘Formula’ column like ‘D9’.

So our formula in ‘E9’ would be ‘=FORMULATEXT(D9)’.

## How to Copy and Paste Values

There may be cases in which we run calculations using formulas *(just like we’ve been doing in our current scenario)*.

However, it may be desirable to remove the actual formulas from our ‘Formula’ range and just leave the calculated values.

**So let’s turn to our current example again where we left off.**

What we need to do in order to convert the entire ‘Formula’ column to values rather than the formula we will select the entire range ‘D3:D14’ and copy.

The only difference in pasting values as opposed to pasting formulas like we did before is the selection we make in the ‘Paste Special’ dialog box.

Once we click on ‘OK’ after selecting the ‘Values’ radio button in the ‘Paste’ section of the ‘Paste Special’ dialog box, the contents of those cells in our selected range now become the calculated values rather than the formulas themselves.

We can validate this by reviewing the content of the ‘Formula Bar’ when any cell in the pasted range is selected.There should be no formula visible in the ‘Formula Bar’. Only the calculated value now should appear.

## How to Copy and Paste Formatting

Before we wrap up our discussion about copy and paste in Excel 2016, let’s take a look at one last thing.

**As our example stands so far after copying and pasting formulas and then changing those calculations over to values, the new data that we have been focused on still isn’t the same format of the previous data range.**

Just like copying and pasting formulas and values provides us with and easier and more efficient method than doing things manually, there is a method (actually a couple) that will allow us to copy cell format and paste without changing the actual content of the cell.

We will take a look at a couple of different situations in which we will copy cell format but let’s first do that for our current formatting.

**We need to select the range ‘B9:D9’ and copy.**

Since this is the last row that is formatted the way we want the entire range of new data formatted this is the correct way to copy cell format for our task at hand.

**The next step is to highlight the range ‘B10:D14’ and right click and select ‘Paste Special’ to bring up its dialog box again. **

Then we will select the ‘Formats’ radio button and click ‘OK’.

Now we should have a complete table with the new data all formatted the same.

## How to Copy and Paste Conditional Formatting

Another type of formatting we have not covered so far is ‘Conditional Formatting’.

**This is a type of formatting that Excel offers us that can be found in the ‘Styles’ section of the ‘Home’ tab (found between the ‘Number’ and ‘Cells’ sections).**

Without making this an article about ‘Conditional Formatting’ *(instead, read more about conditional formatting here)* we will simply use a simple type that highlights cells within a range that are duplicates.

So we selected our first column and applied the ‘Conditional Formatting’ rule that highlights every value that is duplicated in the range with red font and pink background.

We can now copy this formatting by using the same copy cell format method as before.

We simply highlight the first formatted cell in the range which in this case is ‘B3’.

*Then* select the range that we want to apply the new format to (‘C3:C14’) and follow the procedure to paste format as we did before.

However, this time, we need to select ‘Merge Conditional Formatting’ icon from the ‘Paste Special’ drop-down list that appears when we right click into our destination cell.

‘Merge Conditional Formatting’ is the last icon in the ‘Paste’ section.

**Now both value columns have the same conditional formatting rules applied.**

This is definitely a great method to copy cell format and paste to the desired selection.

**However, Microsoft Excel actually offers a better tool to perform the same function called the ‘Format Painter’. **

This tool can also be found in the ‘Clipboard’ area of the ‘Home’ tab and is symbolized by the paint brush. It is just below the ‘Copy’ tool.

The process is very similar but instead of copying the cell or range of cells, we simply make our selection and click on the ‘Format Painter’ paintbrush.

This essentially performs the same function as copying does.

**Then we select the cell or range of cells to apply the formatting to and that’s it.**

Clearly, this is just a bit more of an efficient way to copy cell format and apply to another cell or range of cells since it involves fewer steps.

**Important note**

One thing to keep in mind when using copy and paste for both formula and format is that **there is no need to copy an entire range of cells to be pasted**.

We can simply select and copy a single cell with the formula or format we need and then select the range we want to paste either to and then paste formula or format, whichever would apply.

**However, this does not hold true when using copy and paste for values.**

Since this operation actually pastes the literal value that was copied from the source range to the destination, if we were to select a single cell to copy from it will be the only value pasted to the destination range.

## Wrapping up copy and paste

We have covered three of the more common copy and paste needs *(formulas, values, and formatting)* within Excel along with a more advanced way of copying and pasting conditional formatting.

However, as you’ve seen, there are *many* more methods and ways to copy and paste other variations of data.

The tools you’ve learned about copy and paste gives you ahugeamount of flexibility and functionality. The knowledge gained from this tutorial should be a springboard to developing further skills regarding those needs!