Paste (Ctrl + v) array formula on cell range D3:D8
Remove #num errors (excel 2007)
When you run out of values to show, the array formula above returns #NUM! errors. You can avoid this if you use the IFERROR function, however use it with great caution. It not only finds #NUM! errors but all errors. So if you formula or cells contain an error you won't see it, the IFERROR function removes that error too.
The image above shows random cell values in column B, follow these simple steps to remove blank cells in column B.
Select range B2:B12.
Press F5 and a dialog box appears.
Click "Special..." button.
Click radio button "Blanks".
Click OK button. The image below shows that the cell range selection changed, now only blank cells are selected.
Right-click on one of the selected blank cells and a context menu appears, select "Delete..".
Another dialog box appears, click "Shift cells up".
"Shift cells up" will delete selected blank cells and move non empty cells up. This step will mess up your dataset if you have values arranged as records.
"Entire row" will delete row 3, 6, 8 and 11 in image above. If you have data on these rows they will be deleted as well.
Click OK button.
The image above shows that blank cells are now deleted.