Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I want my column to look like

number
30/31/32/33/34
30/31/32/33/34
30/31/32/33/34
30/31/32/33/34
......

eg: The content in each cell is 30/31/32/33/34. I want to fill all of the cells in the column with the same content 30/31/32/33/34, but when I drop down the first line, the following line will be 30/31/32/33/35, followed by 30/31/32/33/36.....how do I make it so each cell contains 30/31/32/33/34.

How can I fill all the cells in a column with the same content(30/31/32/33/34) if there are 8000 rows?

If there are 8000 rows when using copy/paste, it will take too long, is there a simple way to filling the 8000 rows with the same content; 30/31/32/33/34?

share|improve this question

migrated from stackoverflow.com Jan 11 '13 at 21:24

This question came from our site for professional and enthusiast programmers.

    
i am sorry for my description, i have updated my question. how to fill out all the rows with the same content(30/31/32/33/34) if the rows are 8000. –  stack2013110 Jan 11 '13 at 20:00
    
You mean like copy/paste or how are you filling it? –  demongolem Jan 11 '13 at 20:01
    
yes. you are right.how to filling it –  stack2013110 Jan 11 '13 at 20:01
    
if there are 8000 rows. when using copy/paste, it will cost many many time, is there a simple way to filling the 8000 rows with the same 30/31/32/33/34 –  stack2013110 Jan 11 '13 at 20:06
6  
Copy the first cell, then select the next 7999 and paste. Or drag down while holding the Ctrl key. Or select the whole range, type "30/31/32/33/34" and the press Ctrl+Enter. –  Tim Williams Jan 11 '13 at 20:10

5 Answers 5

Here is the way I did the thing you asked for in 6 seconds:

  1. Type / select the initial cell with your string - let's say it is A1.
  2. Copy its contents (Ctrl-C).
  3. Quickly locate row 8000: press Ctrl-G - that will open Go To dialog box. Type A8000 as destination.
  4. When you're in the cell A8000 - press Ctrl-Shift-UpArrow to select the whole range of cells A1:A8000.
  5. And finally paste your input (Ctrl-V) - it will fill the whole range.

Perhaps this is quick for me, since I know and use many Excel Shortcuts - and recommend that to everyone who uses Excel. Here is the sample list to start:

Good luck!

share|improve this answer
    
@Tim Williams comment above is the nice shot as well - it appeared while I was preparing my answer) –  Peter L. Jan 11 '13 at 20:29
    
You can merge step 2 and step 5 by just pressing Ctrl-D (Fill Down) after you selected the range - without copying anything. And you can even speed this process up a tiny bit, by first selecting the full range (using either your method or use the Ctrl+Arrow key with a filled column to the left or right of it) - and then entering the text/formula, but pressing Ctrl-Enter and thereby filling everything. –  Peter Albert Jan 13 '13 at 20:39
    
@PeterAlbert Nice add, and thanks for Ctrl-D! ;) –  Peter L. Jan 13 '13 at 21:59

The answer is quite simple. Just hold down the Control (Ctrl) key as you drag down the auto fill handle. The last or any of the numbers do not increment.

share|improve this answer

First, for my answer.

Type 30/31/32/33/34 into both the first AND second rows, then drag down. Excel will notice your pattern (or lack there of) and know not to increment the 34.

Secondly, you may want to reconsider having the same information 8000 times. Wouldn't it be better to have it in one spot in case it changes? If you reference it in a formula you can use the an absolute reference (cell address with the $col$row format) or even better use a NamedRange and give the cell some meaningful name. That being said, it is conceivable you do need 8000 copies, but likely not.

share|improve this answer

Another way: Type or copy your desired value to the top row of the relevant column, then copy it to the second row. Highlight both rows and double click the autofill box at the bottom right of the second row. This will fill the column without turning it into a series, provided you have contiguous data in the column to the left. Regards, Martyn

share|improve this answer

Rather than just complain, I found a solution--but only through trial and error. Office Online Help wasn't of any assistance.

Just one cell with the value or formula is sufficient. (Those solutions did seem quite clunky, since some formulae are long and complicated.)

  1. Copy the formula you would ordinarily try to "fill" with the drag 'n' drop handle.
  2. Right click to paste, and several options are available, including advanced options.
  3. The paste option you want is called "paste link," and it looks like an infinity symbol.

All best, folks, and thanks for the earlier help.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.