Hide worksheets, row, columns, cells and other Excel elements

- [Voiceover] Many Excel users are familiar with the idea of hiding a column or hiding a row. I've got a worksheet here and I'm ready to print it, but I really don't want to print the Social Security Numbers or phone numbers, so I'll click and drag across columns D and E and then simply right-click and Hide. And starting in Excel 2013, there is an indicator whenever there's a hidden column between the other columns that are visible, a slightly thicker line, for example, as we see here between columns C and F. At a later time, of course, we may want to bring back the data.

We'll simply drag across here and do one of two things, either right-click and then Hide. That's what most people do, or maybe slightly faster, after dragging across, double-click the boundary, and the data's back. And similarly, on a list to the left here, a different worksheet, I'm ready to print this, but it turns out I don't want to print the admin training group. They're gonna be transferred into a different city, so we don't want their names in this printout, so I'm highlighting these rows right here, and then right-click and Hide. So those rows are hidden. When I print the data, it will not be visible.

There could be other reasons for hiding the data, too. At certain points, maybe I just don't want to see that list for a while, or I don't want to see that data, so I hide the rows. Hiding rows, hiding columns, easy to get to. If I've hidden other rows farther down the list and I want to bring back all the different hidden rows, and there might be lots of them throughout this list, I'll simply click in the upper-left corner and then either right-click and Unhide as we did with columns, or simply double-click any boundary between rows. Double-click.

All hidden rows in the entire worksheet will become unhidden. You can also hide cells, not so well-known. I don't want these salaries showing up here, and they're based actually on data in another worksheet called Source List, the one we just saw, but I'm selecting the top cell. And now holding down the Shift key, I'll double-click the bottom edge, and all these are selected. I'm going to apply a format. Now, before doing that, I could take an easy step of simply making the font be white, so on the home tab, here's the Font button.

I'll make the font be white, and we can't see the data. Now, if I happen to click here, we can see it in the Formula bar, but we can only see it as a formula. We can't see the actual result, so in some cases that could suffice. Let me backtrack, with Control Z, Undo. The data is still selected. You can also apply a format that displays nothing. To get to Format Cells quickly, we can press Control 1, and in this dialogue box here, we can set up a custom format not so well-known; simply three semi-colons.

Long story short, if you're familiar with how to set up custom formats, the semi-colon is a separator between different kinds of formats, and when there are three of them, the beginning format that we might have, in this case, we got nothing, but the beginning format would be for positives, then for negatives, then for zero, and then for text. But since we're putting in nothing here, we're saying, in effect, no format. Therefore we don't see the data. That's hardly any better than making the font be white. We still, if we click here, see the information. Again, it's based on a formula.

You also have the ability, and this is not so well-known, and typically I do not recommend it, but you can hide the formula bar. So for example, in this worksheet to the right, and here we actually do have salaries, if we were somehow making a presentation and didn't want to inadvertently click here, or we didn't want these to be visible, we could, if we wished here, simply hide the formula bar. Now before doing that, let's say if we didn't want to see these, we'd do the same thing we did before. Click here, hold down the Shift key, double-click the bottom edge.

An alternate way of doing this, this time strictly keyboard, Shift key is held down, and keeping it held down, I'll press Control/down arrow. There we have the data that way. And once again, I could be using that custom format, Control 1, and since I have created it for this worksheet, it should be at the bottom of the list. There it is right there, the three semi-colons. Click OK. And if I wanted to be able to click here, maybe even by mistake, and not have that salary show up, I could hide the Formula Bar. And again, most people don't do that very often, I almost never do it, but from the File tab in the ribbon, we can go down the left-hand side and choose Options, very last choice there.

Then go to Advanced, scroll down to the Display section, and uncheck the box for Show Formula Bar. Click OK. We're not even seeing the Formula Bar. We don't even know what's in that cell. As a general rule, I'd say don't do that, but there could be situations where you don't want to show formulas as you're manipulating or showing data. There's a formula here too, but you wouldn't know it from what we're seeing here. Now, another approach is you can hide worksheets. If I'm displaying this data, and let's say we brought back the salaries here.

Now, a quick way to bring back the salaries here will be I'll simply click the column letter, I'll use the comma format here. Maybe I don't want the decimals. Couple of clicks on the Decrease Decimal button, now we've got these back, but I might want to hide the source data. If we double-click here, we will see that there's a formula. Remember, I haven't brought back the formula bar yet, but I could right-click the sheet that's the source of this. It's called Source List. It's down at the bottom of the screen, so click that sheet, then right-click and Hide the sheet. And you might be saying, "Well, can't someone unhide it?" That's true.

Another person could come along and right-click and say, "I wonder what's hidden here?" Let's go to Unhide. I've got some other hidden sheets as well. If you want to turn off the capability of seeing which sheets are hidden, you can go to the Review tab and Protect A Workbook. When you protect a workbook, that means you inhibit the creation of new worksheets, the deletion of worksheets, the moving or copying of worksheets, or the unhiding of worksheets. So if we protect the workbook, and probably we would provide a password, although you don't have to, and then confirm it, now someone else who's viewing the workbook in its current state happens to right-click on any sheet tab, and there is no ability to even get to Hide or Unhide.

You wouldn't even know, necessarily, if something's hidden. And so we've seen a number of different techniques for hiding data. Earlier you saw how to hide the Formula Bar. Of course, we can go back, File tab in the ribbon, Options, and let's bring back the Formula Bar, click Advanced, and then down to the Display section, Show Formula Bar, and OK. And earlier I did hide a sheet. Let's get that back, but as long as the workbook is protected, we cannot get to those hidden sheets, so let's go back to Protect Workbook.

Notice, by the way, that when the feature is active, it's selected, but it doesn't say Unprotect. In other words, this is a command that's a toggle. Protect Workbook. I want to unprotect it, actually, and I'll put in the password, and now it's unprotected. I can right-click one of the sheet tabs, Unhide. You can only unhide one sheet at a time. I want to unhide Source List. This will go back into the location where it was when it was hidden, and there it is back again. So at different times we have this need for hiding data. We've seen a variety of different ways of hiding data by hiding columns or hiding rows.

We also saw how to hide cells, either by making the font be white or applying an unusual format. We saw, also, that we can hide sheets, and we can also hide the Formula Bar.