From the course: Excel 2016: Advanced Formulas and Functions

Converting formulas to values with a drag

From the course: Excel 2016: Advanced Formulas and Functions

Start my 1-month free trial

Converting formulas to values with a drag

“

- On this worksheet called "Convert Formulas to Values," we've got some data in column A that has some problems, mostly of a space nature. Some of these have trailing spaces; they're hard to see, we don't see them necessarily unless we double-click on the end of a cell, like here in row five. We've got too many spaces there in row three between the last name and the first name. And we've got some leading spaces as well. We'd like to clean those up. And in column B we've got some formulas using some text functions. PROPER simply makes the first letters of names capitalized. TRIM takes care of trailing spaces, leading spaces, it even takes care of multiple consecutive inner spaces like we see in row three here. So things are looking pretty good, and we'd like to keep the data the way we see it in column B. In other words, get rid of the column A data, but if we get rid of the data in column A, what happens to these formulas? We'll have nothing over here. So what we want to do in this case is turn these into their results. There is a way to do this, a standard way, but also a great shortcut. After clicking column B, I can press control C for copy, then go to the home tab, paste special, we can also get here by one of the icons here, values right here, and that effectively throws away the formulas and keeps the results. And there is nothing wrong with that, by the way. I'm going to undo this with control Z, so the formulas are back. Another possibility is a new feature introduced in Excel 2013 called Flash Fill. Now what I'm going to do here is click the second named entry right here. Holding down the shift key, I'll double click, highlights the entire cells downward, delete. Imagine if I'd just typed in this name: Paige, Lisa. The new feature called Flash Fill possibly will recognize what I've done here, and then copy this down the column. Flash Fill is found on the data tab, right here in the data tools group, Flash Fill. Excel makes a pretty good guess here and does a pretty reasonable job, except it doesn't handle the spaces very well. For example, in row three, those multiple spaces are still there, that's true in row six. It looks like it took care of the leading space issue as well. But that certainly has its merits, and at times, you definitely want to give that a shot. I'm going to undo that and go back to the formulas. The formulas do get the job done, in other words, this is the way we would want the data to look. So here's the shortcut: we can actually copy and paste values onto themselves, or into a different location. So I'm going to select the entire column B, and now, with the right mouse button, I'm going to point to either the right edge, top edge, or left edge, makes no difference, maybe it'll be the top edge, but I'll hold down the right mouse button and drag this into column A, and when I release the right mouse button, here's the pop-up menu, and what do I want to do? Copy here as values only. The wording maybe isn't quite as precise as we might want it to be, in other words why not paste the results of the formulas here? Maybe that's a bit too lengthy, but that's what we're doing as I click this button. And what are we left with? For example, in cell A2? That's Paige, Lisa, exactly the way we want it to be, and we wouldn't need column B, we'd simply delete it. There's another way to do this too, I'm going to press control Z, undo, to go back to the layout that we saw before. We also have the ability here to copy this to itself. Now, is this better than the previous approach? Not necessarily, maybe slightly faster. This time what we'll do is, we've got all our cells selected in column B, remember these are all formulas here, with the right mouse button, I'm going to drag this into column C and then right back in to column B. I could've dragged it right-left, or left-right over into column A, it makes no difference. I'm still holding down that right mouse button, and as I let go of the right-mouse button, there's the menu, copy here as values only. So what are we left with right there? Paige, Lisa. You see it up in the formula bar. And then we get rid of column A. A similar situation exists over here, slightly different in nature. In column H we've got some salaries. In column J, we've calculated the new salaries, and they've been approved, these are all formulas here, but what we'd like to do ultimately is simply get rid of this data by putting it over in column H. And we can't move it there because we've got formulas that refer to the data in column H, we can't copy it there either. But what we can do is paste the results of this just like we saw in the earlier example, this time over into column H. So, holding down the right mouse button, drag into column H, let go of the right mouse button, copy here as values only. All set. Readjust the column width perhaps, maybe change the heading, maybe not, we don't need column J right now. It is inaccurate because it's actually adjusting our new salaries, so we'll just delete that, perhaps the entire column. But we've got fast, efficient ways here of converting formulas into their results. Either by copying the data into different cells, or copy and pasting the data right into the current cells. So this is a valuable shortcut, it's an easy way to convert formulas to values.

Contents