Ever needed to copy Excel values, but not the formulas? We’ve got a simple solution that can make this task a breeze. Imagine keeping your data clean and understandable without revealing the underlying formulas. This tutorial shows how to copy the values and not the formulas in two different ways. (Includes video demo and sample spreadsheet.)
Why Copy Formula Values in Excel
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 for 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.
Method 1 – Use Copy and Paste 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.
- As a precaution, I like to press Ctrl+Alt+F9. This forces the spreadsheet to recalculate all cells if auto-recalculate is turned off.
- Click the top cell of the column you wish to copy. In this tutorial, it would be C1.
- Press Ctrl+Shift+ ↓ to select the column.
- Click the Copy button from the Home tab on the ribbon.
- Now, click the bottom part of the Paste button on the toolbar to reveal more options.
- In the Paste Values section, click the first icon overlaid with 123 in the lower right.
Alternatively, you could right-click in cell D1 and choose the second icon in the Paste Options list. It’s the one labeled Values (V).
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 – Use Mouse Drag to Copy Values
This method is a little trickier and takes some practice, but I think you’ll agree it’s the more straightforward solution to copy and paste without formulas.
- Follow Steps 1-3 in Method 1.
- Move your mouse cursor to the cell’s far-right border until it changes to a four-headed arrow.
- 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.)
- 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.
- From the context menu, choose Copy Here as Values Only.
You should now see the text values for the presidents in Column C. The formulas are gone.
Use Paste Special If Formula Values Use Conditional Formatting
The above solutions work fine if you don’t have any formatting in the column with the formula. The values will paste fine. However, if you had a conditional formatting rule that turned cells green if the president’s name contained James, the formatting would not copy. The same is true if you had other formatting.
To include the cell formatting,
- Copy the column data. Make sure the dashed line is around the cell values.
- Right-click in cell D1.
- From the drop-down menu, select Paste Special…
- From the fly-out menu, select Values & Number Formatting (A).
Verifying Cells Values are Without Formulas
Before deleting your column with formulas, you should verify you don’t have any in the new column. While you could hover over each cell, there is a built-in feature that helps.
- Click the Formulas tab on the ribbon.
- Go to the Formula Auditing group.
- Click Show Formulas.
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.