Restoring missing column titles

- [Voiceover] In this worksheet, recurring titles for example in cells A3 to A6 have been eliminated also in A8, down here, probably done for visual reasons, both for viewing on the screen, also when this is printed on paper, it's easier to read this way than it would be. The problem is, if we leave it like this and we sort the data, suppose I sort this by employee name. I'm going to click in column B, go to the Data tab, choose AZ, the names are now in order alphabetically but which department is James Abbott in, Robert Acosta? We don't know, necessarily.

And, based on what we had seen before, would we assume that all these people here are in Audit Services? Well, not really because we've got an alphabetized list of names to the right of them. So, obviously we've got a problem here. Let's undo, first of all, control + z. Go back to this display. The next worksheet to the right, you could say, has the reverse problem. Certainly not a problem as we see it. But if we wanted to print this and not show these, how do we do that? Do we have to delete these? Then we create the same problem that we saw earlier. We can deal with both of these but in different ways.

I'm going to go to the first sheet, Missing Titles, and show how we can fill in these cells with that entry and fill in these cells with Admin Training, and so on, and so on, and so on. And it's based on a few different concepts. First of all, how many rows are in this worksheet? Quite a few. All worksheets in Excel, starting with Excel 2007, over a million. Now, if we write a formula for these cells to pick up the data from the cells above, that's gonna be helpful. But, could we do it for these cells at the same time? We could.

But that might mean, for example, highlighting these cells, letting go of the left mouse button holding down control, highlighting all these, and that's gonna take a while. Let's click in column A. Now, a little known feature is the ability to select blanks. If you press the function key, F5 or if you go to the Home tab, Find & Select, Go To Special, you end up in the same place, right here. F5, by the way, as I escape, gets you there slightly faster. I'll press the function key, F5, then Special, same place.

If we choose Blanks, watch the display to the left. Now, remember, there are over a million rows, so what's happening below the data? Here's the bottom of the data, here. Excel doesn't highlight the cells below it. So, even though it doesn't look like it right now cells 739 through 42 are selected, 34 through 37 and so on, these are all selected. Now, if I were to type something and press Enter, it would go in one cell only. Right now A3 is the active cell. You see it in the upper left corner. If I type something and press Enter, it only goes to A3.

But if I type something and press Control + Enter, it goes into all these cells at once. But what if I type a formula? Equal + up arrow, now that says equal A2. If I were to press Enter, I'd only see ADC here in this one cell, nothing else would change. But, if I press Control + Enter, it's as if I am saying, "Let's put the same relative formula "in all the blank cells." I'll press Control + Enter. So, that formula is equal A2. What's this formula here say? That's equal A3, equal A4, equal A6.

This cell was not part of the selection when we selected the blanks, so, it says what it said before. This one says equal A7. So, it looks good right now. We could Print if we wanted to, but if we want to keep these new entries, what we need to do is to select column A, remember most of these are formulas, and a real simple little action, a great shortcut will solve our problem. With the right mouse button, I'm simply going to drag this data in one smooth action into column B, and then right back on top of itself. Let go of the right mouse button, Copy Here as Values Only.

There we are. That's ADC, that's ADC, you can see it in the formula bar, you can double click and see. We filled in all of these. Now, if we sort the data, we can click in column B, do what we did before. Everything's looking pretty good and we have a correct department associated with each employee here. Undo, back to here. So, that's easily taken care of. Now, we might want to hide the titles. Notice that the name of this worksheet is not Remove Titles. We don't really want to remove these, but if we want to print this out, so that we're not seeing these, we can in effect hide this data.

We're going to be doing this by putting in a formula by way of Conditional Formatting. We want to check and see if every cell in column A, one-by-one, if it's equal to the cell above it, let's make its Font be white. So, if this is equal to that, make the Font white. If this is equal to that, make this font white. So, in effect, all of these are going to have white font. We're not really destroying the data, we're simply making it not visible. This assumes of course, that if we were to print this, we'd be printing on white paper. Now, we need a formula for all these, though.

So, we could click column A, that would work. But I'm actually going to start at cell A2. Holding down the shift key, I'll double click the bottom edge. That highlights all of these. Now, Home tab, Conditional Formatting. Let's establish a new rule. Right now, our active cell is A2. I highlighted all the cells in column A from A2 down to A742. Use the formula to determine which cells to format. The formula that we would use is equal A2. Now, even though we're using A2, by inference we are meaning every single cell, one-by-one.

If we're checking to see if A2 is equal to the cell above it, we'll type equal A1. Whenever it is equal to the cell above it, even though for the moment we're talking about A2 and A1, by inference we're talking about every single cell that's been selected in column A all the way down to row 742. So, when this cell is equal to the cell above it, let's apply a font color. Click the Font tab here, Color, White. Color, White. Click OK. Click OK. There it is. Now, remember, the titles are truly there.

Preview, Control F2, displays the data. It will print looking like this. Escape. We're back here. If you click here, you can see in the formula bar that they're selected. If you click column A, sometimes can see through this. Now remember if we've got different background color here on the cells, we can see the data coming through there. We can see how it's black and white, too, what the difference it. So we haven't really destroyed the data. That means of course we can sort the data. Probably you wouldn't leave it in this state if you were sorting immediately in the data, but you could.

So, two different techniques, here. Contrasting techniques. Not quite the opposite, really, but in a sense, sort of the opposite. Initially, we filled in titles over on this worksheet and here, we didn't really get rid of them, but we made them not appear so that when we print this or when we're simply viewing the data we're not seeing that repeated entry over and over and over again.