Someone emailed me recently, asking how to copy just the numbers, from a column that also contained some blank cells. The goal was to paste those numbers in another location, in a solid block, without all the blanks.
Copy the Numbers
The email didn’t give any details about the worksheet setup. For example, were the numbers typed in the cells, or were they the result of formulas? So, instead of replying, and asking for clarification, I decided to make a quick video that shows 3 ways to copy and paste just the numbers in a column.
The easiest solution, if the numbers are typed into the cells, is to select them with a Ribbon command.
- Select all the cells with numbers and blanks
- On the Ribbon’s Home tab, click Find & Select, then click Constants
- All the numbers will be selected, and the blank cells will be ignored.
- Then, copy the selected cells, and paste in a new location.
Copy With Go To Special
If there are text entries in that column, or if the numbers are the result of a formula, use the Go To Special command to select them.
Then, in the Go To Special dialog box, select Constants or Formulas, and check the type of data that you want to select. In the screen shot below, Formulas is selected, and only the Numbers option is checked.
Click OK, and the type of data that you specified will be selected.
Watch the Video
To see the steps for copying just the numbers in a range of cells, and pasting them in a different location, watch this short video. To follow along, you can download the sample file from my Contextures website.
Go to the Data Entry Tips page, and the download link is in the Copy and Paste Numbers Only section.