In this section, we're going to take a look at the paste special option that allows us to copy the result of a formula over into other cells, such as a column of information or a row of information.
Paste Special Values
In this example, I'll pretend that I've been given the assignment to properly capitalize these names and there's a time crunch. My boss says to me at 4:55, I'm sorry to do this to you, but we have a list of names that need to be properly capitalized. Can you please stay and work on this? It's for a very important client.
So before we actually get to paste special, I'm going to show you how I can quickly capitalize these names. I'm going to go to the left of the very first cell and I want to properly capitalized Diana Stone so I can use the function proper. I'll type in equal P R O P and then right at this point I can press tab when I press tab.
Excel will automatically complete the name of that function and even ad in the open parentheses for me. Now I need to select Diana Stone. Diana is over on the right. I'll use the right arrow that selects the cell. Then I'll simply press, enter and now one down.
Many more to go. I'm going to select Diana Stone. Go to the autofill handle, which is located at the bottom right hand corner and simply double click. So far, so good. A lot of work has been done already. So all the names are now properly capitalized. I need to get those names over to column B.
So I'll press controls C, I'll head over to Diana Stone and then I'll press control V. Now, what just happened here is that I copied the formula and pasted the formula. What is the formula do? The formula capitalizes the cell to the right. Therefore, it's capitalizing east and the cell to the left of that cell is also capitalizing the text to the right, which is now E. So that's not what I want to have happen. I'll press control Zee to undo.
I just did that to show you what would happen if you did not use paste special. So now I'll use the keyboard shortcut, control all TV and then I'll choose V for values. Then when I click, OK, I have now pasted the result of the formula, but not the formula itself.
So the column on the left just helped me to get to where I needed to with the result. And now I can delete that formula, so I'll just select that column and delete it.
So that's how you could use paste special values to copy the result of a formula and paste it as text to the column where you want to make an added to the original values.
Become a Master of Excel
Become an Excel wiz in just 3 days with our Excel Bootcamp in NYC or live online. We also offer Excel corporate training options across the US.