How to create a series, or just repeat a cell, in OpenOffice Calc spreadsheets

Never retype when you can do it quicker!

In OpenOffice.org Calc spreadsheets, as in Excel, you can click on a cell, find the little black handle in the lower right corner, and drag it up, left, right, or down, to get additional content.

 

If it's a number when you drag you'll increment by 1. If it's a value like the days of the week or names of months, it will increment as in January February March.

 

If it's a formula where you've just typed it normally without absolute  references, then drag it down through a column or row, it will repeat the formula as shown. Here's the first cell with the formula:

  

Drag down and you get similar formulas referencing the next-door cell, not the original cell.

If it's anything else it will just repeat.

If it's a number or day/week/month (not a formula) that usually increments, and you just want it to repeat, you can:

- Select the cell containing the number, as well as all the cells that you want it to repeat into, and choose Edit > Fill > down or right or whatever fits the cells you've selected

- Or you can hold down Ctrl when you drag and it'll do the same thing: repeat instead of incrementing. This works for incrementing months/days etc and numbers.

Note: for formulas, if you want them to not adjust as you drag, you use absolute references as in Excel. $A$4 instead of A4 for instance.

Comments

[Eddie]

I often use long complicated series, like 1.2.1, 1.2.2, 1.2.3 etc. Excel recognizes most of them, but not Ooo Calc. Do you have any tips how to format the cells to make this possible?

[Solveig]

Hi Eddie,

That's a tough one. I would have to refer you to the Number Format Codes help topic.

Solveig