Transcripts for the above video clip:

GO TO SPECIAL TRICKS – BLANKS

In this section you’ll learn a trick using the GO TO SPECIAL command in order to make BLANK cells refer to the one above, in a quick and easy way and it is often used to help clean up reports from IT systems.

In this example we have a report which shows the Sales People, from various Cities, and how much thy Sold. However the problem with this report is that it’s split it up so that London is shown, and then Ben and then he has 3 Products sold, and then Mary and Susan have there own Products sold, and its very difficult to use this type of report in a pivot table or do any sort of analysis on it.
What we’d like to have, is that each line is filled with all the information so we’d see that London the Sales Person is Ben, and he sold the standard Product for 500. There is a quick and easy way to do this in Excel. You can make use of the GO TO SPECIAL command, but the easiest way to show you is to go through the steps. So what you can do is;

• you highlight all the data
• you go to Edit,
• GO TO
• SPECIAL
• and you’ll see that one of these options is BLANKS
• and when you click Ok
• you’ll see that all the blanks are highlighted
• now the next step is crucial, what you do is you without clicking on anything you’ll type equals

And what you’ll see is that one cell will have the equals in, and what you basically want is that for all these blank cells to refer to the one above, so these cells would all become London, and then New York would take over and these cells would become New York.

• So you’d say equals
• Ben
• and then its very important you must hold the control (Ctrl) button down
• and push enter,
• then you’ll see that all these cells now refer to the one above
• and when you come to New York you’ll see the New York is still there, but this one now refers to the one above.

The data is now almost perfect in the sense that each line has all the information you need; there is just one thing you have to keep in mind. Because these are formulas, what could happen is, if you change the data around by lets say sorting, the formulas will continue to refer to the one above.
So I highly recommend that after you’ve done this

• you just highlight the whole section,
• you copy it
• and you paste special it, just as values and that way you know that you can actually do whatever you want with the data and it will stay the same