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. 😛
Advertisements
This entry was posted in Excel Tips and tagged , . Bookmark the permalink.

One 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

Comments, suggestions, corrections are welcome.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s