Simple Methods to Copy Excel Formula Values

I recently watched an Excel product review and noticed the author copy Excel values from a formula into another column. This was fine, but then he deleted the original column with the formula. Sound familiar? This tutorial shows you two faster and easier ways to copy the values and not the formulas. (Includes video demo and sample spreadsheet.)

Why Copy Formula Values

There are many reasons for copying formula values. Sometimes you don’t want others to know how the result was derived. Other times, you want to make it simpler and faster for users. This is particularly true if your spreadsheet calls external resources or recalculations.

In this tutorial, I’m copying the values from a text formula that concatenates first and last names in the example below. So, instead of pasting the full name into a new column such as D, I’ll overwrite the column C formula.

Column C with contents from concatenate formula.Pin
Full Name derived from CONCATENATE formula

Method 1 – Use Copy and Paste Special Values

This method uses toolbar buttons you already know. The difference is most people paste the formulas into a new column. Unfortunately, they don’t realize they can paste the results into the same column.

  1. As a precaution, I like to press Ctrl+Alt+F9. This forces the spreadsheet to recalculate all cells in case auto-recalculate was turned off.
  2. Click the top cell of the column you wish to copy. In this tutorial, it would be C1.
  3. Press Ctrl+Shift+ to select the column.
  4. Click the Copy button from the Home tab on the ribbon.
Column C highlighted for copying.Pin
  1. Now, click the bottom part of the Paste button on the toolbar to reveal more options.
  2. In the Paste Values section, click the first icon.
Opening the Paste Special menu.Pin
Make sure a dotted lines is around your selection

If you move through column C you should see that the formulas have been replaced with the text names.

Excel Paste Values Shortcut

If you prefer keystrokes over a mouse, here’s the shortcut sequence.

Alt + e + s + v

Method 2 – Copy the Column Values with Mouse Drag

This method is a little trickier and takes a bit of practice, but I think you’ll agree that it’s the easier solution.

  1. Follow Steps 1-3 in Method 1.
  2. Move your mouse cursor to the cell’s far-right border until it changes to a four-headed arrow.
Mouse over edge of Column C with 4 headed arrow.Pin
4 Headed arrow cursor
  1. Press your right mouse button and drag Column C to the right. (Don’t worry if it looks like you’ve shifted cells up or down.)
Dragging Column C to Column D and then back.Pin
Notice Green outline on Column D
  1. With the right mouse button still pressed, drag the column back onto itself and let go of the mouse button. A new menu should appear.
  2. From the context menu, choose Copy Here as Values Only.
Copy Here as Values Only menu item selected.Pin

You should now see the text values for the presidents in Column C. The formulas are gone.

In this tutorial, we’ve shared two different ways to copy and paste values in Excel without copying the formulas. The first method is to use copy and paste with special values. The second method showed how to do the same task but utilizing “copy here as values only.” Feel free to check out the video and practice worksheet below.

Additional Resources