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 column with the formula. Sound familiar? This tutorial shows you two faster and easier ways to copy the values and not the formulas. (See the Resource section for a video demo and sample spreadsheet.)
Article Contents
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. 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 Special Values
This method uses toolbar buttons you already know. The difference is most people paste the formulas into a new column. 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 in case auto-recalculate was turned off.
- Click the top cell of the column you wish to copy. In this tutorial, it would be C1.
- Press CTRL+SHIFT+ the down arrow key to select the column.
- Click the Copy button from the Home tab on the ribbon.
- Click the bottom part of the Paste button on the toolbar to reveal more options.
- In the Paste Values section, click the first button.
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.
- 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.
Additional Resources
You Might Also Like These Excel Tutorials
Disclaimer: Images from Amazon Product Advertising API. I may receive an affiliate commission on these products if you buy. Updated: 2021-04-17