How do you copy values in Excel after formula?

How to Copyand Pastein Excel:Formulas, Values, and FormattingWritten by co-founder Kasper Langmann, Microsoft Office Specialist.At the surface, copying and pasting in Excel seems s

How do you copy values in Excel after formula?

How to Copyand Pastein Excel:
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 theres so much more to it

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

  • Formulas
  • Values
  • Formatting

When youve got the hang of it (which youll 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 lets get right into it!

Table of Contents

1: The basics of copying and pasting

2: Copy and paste formulas

3: Copy and paste values

4: Copy and paste formatting

5: Copy and paste conditional formatting

6: Wrapping things up

Follow along by downloading our sample file right below!cloud-download

BONUS:Download theCopy and PasteExercise 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!

Kasper Langmann, Co-founder of Spreadsheeto

We wont 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 youve 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 4elements:

  • 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 dont have to retype the entire formula all over again.

I mean, wouldnt we prefer to be able to just make a few mouse clicks?

Rather than retyping something like this:

Without the ability to paste formulas, Excels innovative functions would reach the point of diminishing returns very quickly.

Kasper Langmann, Co-founder of Spreadsheeto

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 lets 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.

Kasper Langmann, Co-founder of Spreadsheeto

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.

Kasper Langmann, Co-founder of Spreadsheeto

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 shortcutCtrl + C.

This shortcut copies the selection to the clipboard and saves time by eliminating repetitive mouse clicks and movement.

Kasper Langmann, Co-founder of Spreadsheeto

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 tothe 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.

Kasper Langmann, Co-founder of Spreadsheeto

These options are:

Paste  Values  Formulas  Transpose  Formatting  Paste Link

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

Lets 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.
The keyboard shortcut Ctrl + Alt + V.

Kasper Langmann, Co-founder of Spreadsheeto

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

But lets 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.

Kasper Langmann, Co-founder of Spreadsheeto

But not to worry!

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

So lets 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 lets 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 BUTwith 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.

Kasper Langmann, Co-founder of Spreadsheeto

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 weve 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 lets 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.

There should be no formula visible in the Formula Bar. Only the calculated value now should appear.

Kasper Langmann, Co-founder of Spreadsheeto

How to Copy and Paste Formatting

Before we wrap up our discussion about copy and paste in Excel 2016, lets 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 isnt 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.

Kasper Langmann, Co-founder of Spreadsheeto

We will take a look at a couple of different situations in which we will copy cell format but lets 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 thats 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 youve seen, there are many more methods and ways to copy and paste other variations of data.

The tools youve learned about copy and paste gives you a huge amount of flexibility and functionality. The knowledge gained from this tutorialshould be a springboard to developing further skills regarding those needs!

Kasper Langmann, Co-founder of Spreadsheeto

Video liên quan