Copy Numbers Only in Excel

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.

copypastenumbers02

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.

copypastenumbers03

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.

copypastenumbers04

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.

copypastenumbers01

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.

______________

Leave a Reply

Your email address will not be published.

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