If you are working on a Excel 2010 spreadsheet that contains a lot of blank cells, it’d be one tedious job to remove all of them manually. However, there is a small but useful workaround you can try. Let’s see how to delete blank cells in a data set.
We will copying only non-blank cells to the required column. To copy only non-blank values from column, select the data range containing blank and non-blank cells, and head over to Data tab, under Sort & Filter group, click Filter button.
It will add drop-down buttons over column, click the drop-down button and disable (Blanks) option.
Upon click, it will only show non-blank cells. Now you can copy the values and paste it anywhere in the spreadsheet.