Calc Quickies

When I have an advanced class that races through the material and gets done early, hungry for more, this is what I teach them.

Quick Fills: Formulas

If you use Excel you know that if you drag a formula, or just about anything, down with that little handle in the lower right corner, you can get what you want without having to type it in.

Same with OpenOffice.org. If you want to have a formula appear in 45 cells of a column, just drag down.

This illustration shows the whole series of steps; click to get the big picture.

Quick Fills: Things That Are Always in Order

Let's say you need one column that start with 1901 and go through 2005. You could type it in, or, since OpenOffice.org knows about numbers and years and all that, you can let the program do it.

Just type 1901 or whatever the starting year is, and drag down.

You can try this for things that aren't in order--just type for instance Beer, Pizza, Ice Cream, select those cells, and drag.

This also works for months, days, and anything else under Tools > Options > Calc > Sort Lists.

Can you make your own sort lists? Excellent question! Of course you can.

NOTE: In StarOffice 8 and OpenOffice.org 2.0, there is no Tools > Options > Calc. It just doesn't exist. The Help says it's there, and it should be there, but it's not. It does work in 1.x and StarOffice 7. So keep looking for it, and try it in 1.x or 7.0.

Making Your Own Sort Lists That Work With Dragging and Sorting

SEE NOTE ABOVE.

These are great. If you have a group of people or departments who need to be listed in a certain order, not alphabetic, or if you just want a quick shortcut for typing, use sort lists.

1. Choose Tools > Options > Calc > Sort Lists.
2. Type the items you want in the order you want them.
3. Click Add.
4. Click OK.

Now you can type the first item in the last and drag down or across in Calc to get the whole list, or use them when you sort.

Complex Numeric Fills

If you want to fill a column or row with something complex like a specific increment or a geometric increment, you'll want to use the fill window.

1. Select the range of cells you want the fill to be in.
2. Choose Edit > Fill > Series.
3. Select the values you want, like the starting value and a specific increment of 7.25.
4. Select the options you want, like Linear or Growth.
5. Click OK.

Selecting From a List

Let's say you're writing up a budget with lots of different rows, but the value in the Department column is always going to be Research and Development, Technical Publications, or Marketing and Advertising. And not in a predictable order so that sort lists are an option. You could type it all repeatedly, you could copy and paste, or you could use the lookup list option.

1. Click in the first cell in the column where you have repeated values.
2. Type Ctrl + D.
3. Select the item you want.

 

Comments

[Alfonso Baqueiro]

About the process of filling a column with formula draging the cell corner, what about when there are thousand of records, is not much practical.

[Solveig]

Hi Alfonso,

In that case you click on the first cell, hold down Shift, scroll down and select the last cell, then choose Edit > fill > down. You could go under Tools > Customize, Keyboard to set up a keyboard shortcut for edit > fill > down.

Solveig

[Albert]

It's: Tools > Options > OpenOffice.org Calc > Sort Lists