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?

  • “Excellent Regulatory Review Course. The self-study format (E-Book) provided an excellent overview of the regulatory requirements for California CPAs. I will be using the E-Book as  a reference material. Thank you for providing a very comprehensive yet affordable option for this required CPE.”

    - Constance

  • Overall I was very pleased with the course. Previously I knew just enough about pivot tables to be dangerous. Now I see the power that they have and I feel much more confident using pivot tables. The course materials were very clear and well prepared. I’ll be looking at your website for future CPE hours.

    - Scott

  • I loved the course as it had current cases which I am familiar with. Best ethics course I have taken in recent memory. Thanks!”

    - Kathy