Wednesday, 5 March 2008

How to quickly enter lists of numbers, dates and times in Excel

Excel spreadsheets often have a column or a row of incremental data. That is, information in the form of a list of numbers (1, 2, 3, 4, etc), times (09:00, 10:00, 11:00, etc), the names of months or the days of the week. You could type all this information into the row or column yourself, or you can let Excel handle it for you.

To try this out open Excel and type 1 in any cell, and 2 in the cell below it. For lists of numbers you need to enter a couple of numbers so that Excel knows where to begin and what interval to use.

Select both the cells (by hovering the mouse pointer over the top of one, holding down the left button, moving to the other cell and letting go).

You will notice that there is a small square in the bottom right hand corner of the selection box. If you hover the mouse pointer over this square the pointer will change to a + symbol. If you hold down the left mouse button and drag downwards Excel will fill in the cells continuing the series of numbers.

You can do the same with larger increments too. The images to the left show the same process being used to generate is a list of numbers with an interval of 10.

But that is far from all you can do with this process. You can drag these lists in any direction so that you can create rows as well as columns. Also, you are not limited to numbers; you can produce lists of months, days of the week, and times. I have included a video in this post showing this feature of Excel in action creating a few of these lists.


Catherine said...

Wow, this is a good tip! I've been using Excel for about 12 years and never knew this one (and have been using various other methods of populating cells instead, which take much longer). Thanks

Phos Tan said...

thx for the great tips.

Manick said...

Good information. Thanks.

Stepterix said...

Thanks for the comments