Join Dennis Taylor for an in-depth discussion in this video Accelerating Cut, Copy, Paste, and Insert tasks, part of Excel Tips Weekly.
- The general meaning of the right mouse button is display a shortcut menu. If we right click a ribbon tab, we get some choices related to toolbar and ribbon adjustments. If we right click in the worksheet on a cell, we get lots of other choices here. If we right click on a column letter or a row number, we get a different set of choices. Not so well known is the fact that we can drag with the right mouse button and also get a list of choices. In this worksheet called Employees, I'm about to move column E, the Phone column, to the left of column D, but you can simply drag data with the shift key.
Hold down the shift key; drag the leftward here. Drag the left edge of it over here and let go of the mouse, and I've moved the data. Control Z, undo it. But you can also do this with the right mouse button. I've selected column E, now I can drag the top edge, left edge, right edge, doesn't make any difference, but I'll be holding down the right mouse button. Say I choose the top edge. When you point to one of the edges, you'll see a four way arrow. Hold down the right mouse button. Now I'm going to drag this on top of column D and then let go of the right mouse button, and I've got lots of choices, and in this particular example here, I want to Shift Right and Move, meaning shift column D to the right and move the column E entry leftward, Shift Right and Move.
We simply move the data that way. If you perform that function only occasionally, you have to kind of stop and think what it means, but it doesn't take long. At a later time maybe I would change my mind. I want Phone to be to the right of Status. I'll click column D, drag the right edge or top edge or left edge with the right mouse button, drag it on top of column F. Let go of the right mouse button, Shift Right and Move. We can see how it's done there. Now, on a different worksheet, and using the right mouse button somewhat differently, a sheet called Mixed Names here, I've got some formulas, first of all, in column B.
Let me zoom in on these a bit so we can see them better. We're trying to adjust two things here. We're trying to make the letters only uppercase when it's the first letter. We can see what's happening in the results here. We also wanna get rid of the extra spaces, like we see in row six, or the leading space in row two, there, leading space in row eight, and so on. So these functions here will get the job done. Now the new Flash Fill feature will allow us to get some of this done, but not all of the things we're looking for in this particular example here. So, what we have created in column B is the look that we want, and what do we want to do from here? We in effect want to say, "Let's take this particular set of data," and there could be thousands of entries, let's effectively say, "Let's replace column A with this, "as we see it." In other words, no formulas.
If we delete column A, these formulas have nothing to refer to, so we can't do that, and if we erase the content of column A, same problem. So, we've got the formulas here. Now, with the right mouse button, I'm gonna drag top left or right edge onto the data in column A and let go of the right mouse button, and although the wording may not be exactly what it should be, Copy Here as Values Only. It's as if we're saying, "Take those results in column B, "place them in column A." Copy Here as Values Only, and what do we have left, for example, in A Two? Simply the data as we see it, Wagner, Max, no formulas.
The formulas are still here over in column B, but now they're worthless. They don't mean much. We can simply delete the data from column B. And let me repeat this action because at first it is a little strange. Remember, column B has formulas that in this case are adjusting the upper and lower case and also getting rid of spaces. We selected all of 'em, and with the right mouse button this time I'll drag the right edge. With the right mouse button we're gonna drag this data on top of the data in column A, release the right mouse button, Copy Here as Values only.
In column E, similar situation. Here we've got a formula that's effectively replacing the Ys with Xs, but only the first occurence, so in some cases, for example in row four there, we've got two Y's, but only the first one's being replaced with X, and here too. The new Flash Fill feature wouldn't quite get this one right. Same Idea here, these are all selected. With the right mouse button, we'll simply drag this data on top of the old, and Copy Here as Values Only.
Same Idea. We don't need this data anymore. This is looking the way we want. In more complex situations, like column M. This time we'll do it a little bit differently, sometimes you want to copy this data to itself. This example's a little bit different, in that it's got something logic embedded in the formula that says in effect, "If there's no middle initial, "let's, don't put in a blank, put in a null string, "otherwise, do put in the middle initial "and trailing period." So these are all in place here. I'm going to highlight them all using the shift key and double clicking the bottom edge.
Now this time, instead of copying the data onto some of the old columns over here, and we can certainly do that, you can also copy data to itself, using the right mouse button. So, taking any edge, this time I'll just take the top edge. Holding down the right mouse button, I'm going to drag this momentarily into M One and then right back on top of itself. Let go of the right mouse button, Copy Here as Values Only. So what do we have left right here? As I double click, and you can see in the forumula bar too, simply the result.
Let me undo this, and do it again. Another way, not necessarily better, but same general concept. We've got all these formulas here, we effectively want to throw away the formulas and keep the results. With the right mouse button, this time I'll drag the right edge momentarily into column N and then right back on top of itself in one fluid motion. So pointing to the right edge, holding down the right mouse button, drag into column N, then right back on top of itself, let go of the right mouse button, Copy Here as Values Only.
Once again, we've got the situation looking like this. So different techniques here of using the right mouse button for turning formulas into their results. You can also use the right mouse button to copy formats. I've got some formatting here in column H, it's bold print, we got yellow backgrounds here. A blue background here, the middle one's not bold, the others are. We got currency format and so on. We'd like to copy these formats into column I. So, with the right mouse button, after having selected the data, I'm gonna drag the right edge into column I, let go of the right mouse button, Copy Here as Formats Only.
Do it that way. At a later time I decide I might want to have this format all the way over to the left as well, so I could take this data right here, select it, and this time I'm going to be using the fill hand on the lower right hand corner. But instead of holding down the left mouse button, I'll hold down the right mouse button and drag leftward. Letting go of the right mouse button, Fill Formatting Only. And we copy the format that way. Similarly, downward in row nine, I like this format here.
It's percent format, also with a light tan or orange color there. I'll use the right mouse button and drag the fill handle downward. Letting go of the right mouse button, Fill Formatting Only. So in all these different examples, we're using the right mouse button to achieve various copying and formatting techniques.
Author
Updated
1/19/2021Released
1/16/2015Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Intermediate
Duration
Views
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.
Related Courses
-
Excel: PivotTables for Beginners
with Dave Ludwig23m 52s Beginner -
Presentation Tips Weekly
with Jole Simmons3h 14m Intermediate -
Excel: Lookup Functions in Depth
with Dennis Taylor1h 8m Intermediate
-
Excel Tips - New This Week
-
Previous Episodes
-
Quick formatting tips8m 9s
-
Create an organization chart8m 56s
-
Auditing9m 1s
-
Adding comments and shapes7m 35s
-
Creating an Excel template7m 57s
-
Adjust banded-row formatting14m 35s
-
Dealing with circular errors8m 20s
-
Sorting tips and shortcuts8m 40s
-
Freeze Panes and Split5m 30s
-
Calculate % of change5m 41s
-
How to adjust names5m 45s
-
Sorting by moving columns4m 16s
-
Using the Solver Add-in4m 29s
-
Create picture links5m 37s
-
Display large values5m 16s
-
Create a powerful macro4m 40s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Accelerating Cut, Copy, Paste, and Insert tasks