Learn how to efficiently copy formula results into Excel cells without duplicating the formulas themselves. Master the Paste Special Values feature to streamline data editing and formatting tasks.
Key Insights
- The Paste Special Values feature in Excel allows users to copy only the results of a formula, without duplicating the formula itself, effectively preserving desired formatting and values.
- Using the PROPER function, users can quickly capitalize text in Excel cells, and then apply autofill to automatically capitalize multiple cells at once.
- Excel's shortcut keys, such as Ctrl + Alt + V followed by V, enable quick access to Paste Special, optimizing workflow when editing and organizing data.
This lesson is a preview from our Excel Bootcamp Online (includes software) and Excel Expert Certification Online (includes software & exam). Enroll in a course for detailed lessons, live instructor support, and project-based training.
Paste special values. 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. 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. Could 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 capitalize Diana Stone. So I can use the function proper. I'll type in equal, PROPER, 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 add 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 key. 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 Ctrl+C. I'll head over to Diana Stone, and then I'll press Ctrl+V. Now, what just happened here is that I copied the formula and pasted the formula. What does 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 east. So that's not what I want to have happen.
I'll press Ctrl+Z to undo. I just did that to show you what would happen if you did not use paste special. Now I'll use the keyboard shortcut Ctrl+Alt+V, 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 edit to the original values.