Adding custom lists to Excel and OpenOffice.org

In the last post I demonstrated how to easily enter lists of numbers, days of the week and months of the year in Excel and Calc (the OpenOffice.org spreadsheet application). Both programs offer the option to add custom lists to this feature. In this post I will show you how to add the colours of the spectrum as a new list.

Excel 2003 and earlier
Open up Excel and type the colours of the rainbow into one of the columns, then select the cells as shown below.

Pull down the 'Tools' menu and select 'Options', then go to the 'Custom Lists' tab.

You should see an entry in the 'Import list from cells' box. Click on the 'Import' button to the right of this to add the list.

The colours list will appear in the 'List entries' box. Rather than type the entries into cells, you could just type the list in this box, pressing Enter after each item, and then click the 'Add' button to save the list.

Click 'OK', and the list is available to use. Simply type 'Red' in a cell and drag the handler down to automatically fill consecutive cells with the other colours.


Excel 2007 and later
Follow the previous instructions, except to open the 'Options' box click the Microsoft Office Button (shown right), and then click the 'Excel Options' button.

On the 'Popular' page, click the 'Edit Custom Lists' button and continue as above.

OpenOffice.org Calc

Type the list of colours into a column of cells and select them as shown.

From the 'Tools' menu, select 'Options', then click the + next to 'OpenOffice.org calc' in the list, to expand its entries, then click on 'Sort Lists', as shown.

You should see an entry in the 'Copy list from' box. Click on the 'Copy' button to the right of this to add the list.

Click 'OK', and the list is available to use.

As with Excel, you can enter a list manually by clicking the 'New' button on the 'Sort Lists' option box, typing in the entries, and then pressing the 'Add' button.