Data Validation Using DropDown List – LibreOffice Calc


This tutorial would show how to use a basic range based data validation in LibreOffice Calc using a drop down list.

While accepting data in a Calc sheet, it is possible to provide the user a choice of items in a form of a drop-down list. Users can select the desired value from the drop-down instead of custom text. This eliminates the possibility of error, duplicate in the data entry/collection process.

Let’s look at how this can be done using an example.

Data Validation using Dropdown

Say, we want users to select their favorite fruits in cell B1 from a list of fruits which is F1:F4.

Data Validation from List - Source
Data Validation from List – Source

Select cell B1 and from the menu select Data -> Validity.

In the Validity window, Criteria tab, select “Cell Range” against Allow field.

SEE ALSO:  Create, Save, Open and Close a Workbook in LibreOffice Calc

Check Show selection list and un-check allow empty cells.

In the source field, click the small Shrink button at the right and select the F1:F4 range.

Validity Settings
Validity Settings
Select Range as Source
Select Range as Source

Press Ok.


In cell B1, you can see a small down-arrow button. Click the button and the drop-down would show the list of values from the source range.

Now, you can select a value from the drop-down list.

Data Validation - List
Data Validation – List

If the user wants to type a different value in cell B1, the data validation would give an error by default saying it is an invalid value.

Looking for something else?

Read our complete tutorial index of Calc, Writer, Impress and Draw.

Join our Telegram channel and stay informed on the move.



IT professional by profession and founder of Loving Linux and other technologies since 2002. I believe in open source and its philosophy. Follow me on Twitter or email me.


  1. I want to use a dynamic list in the pick list that changes as data is added to the list but I need to eliminate duplicates in the list. ow do I dynamically remove the entries in the drop down list. Is there a formula that will achieve this?

  2. Want to be able to specify a default value in the cell that is validated (eg if different VAT rates are available 0% 6% 12% 25% – the 25% as default value)

  3. In LO 6.1.5, if I used ALT-DownArrow in a constrained cell to show the drop-down list, I could then use the arrow keys to navigate to the desired value and press ENTER to select it. I’ve just upgraded to 6.3.5 and now as soon as I use an arrow key to change the current value it is auto-selected. Do you know if this was an intended change in behaviour, or a bug? If intended, is there a way of reverting to the previous behaviour, as the new behaviour is really annoying.

  4. Hi Arindam,
    Useful howto, Thanks!
    Is there an option/possibility to make this multiple choice selection?
    I have defined a column with all possible values which can be used but I’d like to be able to choose one, none or many of those being in the range. Is there any switch allowing for that?
    Or different way to achieve that?

    • No. When you are creating a dropdown, by design it gives users a choice to choose only one value. That’s how drop-down works everywhere. If you want to allow users to choose multiple values then your design must change. You might rethink your data organization in the sheet. You might want to provide multiple cells with the same drop-down values to give users the option to choose multiple values.

  5. Hi, can I get a formatted values from the source?

    I mean, if I have as origin cells: ‘$10,500; $20,300; $30,100’
    when I click on the arrow to choose the values, I get these values:
    10500; 20300; 30100

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.