SMBs

DIY: Add drop-down lists to LibreOffice spreadsheets

The addition of drop-down lists can make your LibreOffice Calc spreadsheets more flexible and powerful. Get instructions on adding them in Calc.

If you want to be a LibreOffice Calc power user, you should follow my step-by-step tutorial on two possible ways to add drop-down lists to the program for faster spreadsheet creation and easier data entry.

Approach #1

This approach is rather limiting. One of the things you can do with Calc's Data Validity tool is create drop-down lists by following these steps.

1. Open the spreadsheet you want to add the drop down to and select the cell where you want the drop-down to be placed.

2. Open the Data Validity tool by going to Data | Validity. Once this new window opens, select List from the Allow drop-down (Figure A). Figure A

There are multiple ways of getting the data needed for the drop-down.
3. In the text area, enter the choices that will be displayed in the drop-down list (Figure B). You should enter these choices one entry per line. Figure B

I'm creating a drop-down for hardware that can be used within my spreadsheet.

4. Click OK. Make sure all of the check boxes are selected before you continue.

Your drop-down list should be available in the cell you selected before you began the creation process (Figure C). Figure C

These entries should look familiar -- we just created them.

You can copy and paste this drop-down list throughout your spreadsheet as needed. If you want to edit the contents of that drop-box, you have to go back into the Data | Validity tool and edit the list manually.

Approach #2

This alternative is as simple as the first approach, though it does require an extra step. Also, this option is the more flexible method.

1. On a separate sheet, create the list of items you want to use for the drop-down list (Figure D). Figure D

We're creating the same drop-down we did with the first method.
2. Define the range you just created. Now we need to create a name for the range of cells. First, highlight the cells with the new data you just created and then go to Data | Define Range. When the new window pops up, enter a descriptive name (such as Hardware) in the text box (Figure E). Figure E

When you enter the name, the cell range should appear. (Click the image to enlarge it.)

3. Click OK.

4. Go back to the original sheet and go to Data | Validity.

5. In the Allow drop-down, select Cell Range and then type the name of the Cell Range you defined above (in this case Hardware).

6. Click OK.

The drop-down should now be available to the cell selected.

The nice thing about this second method is that it's easy to change the items in the drop-down list. To do so, go to the other sheet (where you entered the items in cells) and modify the items you want to change. One caveat is that you cannot add to this list; the only way you can manage this is to include extra cells when you make your selection. You should remember how many extra cells you added, because you cannot go beyond that number when adding data for the drop-down.

About

Jack Wallen is an award-winning writer for TechRepublic and Linux.com. He’s an avid promoter of open source and the voice of The Android Expert. For more news about Jack Wallen, visit his website getjackd.net.

5 comments
ygehlot
ygehlot

Hi,

I was just wondering if drop-down could fill subsequent cells of the rows. I have bank account details of some employees in one sheet. In another sheet, there is one statement. I want the names by drop down and when I select the name, other details in subsequent cells should fill itself from the details given in previous sheet. Please let me know if it is possible.

steveedmonds
steveedmonds

With this method you can still copy and paste any value you wish into the cell with the list, the value does not need to be in the list.

sarveswaran_m
sarveswaran_m

If all cells in a column need to have drop-down lists, click on the column header & follow the procedure mentioned here.

dianemaryknollolanda
dianemaryknollolanda

I just wondering how to make entries per line in creating the drop down list in Approach A. Please explain it further. Thank you very much.

mhatherly
mhatherly

Nice article. Here is something I do: At the bottom of the validation list , in the cell after the range ends, I key in something like "--End of List ---" to remind me that when I fill the empty slots and need more I need to also expand the named range.