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

Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course  or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.