Paste Special Values
This is the final post of my three-part Paste Special Series. Last week, I talked about two types of paste special – paste special formats and paste special formulas. In this post, I’ll talk about paste special values. As I previously mentioned, I use one of these three almost every time I use paste special.
Paste Special Values
When you’re pasting special value, you are pasting the value of something you previously copied or cut. If you had previously copied a hard-keyed value, such as “108,” you are pasting “108.” If you had previously copied a formula, such as “=100+8,” you would be pasting “108.” In either case, you’re pasting just the value of the cell you copied, and not the formatting.
If you read my two previous posts (or even if you haven’t), you can probably see many practical applications for paste special formats and paste special formulas. But why on Earth would someone need to use paste special value?
Well, there are times you’ll want to grab only the results from a set of formulas and paste them into another section. But what I use paste special values for most of the time is for something I like to call “valuing-out.”
Valuing-out is simply copying a formula and pasting special value right over that formula. I actually do this quite often because it makes your spreadsheet much more stable and smaller.
When you value-out, you get rid of unstable and complex formulas and you’re left with only the results. Formulas can be very unstable especially if they involve links to other worksheets or workbooks. Any little change on a linked document could potentially mess up your formulas. Additionally, if you are working with complex formulas, your file can be very large. Large files take a while to load and are slow to update. By valuing-out wherever you can, your file can be much faster and easier to work with.
Let me show you an example of valuing out. We’ll use the same simple budget data from the previous posts.
Download the simple budget to follow along.
This is what the budget looked like where we left off from the previous post.
All of the numbers in column F right now are formulas, but we’ll value them out using paste special values.
First select the area with the formulas.
Copy it (by right-clicking or pressing CTRL+C), and keep your cursor where it is.
Go to the Paste Special menu (by right-clicking or pressing ALT, H, V, S) and select Values.
You’ll see that now all of these cells are values instead of formulas.
Granted, these were not complex formulas, and we didn’t gain much by valuing them out. But trust me, next time you are working with crazy formulas, you will be thankful for knowing how to paste special value and get rid of those formulas!
Well that’s it for my Paste Special Series. Paste special can save you a ton of time when you need to quickly paste formatting, formulas, or values over a large selection. You can become super fast at Excel by using paste special regularly, especially when you do it with the shortcut keys.