Restore missing labels and hide repeating labels

- [Instructor] We're looking at a worksheet called RestoreLabels HideLabels. It's in our chapter three file. And right away you can see in column A that the department names are not being repeated here. They don't need to be and this is easier to read. Imagine these being filled in with ADC, all these being filled in with Admin Training. The problem here is if we leave the data here in this shape and then sort the data. I'm going to click within the data here and on the Data tab click the Sort button. And I'll simply sort by column B, that's the Employee Name.

Ascending order. And just before we do this keep an eye on Victor Arnold, who's in row three right now. Full time, compensation all eights, 88,888. Click OK. We've sorted the data. Where's Victor Arnold now? He's down here in row 17. Everything in his record got moved up and down properly, as it did with all the other records, but now it looks like he's in Quality Assurance. And all the department names seem to be clustered closer to the top. As we scroll down the list here we see big gaps and it's pretty apparent after a while that we've got big problems.

These names are not associated with the appropriate department anymore. So let's undo this. I'll press Control + Z. Now there is a way to clean this up relatively quickly. The key is to use a few features in Excel that are relatively hidden. You might not be aware of them. One is the ability to be able to select a bunch of cells and then from that list highlight just the blank ones. What we want to do is to select the blank cells and then fill them in with the data from the cells above.

So I'm going to click column A and on the Home tab Find & Select off to the right. Go To Special. This is a dialog box with lots of little treasures in it. And one of them right here is Blanks. Click OK. And we're about to see only the blanks selected in column A. There they are. And you might be wondering, column A goes all the way to over a million rows. What happens if we scroll to the bottom of this list here? Did it select the blank cells below this? And fortunately it does not do that.

Now the next step, and this is a bit unusual too, the active cell right now is in cell A3. If I type = and press up arrow I'm about to say that cell A3 is getting data from A2. But all the other cells below it are selected as well. If I press Control + Enter every one of these cells will be getting data from the cell above. Ctrl + enter, see what's happening. All these are filled in. Now most of them right now are formulas and using one of our tips that you saw from chapter one I'm going to select column A and convert these various formulas into their results.

I'll use the right mouse button and simply drag this into column B and right back into column A, letting go of the right mouse button, Copy Here as Values Only. There we are. And so we have nothing but data here. Going back to the previous look. Suppose we wanted to print this and not show these entries, the second entries. We can do this not by destroying the data, but by simply hiding it. If the font of these is white then we won't see these on the screen or when we print them on white paper.

So let's select all the data from cell A2 downward. A quick way to do this, the active cell's in A2, got the Shift key held down, I'll double-click the bottom edge, all those cells are highlighted. Then I'll go to the feature Conditional Formatting and what we'll use here is a New Rule. Now the active cell's in cell A2, so when I write a formula here I'll only be using the A2 cell, but by inference every other cell will have the same rule.

And the rule is = this cell A2, when this is equal to the cell above it, A1, that's when we want the font to be white. Not these are absolute addresses, we want to make sure these are gone. So I'll click A2, you can press F4 multiple times. We don't want that to be an absolute address. I'll click in the A1 address, press the function key F4 a few times, our formula now reads =A2=A1. And a different way of saying this is whenever a cell is equal to the cell above it then we want the font to be white.

So we go to Format, Font style is here, Color is right here, choose white, click OK, and OK, and we see what's happening. The data's still here, but when we print this we won't see it and as we look at the data now we don't see it. So two different techniques here for bringing back the labels or hiding the labels. And hide is the correct word here, we're not destroying the labels. Restore is the correct word, we did bring back the labels.

Using techniques in Excel you might not be familiar with, but certainly real time savers both of them.