Skip to content

start:

 

 

How to utilize Sort Lists and AutoFill



Provided from
the OpenOffice.org Documentation Project



Table of Contents

1. Introduction

These automations make data insertion easier in Calc. For instance, when you type Monday into a cell, there is a mechanism to allow inserting all the days of the week. The same mechanism works for month lists, as well. OpenOffice.org allows you to automatically insert the most used Sort Lists.

It's the same for a number or date series. With a single click or dragging action, you will be able to increase the series of numbers or years. Very practical, isn't it?

2. Sort Lists

Here's an easy example: Type the word January in a cell, select the cell (it has to be surrounded from a thick rectangle with a small square on the lower right corner), and click on that square without releasing the mouse button and drag the mouse.

list example 1


Magic! By dragging the mouse, the words are automatically displayed in a yellow floating info box. Stop at March and the cells will be automatically updated.

list example 2

 

 

 

These lists are already recorded in the software; there are a lot of them, and of course, you can record your own lists. Let's see how this is possible.

  • In Tools-Options-Spreadsheet-Sort Lists, let's discover those lists that are already present


There are 4 default recorded lists. In the image above, there is an additional custom list.

You can insert them from the left, right, upwards, or downwards. Of course, the order will be respected: If you start with Monday then drag your mouse upwards, the next displayed day will be Sunday. Same thing, if you drag your mouse to the left.

  1. Now, we'll create our own sort list
  2. Type the list in your sheet,
  3. Select it by highlighting,
  4. Choose Tools-Options-Spreadsheet-Sort Lists,
  5. In the dialog window that it will appear, you'll see the 'Copy' button. Next to this button there is a text box, named 'Copy list from'. You should read there the sheet name and the cells range you have previously selected,
  6. Click on the 'Copy' button,
  7. Then, your custom sort list is listed in the main Sort Lists text box!

list example 4


Now, you are able to use it in your sheets, in every way, being sure that the order will always be respected. In fact, you can start from whichever element of the list you choose.

If you don't wish to keep that sample list:

  • Select it in the Sort Lists windows and click on the 'Delete' button to remove it.

3. AutoFill

The AutoFill is similar to the procedure described above.

  1. Select a cell range,
  2. Under Edit-Fill; Choose 'Series...' ,

filling window

  1. In the Direction section, determine in which direction you wish to fill your cells
  2. For the time being, in Series Type, choose Linear,
  3. In Start value, type '2'; in End value, insert '16'; and finally in Increment, type '2'. We have set up to fill the cells with numbers from 2 to 16 incremented progressively by 2.
  4. Let's see the result:

filling example 2






Wonderful! We have filled 8 cells only, but try to imagine if there were 3000. What a time savings!

Let's analyse the other filling options:

  • 'Growth': it's similar to linear, but this option allows not selecting the exact range of cells to be filled; though it is obligatory to insert the End value. Even if you have selected only a few cells, the filling action will continue until the End Value has been reached, while the linear mode respects the number of cells you have selected,
  • 'Date': gives you the access to the section 'Time Unit'. It's an explicit choice, you have to select what you wish to be displayed. Remember to fill the fields 'Start Value', 'End Value', and 'Increment'
  • AutoFill: will fill your cells from a start value.

Note : As far as the Date AutoFill is concerned, don't forget to format your cells with the format 'Date' in the format 'Number' as they are by default; otherwise, you'll get the wrong result.
After you have acknowledged your mistake after the AutoFill action, selecting the related cell range and formatting it correctly will resolve the issue.

A faster trick is:

  1. Type 1 inside cell A1,
  2. Type 3 inside cell A2,
  3. Select both cells by clicking on the first, hold down the [SHIFT] key, and press the arrow key,
  4. Click the little square on the bottom right corner of the highlighted area and drag downward with the mouse, your cells will be increased 2 by 2!

4. Credits

Author: Sophie Gautier

Thanks: Richard Holt, great proof reader and OOo contributor

Intgr by: Gianluca Turconi

Last modified: 10 February, 2002

Contacts:OpenOffice.org Documentation Project http://documentation.openoffice.org/index.html

Translation: Gianluca Turconi