From the course: Excel 2016: Tips and Tricks

Convert formulas to values with a simple drag

From the course: Excel 2016: Tips and Tricks

Start my 1-month free trial

Convert formulas to values with a simple drag

“

- [Voiceover] Sometimes you write formulas and functions to clean up data, and sometimes there reaches a point when you no longer need the formulas. You simply want to keep the results of the formulas. On this worksheet, called Salaries, we've got some data in Column A. It isn't looking so good. There are two things we might want to do to clean it up. I'm going to right-click Column B, first of all, and insert a new column, and what I'd like to be able to do is, first of all, not have all the letters be uppercase, and there's a function for that, it's called Proper. There's another issue in here, though. We've got too many spaces, leading spaces in Row 3 here, possibly some trailing spaces on some of these, too many inner spaces here in Row 8, between the names, and this is a big list, so we want to clean them all up. There are two functions we might use here. First of all, Proper is a function that simply capitalizes only letters that follow punctuation, or space, or begin the actual entry, so, if we simply use this function by itself, I'll press Control + Enter, the data looks like that, and that looks just great, but the space issue, which is not a concern here, is going to be on the next one, so let's change this and use the function, Trim. We can put it either in front of Proper, or in front of A2, doesn't make any difference. We do have to add additional parentheses, both left and right, and I'll complete this with Control + Enter. I don't want the active cell to move downward. I'll double-click and copy this down the column. That's the way I want the data to look, no unwanted spaces, no unusual use of uppercase, so it looks great, but these are formulas, and they depend upon the data in Column A. If we delete Column A, these formulas have nothing to refer to. If we erase the data in Column A, same problem, so what do we do here? Here are the formulas that we want to keep, but we don't want to keep the formulas themselves. We want to keep the results. Now, there are multiple ways to do this, and the standard way is to copy this data, and, after copying it, go over here, and then go to Paste Special and paste just the values, but it's much, much simpler to highlight the data, and this could be a huge list. With the right mouse button, I'm going to either drag from the top, the left or the right, makes no difference. I'm going to drag from the top here, over on top of the old data. Remember, I'm dragging with the right mouse button. As I let go of the right mouse button, Copy Here as Values Only. Now, I always wanted to say, "Paste results of formulas here." Maybe that's not as clear as you might want it to be even, but certainly clearer than this. Copy Here as Values Only? What do they mean? We're going to take the results that we see in Column B, paste them into Column A, and what do we see here, for example, in Cell A2? Exactly what we're seeing, Baker comma space Mark, nothing else, that's it. We don't need this data any more. We don't even need Column B. Right-click and delete it. Now, another scenario, doing something similar, could be over here, we've got formulas here. It could be we've reached a time where we're going to adapt to the new salaries here. These are formulas. See the formula bar? See it as we double-click here? I'm going to highlight all these, and, in effect, say, "Let's just keep these. "We don't need them as formulas any more." Now, what if we wanted to say something like, "Well, could we somehow copy these to themselves?" In other words, can we turn Cell C2 into 68,660 exactly? In other words, maybe we don't want to copy these and wipe out the old salaries just yet. We might want to hold on to them, or it could be a situation where, maybe, it's early 2016, and we're saying, "Let's label this, 2015 Salary, "and label this, 2016 Salary," so we've got these formulas. Maybe we don't want to get rid of the other ones over there, so we highlight the data, and we can use that same feature, dragging with the right mouse button, but, this time, we're gonna drag right back on top of itself, so we can either drag right left, or up down, or left right, makes no difference, but we've got to be holding down the right mouse button as we point to the edge of the data, so here I'm holding down the right mouse button, dragging rightward, and then leftward, letting go of the right mouse button, Copy Here as Values Only, so what's in this cell right here? As I double-click, exactly 68,660, that's it. We copy these to themselves. Let me undo that, Control + Z, do it again, slightly differently, just to show that we can do it, The data's highlighted. I'm pointing to the top edge. Holding down the right mouse button, I'm going to drag up down. Up, down, let go of the right mouse button, Copy Here as Values Only. There it is, and what do we see right here? Just the data. We threw away the formulas, kept the results. I use this feature a lot, particularly when I'm cleaning up data. It's invaluable, and it's really ease to use. Simple right drag, drag it elsewhere, right back on top of itself, or into another column, and then we paste as Values Only.

Contents