Glam Prestige Journal

Bright entertainment trends with youth appeal.

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?

6

6 Answers

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.

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:

  • Shortcut Keys in Excel 2000 through Excel 2007:
  • Keyboard shortcuts (by categories):
  • Charlie’s List, or “214 Keyboard Shortcuts”:
  • Microsoft Excel 2007 keyboard shortcuts:
  • Microsoft Excel Shortcuts:

Good luck!

3

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

0

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.

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.

Say if you have a Function, you want to check if cells in Column B exist in Column F.

=COUNTIF(F2:F2145,B3)

The problem is when you set the first few cells formula's:

=COUNTIF(F2:F2145,B3)
=COUNTIF(F2:F2145,B4)
=COUNTIF(F2:F2145,B5)

It then generates the next one as

=COUNTIF(F5:F2148,B6)

We only want to increment on the B column. So I just used a "Named Range" for the hardcoded range in my incremental formula:

=COUNTIF(MyNameRange,B2)
=COUNTIF(MyNameRange,B3)

Related Journals