Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Working with different portions of a worksheet in Excel is something you need to do at different times and a pretty well known shortcut but a great one is this idea that you can select different cells at the same time. Sometimes it's just for formatting reasons, sometimes it's because you want to delete data, any number of different approaches. Maybe I want to copy this worksheet somewhere, but I don't want to copy everything for example. Maybe I don't want to copy columns D and E. So with the mo use I will maybe select those. So I am about to hide this, but it occurs to me that maybe I shouldn't copy these salaries.
So how do we select different ranges within Excel? Efter selecting one range and in no particular order we let go the left mouse button then with the Ctrl key, we perhaps select another range. If it's a formatting issue sometimes you might just say "well you know I am going to change the look of those cells" and let go the left mouse button and the change the look of that cell and this cell and that cell and so on. Make them all blue or green or whatever. We have a lot of different situations where we want to select different cells or ranges at the same time and then apply color or delete them or whatever.
Now suppose we would like to copy this information or some of this information to a different worksheet. I am thinking the Admin Training people here. Now a couple of these people have left already and so we don't want to include them so what we might consider doing is highlighting those names. For example, Debbie Wolf she has left. We don't want to take her name out of the list just yet, and also Donald Holland up here. So using the Ctrl key we will select both of those rows and then right-click and hide them, and let's say that the Admin Training list here that we are about to copy doesn't need to include Social Security numbers and, pulling down the Ctrl key here, salaries.
Right-click on either column letter. Hide those as well. So here is the data we would like to copy. It's highlighted right-click and Copy or Ctrl+C, whatever works best for you. Go to an empty sheet, for example Sheet 1. We will put it up here, right-click and Paste or Ctrl+V, either way, and it looks a little strange here. Aren't we seeing some of those names that we saw earlier? There is Debbie Wolf. There is some other names, there is Donald Holland, and the Social Security number is looking a little weirder here and so are the salaries. So it just didn't work so well.
Let's go back to HR List here where we started here. Now if we would like to copy this and not include the hidden data, we press Alt+Semicolon. Alt+Semicolon, strange keystroke shortcut. And if you look at the changed display there, I can sort of figure out what's going on. It's as if we are picking up the data in little chunks, and now when we press Copy or Ctrl+C, look at the difference on the screen. Wuite a bit different, and now it becomes even clear of what's happening. We are about to copy nine chunks of data here, but we are not including, we are not picking up the hidden data, and that's not going to be really confirmed until we actually do the paste.
So let's go back to the other sheet and paste this right under the other data, click right here, right- click, Paste. There we go. And we see what's happening. This time we are not getting the Social Security numbers. The phone number's looking little weird. We are not getting the salaries, and we are not getting the two names that we say earlier that we actually had hidden. Donald Holland up there right after Troy Chase. We don't see his name down here nor Debbie wolf, under Kent Oconnor's name. So Alt+Semicolon is a strange keystroke shortcut and we don't always remember them.
So what if you were trying to copy data like this at a later time and you forgot that keystroke shortcut? And by the way, the concept of selecting visible cells only just isn't that well known in Excel. You will find the feature buried on the Home tab in the Editing group on the extreme right under Find & Select and not so obvious here either, it's under Go To Special. Finally, in here Visible cells only, there we go. Then we could proceed with our copy if we wanted to.
So the shortcut is not so well known and by the way if you do use a filter and a filtered list which hides data, you don't have to worry about this feature because automatically when you copy data from the filtered list, it does not pick up the hidden data. So you don't have to worry about it there, but if you have created manual subtotals or as in the case here where we specifically highlighted and hid certain columns and rows. In this case too, we did need to use that shortcut or at least the feature that allows us to select visible cells only. So different uses for this feature as well as for that general idea much more widely applicable of selecting non contiguous cells.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 91216 Viewers
80 Video lessons · 138196 Viewers
59 Video lessons · 57025 Viewers
52 Video lessons · 70658 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.