Join Dennis Taylor for an in-depth discussion in this video Selecting non-contiguous ranges and visible cells only, part of Excel for Mac 2011 Tips and Tricks.
- In this workbook we're looking at a worksheet called Profits. If you're trying to make formatting changes and the cells you're trying to apply the formatting changes to are not adjacent to one another, you have to select the range, make the change, select another range, make a change. You can certainly do that and there's nothing wrong with it really but it's more efficient if you learn how to select non contiguous ranges, that's a mouthful. I want to change these cells here to be bold, also the ones down here. We can highlight either range first, makes no difference, but after highlighting one range, release the left mouse button and then with the command key held down highlight the other range.
I want to make them all bold, just make them bold, or blue or whatever. I want to add some color here to offset the different months here to make it a little bit easier to read so I'm going to highlight the January data this way, letting go of the left mouse button, using the command key to highlight these cells here and these cells. Just a slight change here from the home tab, the fill color bucket, a pale green or some other color, just to offset the data a little bit. Certainly not necessary but just brings out the idea that we sometimes need to highlight non contiguous ranges.
We can use this also with formulas at times too. At other times when we're working with data, we're about to make some copies. I'm going to go to a different worksheet here called HR list, it's the first sheet in this workbook. I'm looking at the data here. I need to copy the data for the Admin Training Group. I want to get the titles as well. I don't need the data from the ADC group so I'm going to be hiding columns two through six. Right click and hide. And before copying the data I also realize that I really don't need the Social Security Numbers and phone numbers.
So I'm going to hide these two columns dragging across columns D and E, right click and hide. It also turns out that one of the people on our list here, Erik Pratt, has recently left and we don't usually take the name out of the list for a while. We might put strike through or add a color or something but for the moment we're simply going to hide row 14. That's for Erik Pratt. What I'd like to do is to take this data here and copy it to that empty sheet one. So I've got the data highlighted.
Remember, there are hidden rows within this. Rows two through six are hidden, row 14 is hidden. We've got some columns hidden as well, columns D and E. I'm about to copy this data, press command C, and go to the empty sheet, sheet one, zoom in on it a bit, although that's not critical, and simply paste the data in the upper left hand corner, command V. And right away, I'm a little bit unhappy about this because I got columns D and E, I don't even care to see them anyway, and I also got that ADC group that I didn't want to see and here's Erik Pratt down here.
So I got all the data. What do we do? There's a keystroke shortcut and you wouldn't guess it because it's pretty obscure, that allows us to select just the visible data. In other words, not the hidden data that we have here. So, on this worksheet, with the data highlighted we simply press command shift Z, highlight the data. We're gonna press command shift Z. Now the slight screen change, you can't really see anything different.
But as soon as you press command C, it looks a little different. Now it might be a little tricky to see on your monitor, but we see dotted lines around different chunks of the data. We're now on the path of picking up this data in pieces and then if we go to sheet one and I'll zoom back a bit here, and simply paste this below the data. Right here, with command V, right away we see it's narrower. It doesn't contain these ID and phone number columns. We don't see any Erik Pratt in here and we're only seeing people from Admin Training.
Of course in the long term we would get rid of the other data and adjust the column widths and so on. So, going back to the original data here, if you're about to copy data, and you've got hidden rows and or hidden columns, you're going to get all the data. The only exception to this is if we were using the filter capability. In filtering, when you see a list, and you copy paste it, you do nothing special, you only get the visible data. In any other situation like this one, where there are hidden rows and or hidden columns before copying the data, you need to make sure that you're selecting the visible cells only.
Now the keystroke shortcut, command shift Z, certainly doesn't spring out of any intuition, it's not the easiest thing to remember so you would certainly write it down. But if you forget or didn't write it down, you'd be hard pressed to find this. You need to go to the edit menu and choose Go To. Now a shortcut for that is the F5 key, you could do that. In either case it takes you to this dialog box. You can choose Special and lots of good choices in here including the one Visible Cells Only.
It would be nice if they indicated the keystroke shortcut for that right here. Anyway, we click OK and now we've selected the visible cells. Once again, as we press command C on the path to copying this, we would be picking up just the pieces of the data that are visible here. So at different times when you're copying data remember if you don't want to copy any of the information that's in hidden rows or columns, you either press command shift Z, or you find that command by way of the edit, Go To process. Earlier in this movie we talked about how to select non contiguous ranges.
Learn the top shortcuts, find out how to most efficiently navigate and control the display, and discover the best ways to select, enter, and format data. The course also includes ways to leverage drag-and-drop features, shortcuts for formulas and operations, data management efficiency techniques, guidelines for working with charts efficiently, and a selection of quick tips.
- 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 and zooming quickly
- Entering data more efficiently
- Performing calculations without formulas
- Applying formatting with keyboard shortcuts
- Quickly cleaning up extra spaces and deleting duplicate entries