Move cells with data to the top

Did you spend too much time to move cells so that there are no blank cells in each column?

Again, a picture tells thousand words.  Here’s the task…Excel Tips - Move cells with data to the top 0.JPG

Would you do it this way?Excel Tips - Move cells with data to the top 1.gif

Or this way?Excel Tips - Move cells with data to the top 1.1.gif

What did I do in the second animated screenshot?

In short, what you need to do is to select all blank cells, delete them and shift cells up. That’s all.

Step 1: Select the range (Shortcut: Ctrl+*) 

Step 2: Go to  –> Special… –> Blank (Shortcuts: Ctrl+G, Alt+S, k, enter)

Excel Tips - Move cells with data to the top

Step 3: Right Click on a blank cell selected –> Delete… (Shortcut: Ctrl+)Excel Tips - Move cells with data to the top2

Step 4: Select Shift cells up –> OKExcel Tips - Move cells with data to the top3

As simple as that!

Note:  Shift cells up applies to whole columns…… Make sure there is nothing underneath your range selected, otherwise you may distort everything below. 😦

Read also Clear or Delete.

If you do have something underneath, you may consider

  1. move everything below out of the columns affected; and then move them back;
  2. copy the data range (that you need to shift cells up) to a blank sheet, apply the steps above and then copy back to replace old;
  3. VBA? which i have no idea. 😛

About MF

An Excel nerd who doesn’t work on Excel most of the time at current job...... 🤔 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged , . Bookmark the permalink.

1 Response to Move cells with data to the top

  1. rmb says:

    for a single column of data, you can sort the data and blanks will fall to the bottom

    Like

Leave a Reply to rmb Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.