Saturday, 13 March 2010

How to quickly enter lists in Excel and OpenOffice

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.

Calc, the OpenOffice spreadsheet package, works in pretty much the same way, except that there are no default intervals for times. So, if you enter 00:00 and drag the handler down selecting consecutive cells they will all also be filled with 00:00, but this is easily solved by entering 00:00 in the first cell and 01:00 in the second, then selecting both cells and dragging the handler down.

In the next post I will show you how to set up your own custom lists in Excel and Calc.


Stefano Izzo said...


Kevin Grieves said...

Thanks for letting me know. Unfortunately, I don't think that there is much that I can do about it, apart from leave a snotty comment on the blog.