Only Copy Cells with Data

A Method to Copy Only Cells with Data

Have you ever had a couple of columns of data that you wanted to combine into one column?  What do you do when there are blank spaces in some of the columns of data?  Many will cut and paste the data from one column to the other, then manually delete the rows containing blanks.  This begs the question; Is there an easier way?
One way is to use the GoTo Special feature in Excel.  This feature is located in the Home menu, under Find & Select on the far right hand side of the toolbar.jfog-1a

 

As an example, assume that the following data in in Excel and you want it in one column for analysis:
jfig2

Let’s copy only the cells that are not blank from B1:B7 to column A, under A7.
Select cells B1:B7
Click Find & Select and choose GoTo Special.

This menu will appear.

go to special dialog box

 

Select the radio button next to Constants and press OK.

 

Notice how only the cells with data are now shaded.  Hover over any of the three shaded cells and right click.  Select Copy.  Then, select A8, right click and select Paste.

Only the non-blank cells are now pasted into column A.

This technique can save quite a bit of time if you’re working with a large amount of data that requires manipulation into one column.  It also works with any data that needs to be moved and contains unneeded blanks.

2 Responses

  1. Angela Citizen says:

    I think I get the gist of it, but I am a little confused about the instructions to paste in cell B7 to paste. Did you mean A8?

Leave a Reply

Your email address will not be published. Required fields are marked *

  • Thank you for providing very informative CPEs. I have enjoyed your courses for years now. Yours is truly my go-to CPE sources.

    - Lily

  • “..may I say it is rarer that I ever have gotten a personal reply from a CPE Site! Glad I have found your site.”

    - Tom

  • Your ethics course was the most interesting and clear course I have taken. I usually dread the years it is required but this year was a pleasant surprise.

    - Terry