Join Dennis Taylor for an in-depth discussion in this video Convert formulas to values with a simple drag, part of Excel 2013 Tips and Tricks.
- View Offline
There are times in Excel when you've got formulas that convert the data into an appearance, or into a result that you want to keep, but the question comes up, sometimes, do you really need the formulas, or do you want to keep the formulas? We've got two situations in this worksheet called 'Salaries'. In column A, we've got entries that are all uppercase, and also some of the entries here have leading spaces, or multiple embedded spaces, and we want to clean that up, so in column B, and I'll double click on cell B2 to reveal the formula, we're using a function called, 'Proper', and also a function called, 'Trim', that essentially cleans up the data.
Appropriate letters uppercased, trim gets rid of the extra spaces. So, ultimately what we really want to do here is to keep the results as we see them in column B. The wording that we use for this might seem a little strange. What we really want to do is to actually copy the values. Now, sometimes in Excel, the word 'value' is used to refer to numbers, but in this case we're talking about, ultimately, non-formulas, and the way we do this at first, probably seems a little bit strange, what we're going to be doing is highlighting the data, and although there is a multiple step process here that we could be using, and that would involve 'Copy', and 'Paste special', and so on, we're going to do it with a simple drag, but using the 'right' mouse button.
And, what we'd like to be able to do is take these results, not the formulas, and put them in column A. So, with the 'right' mouse button, we can point to either the left edge, the top edge, or the right edge. I'll point to the top in this case. Hold down the 'right' mouse button, and drag this into column A. As I let go of the 'right' mouse button, here's the shortcut menu, and what we want to do is 'Copy here as values only'. Again, in a different way, we could've said here, "Copy the results of the formulas here," cause that's ultimately what that means, and what are we left with here, simply, Baker 'comma' 'space' Mark, nothing else.
That's not a formula, it's the result that we actually saw over in B2, but no formulas are left here. Now, I want to undo this, because sometimes what you want to do is take these results and copy them to themselves, and what we need to do in this case, is again, use the 'right' mouse button, but temporarily drag the data elsewhere, and then right back on top of itself. So, in one fluid motion, we can drag this either into column C, then right back on top of itself, or up into B1, then right back down again.
So, I'll just go up and down in this case. I'll point to the top edge, hold down the 'right' mouse button, drag up, then down, let go of the 'right' mouse button, 'Copy here as values only'. So, we've thrown away the formulas here. We'd probably get rid of column A, eventually. And, so what are we left with here? Baker 'comma' 'space' Mark, and all the others are looking in a similar fashion, no formulas are left. [Similarly] in column D, but here we've got formulas. And, let's say, eventually, we don't need to keep both salaries, so we've got the same general idea here, possibly, we want to simply move this into column C, and get rid of column D.
So let's simply, highlight the data. Remember these are all formulas here that calculate a new salary. We'll simply, with the 'right' mouse button, drag this data into column C, release the 'right' mouse button, 'Copy here as values only', and there we are, and we don't need column D anymore. And so, this idea of converting formulas into values is extremely fast and efficient using this 'right' drag method.
- Converting formulas to values with a simple drag
- Entering today's date or time instantly
- Accessing Ribbon commands from the keyboard
- Creating split screens fast
- Navigating quickly
- Entering data more efficiently
- Performing calculations without formulas
- Applying formatting with keyboard shortcuts
- Using database techniques to work with Excel data