2

I am using Microsoft Excel 2007 and have a few different cells. One cell is going to have the same data for every cell. I have another which will be numbered 1 - whatever, Example:

Column        A             |    B       | C  |     D

1      Data that will change   TRUE         1    3-May-2012

2      Data...                 TRUE         2    3-May-2012

3      More Data...            TRUE         3    3-May-2012

ETC.

How do I repeat data like the above column in Excel without having to manually cut and paste?

If this is not clear I will attempt to clarify.

  • Once you have a list of three or more items, and select that list to copy down, Excel is usually clever enough to figure out it's a repeating list. Depends on the data type though. Otherwise, Ctrl+D in the row beneath the value you're copying, is a little quicker than copy and paste. – user3463 May 4 '12 at 4:37
  • do you mean 'column' instead of 'cell'? just to check, you are only thinking of repeating columns C and D? – Reuben L. May 4 '12 at 6:25
2

If you click on a cell, in the bottom right of the cell is a black square. When you click on the square, drag it vertically or horizontally, and let go, Excel will autofill the cells in the range depending on your selection.

For example, if you wanted all of the cells in the first column to be the number 1, enter 1, click the black box, and drag down:

How to autofill

If you wanted to fill the numbers from 1 to 13:

  1. Enter 1 in the first cell
  2. Enter 2 in the cell below
  3. Highlight both cells
  4. Click the autofill box and drag down until the number displayed is 13: How to autofill incrementing numbers

There are a couple of ways to get all of the information filled down that you require.

  1. Enter the following in row 1 and 2:

1 Data1 TRUE 1 3-May-2012

2 Data2 TRUE 2 3-May-2012

  1. Highlight the whole thing and drag the autofill button down.

Solution 1 - autofill all

or

  1. Enter the following in row 1 and 2:

1 Data1 True 1 3-May-2012

2 Data2 True 2 3-May-2012:

  1. Enter the rest of the data in column 2.
  2. Highlight numbers 1 and 2 in first column and double click the autofill box (or you can drag down). This will fill until the last Data entered in column 2.
  3. Highlight from True to 3-May-2012 (C1 to E2) in the first 2 rows and double click the autofill box (or drag down the box). Again, this will fill until the last Data entered in column 2. Solution 2 - piece by piece

Hope this helps!

0

use excel tables (Insert > Table) with enabled Excel Options > Proofing > AutoCorrect Options > AutoFomat As You Type > Fill formulas in tables to create calculated columns

B: =TRUE

C: =row()-1 (because you need the first row to be headers)

D: ="3-May-2012" or =41032 and format the number as d-mmmm-yyyy

0

select the cell where you have entered the data already, move your mouse to the bottom right-corner the mouse will change to a cross, then click and drag to the bottom or to the many cells you want to auto fill or you can click & drag vertically.

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.