How to fill in blanks in Excel in order to use the data in a sort, filter or pivot table using the GOTO Special tool.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
- - 2:47
- - Skill Level: 5
- - What's my skill level
Fill in Blanks in Excel
In this example we want to fill in blanks Excel. 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