Shift cells up if blank in Excel

As you work through a spreadsheet you may delete a number of cells and then want Excel to shift all the cells up so that there are no blanks.

As shown below, we have removed all the references to the solutions in our exercises but we want everything to move up.

The steps to follow are:

  • Highlight the cells with the data
  • Access the GOTO Special button i.e. HOME Ribbon, Find and Select, GoTo Special (you can learn more about GoTo Special here).
  • Choose to highlight the blanks and click OK

Shift cells up if blank in Excel

You will see that Excel now highlights all the blank cells. You can right click on any one of them and choose the Delete option.

Shift cells up if blank in Excel

Excel asks if you want to shift the cells left or up. In this case it is up.

Shift cells up if blank in Excel

Excel will now delete all the blank cells and shift the others up so that there are no blanks but the order is maintained.

Shift cells up if blank in Excel