Join Dennis Taylor for an in-depth discussion in this video Working with hidden data when copying cells and creating charts, part of Excel Tips Weekly.
- [Instructor] At different times when you're working with Excel Charts or simply with Excel Data, you want to hide certain information. And that has implications. Not only in the way that view charts but also in the way that we see data. And when we copy data that has hidden information in it, sometimes we get unexpected results. Let's first look at some charts. You'll notice that these two charts are identical in terms of the information they are displaying. Obviously, the charts are different in the design. Below, the chart on the left we see the phrase Plot Visible Cells Only.
And on the right, Plot All Cells. In Excel, when you create a chart, the default setting is to show visible cells only. The chart that we see on the left and also the one on the right, are both based on the data we see below, starting here in row 21. Now, if I hide rows 24 and 25. And I'll simply drag across those two rows, right-click and Hide, the chart on the left reacts. We're not seeing Pennsylvania and Illinois.
The chart on the right shows all the states regardless of whether the data is visible or not. Now remember, the default setting in Excel is to show visible cells only. Now, let's undo that. I'll press CTRL+Z. It's highly unlikely that you would have these two charts together on the screen at the same time. Although, you certainly can and I'm doing it right now. But if you were making a presentation of this information, perhaps you might be showing states, like this. And you might be showing a few more, of course, too. But I've also go a filter in place just for column A.
Now, most of you know how a filter works. We can use a drop arrow here. And the more states we have, the more efficient this would be. But let's just say that we don't want to see Florida and Illinois in this particular list. We could uncheck those boxes, click OK. And the same effect occurs as what we saw before. In other words, if the rows are not visible the data for the chart is not showing up on the chart itself. So, how did we get here and how do we control this? Keep in mind too that for presentation purposes, you might want to have this flexibility here to hide and expose the data as you wish.
So, a filter area would be a more convenient way of exposing the data, hiding the data at different times, rather than hiding and revealing rows. This is going to be more efficient. If you right-click on a chart, and choose Select Data. You'll notice a choice called Hidden and Empty Cells. The default setting here is that the box that we see is unchecked. Show data in hidden rows and columns. Now to verify that that's the default setting, I'll show you very quickly here.
Just by doing Cancel here. Let's have all the data exposed. I'll simply choose the drop arrow here, Select All, OK. What if we didn't have either chart, and we've highlighted the data right here? We create a chart quickly with Alt+F1. There's a chart. That's the way it would look with all the data visible. Right-click a chart, go to Select Data, and Hidden and Empty Cells what do we see? This box is unchecked. That's the native state, what we call the default setting for Excel, do not show the hidden data.
And we don't need that chart anymore. I'll simply press Delete. So, what's happening in the chart on the right? In this chart we're choosing to plot all cells regardless of whether the data is visible or not. And there certainly will be times when you want that setting. And so what's happened over here? You probably guessed it already. If we right-click and choose Select Data, what do we see under Hidden and Empty Cells? That box is checked. And that certainly is your choice to make if you wish. So, we see the difference here in the two charts. And once again, although you don't necessarily need to use the filters that makes it easier to use here.
Let's say we just want to see New York, California, and Texas. We'll come back here, unselect them all. California, New York, Texas, Florida. Click OK. There we are. And you can tell by the heading and you can see by the number of columns here in the chart what's showing. And again, it's unlikely you would want to be showing both of these at the same time. So, we've got that flexibility there. When we work with data sometimes we run into other issues. I've got a list here. And what I'd like to do is to copy information from the Admin Training Group here.
I might as well pick up the headings as well but in order to do that first I'll hide these rows right here. Right-click and Hide. And when I copy the data, I don't want the ID and the phone number, so I'm going to drag across columns D and E, right-click and Hide. So, here's what I'd like to pick up, all the data for the Admin Training Group. Let me zoom back first with CTRL and the mouse wheel. There we are. I want to pick up all this data here. Now, within that range, I've got two hidden columns.
And also, rows two through six are hidden. So, when I copy this, either by right-click or CTRL+C, I'm ready to paste this data onto a different sheet. And I go over to this empty sheet. I'll press CTRL+V to paste. And look what happens. I've got ID and phone number. I've also got the ADC people here. This is not what I wanted. So, let's go back here. There's a keystroke shortcut that says in effect, "Let's just pick up the visible data". And it's not a keystroke shortcut you would distinctively come to recognize.
It's ALT+;. But they include some rows that I don't want to copy and some columns that I don't want to copy. So, I will press ALT+; and there's a subtle difference on the screen. And now I'll press CTRL+C. And that's a more prominent display. And you can see now, that the data's actually being picked up in four separate chunks. So, I pressed ALT+; to select just the cells that were visible. Then I pressed CTRL+C to copy. I'm going to Sheet2 now. I'll paste this off to the right.
I'll press CRL+V. And now we're getting the data we want. We're not getting that ADC department data. We're not getting the ID and phone number data. And of course eventually, I'll readjust the column widths, and use that data. So, going back here again. How do we know that keystroke shortcut? Well, like a lot of things in Excel, you pick it up along the way. If you're looking for it, well good luck. And the help system, if you start looking for keystroke shortcuts you would eventually find it. The command way to get to this feature, is buried deep in the menu system, But here's where it is.
And there is a shortcut to get to it also. So, you've got your data highlighted. Maybe you forgot the keystroke shortcut. Or maybe you're not aware of it. But someone has alerted you to the fact that on the Home tab, a far right button Find & Select, there's a choice called Go To Special. And in this dialog box are lots of interesting choices, including one Visible cells only. This is the one that has a keystroke shortcut Alt+;. Although, we're not seeing it here.
Click OK. We have now selected the visible cells only. Now, of course, we could've done that a lot faster with Alt+;. Now, a slight shortcut to get to that command sequence is to press the function key F5, and then Special. So, that gets us here a little bit faster. Visible cells only. But of course, the keystroke shortcut Alt+; is even faster. Now, I'm going to bring back the hidden columns. I'll click in the upper left hand corner. We can right-click any column, and simply Unhide.
And you right-click any row, Unhide. Now, there's another variation in all this. If you're doing filtering. I'm going to go to the Data tab here, and apply a filter. And this time I'm going to click the arrow for department, and uncheck ADC. Click OK. And this time I'm going to do the same thing as before. Now, before doing this I might or might not want to hide columns D and E. So, it'll make no difference. But let's say I don't want to copy that information. I drag across here.
Now, remember this is a filtered list. Although what I'm doing for the moment isn't really part of what we typically do with a filter. I'm hiding these columns. I'm going to select this data right here. And this is a filtered list. We don't have to worry about picking up the visible data. It's going to happen automatically. I've highlighted this data. I'll press CTRL+C. I'll jump over to the other worksheet here where I copied previously. And off to the right, I'll paste right here, CTRL+V.
And there it is, we're getting the data we want. We don't have to worry about picking up the visible cells if we are using a filtered list. Right now, the filter is in effect. Filtering automatically picks up the visible data when we highlight data that includes hidden rows and/or hidden columns. So, another small exception to this too. So, I press Esc. What if I'm using the filter but I've clearing the filter? Now, I've cleared the filter but I still have some columns hidden here.
So, right now if I were to pick up just this data for the ADC group. Remember, there are two hidden columns there between column C and F. So, if I copy these four columns here it is a filtered list but notice nothing's really being filtered right now. I've hidden these manually. Columns D and E. So, if I copy this data with CTRL+C, go to a different worksheet over here, and CTRL+V. It does pick up the data that I really didn't want. So, even though we're using a filtered list here, we're not using the filter to hide these columns.
And the same thing would happen, if we hid these rows not by way of the filter but by simply hiding the rows. But even though the filter's in effect we're not using the filter to hide these. So, if I simply drag across these, right-click and hide. Granted this is a filtered list but I did the hiding not by way of the filter. So, here too if I were to highlight just this data here, and press CTRL+C, I've got hidden rows in there. I've got hidden columns. If I go to another sheet and paste this data, I'm going to get a lot more than I would've expected.
So, main idea here is when you're working with lists like this, if you're expanding and shrinking the list and you're hiding rows, hiding columns, if it's not a filtered list you got to remember to choose the visible cells only. If it is a filtered list, you have to have at least one filter in effect, and then all the actions that you take when you copy paste are going to work the way you want them to. So, these are number of different issues that we covered here as we talk about hidden and visible cells in Excel. The impact that we saw earlier with charts. And the impact we saw most recently on lists, both those that were filtered and those without filtering.
Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Appropriate for all
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.