This short video shows how to copy and paste the values from an Excel formula into the same column using 2 different methods. This video supplements our Simple Methods to Copy Excel Formula Values tutorial and spreadsheet file. It is suggested you read that article and download the example worksheet.
If you don’t see the video control bar, move your mouse toward the bottom and it should appear.
This is a short demo to show how to override column C.
Currently, it has the names of presidents that are derived from a formula using concatenate. Instead, what I want to do is show the president’s name. I don’t want to show the formula.
The way that we’re going to achieve this is by first clicking in cell C1, and pressing Ctrl + Shift and my down arrow key. This keystroke combination highlights the entire column.
I’ll go to the Home menu and select copy. You’ll now see I’ve got a ring going around my column.
Next, I’m going to click Paste and select the Paste values icon.
I’ll click outside the column in cell D1 and then hit my Escape key. Notice when I click a president’s name it’s the name of the president. It no longer has that concatenate formula.
That’s how you can overwrite your columns that contain formulas and just leave the values behind.
In this second solution we’re going to get the same results, but what I’m going to be doing is dragging the column on top of itself to paste the special values.
Again, I’m going to click cell C1 and I’ll press Ctrl +-Shift and my down arrow key to highlight the column.
I’ll hit Copy, and now I have my selection and the border going around it.
Next. I want to move my mouse to the outer column edge, and when my mouse cursor changes to a four-headed arrow, I want to hold down my right mouse button.
Then I’m going to drag it to the right on top of column D, and then drag it back.
When I let go I’ve got a new menu and I can choose Copy Here as Values Only.
As before, if I click off you’ll see I no longer have the concatenate formula.
I find this method easier, but it may take you a little bit the first couple of times. Once you get the hang of it, it goes faster.