I have an extremely basic question, but I have never found the answer by searching the internet. I simply want to create a large column of zeros with Excel.

Sometimes I want to create a huge matrix of zeros (maybe 600 rows by 500 columns) and then replace a few zeros with 1's to create a model design matrix.

I have always started by creating a column of, for example, 10 zeros, copying and pasting those zeroes, then copying and pasting the resulting column of 20 zeros, etc., until I had the desired number of rows. Then I would copy and paste that column of zeros one at a time over and over until I had the desired number of columns.

This procedure is tedious and time-consuming and I know there must be an easier way.

Do you know of any other methods?


If you want to create a block of zero's

  1. Hi-light (select) the area
  2. touch the 0 key
  3. touch Ctrl + Enter

To create a column of 0's in Excel:

  1. insert a 0 into one cell.

  2. copy that cell, for example by right-clicking the mouse and selecting 'copy'

  3. select the blank rows you wish to fill with 0's by left-clicking the mouse and dragging to high-light the desired blank cells.

  4. press control-v on the keyboard

You can also select a block of empty cells containing many columns and rows.


To create a column of a certain digit / letter:

  1. Insert the number (0 in your case) (eg. A1)

  2. Select that cell (ie. A1)

  3. In the name box at the top left (where it says your cell reference), change it to a range (eg. A1:A20)

  4. Press Enter ↵

  5. Press Ctrl + D (shortcut for 'fill down')

This is easier than dragging over each cell if you have 100s of cells you want to do this to.

Screenshot (I can't show the cells being highlighted after pressing Enter because Print Screen removes it > <):

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.