This tutorial will 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 with 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 errors, and duplication in the data entry/collection process.
Let’s look at how this can be done using an example.
Table of Contents
Data Validation using Dropdown
- Say we want users to select their favourite fruits in cell B1 from a list of fruits which is F1:F4.
- Select cell B1, and from the menu, select
Data > Validity. - In the Validity window, Criteria tab, select
"Cell Range"againstAllowfield. - Check the
Show selection listandun-checkallow empty cells. - In the source field, click the small Shrink button at the right and select the F1:F4 range.
- Press Ok.
Results
In cell B1, you can see a small down-arrow button. Click the button, and the drop-down will show the list of values from the source range.
Now, you can select a value from the drop-down list.
If the user wants to type a different value in cell B1, the data validation will give an error by default, saying it is an invalid value.
You can also configure the error message for invalid values from the “Error Alert” tab of the validity window.
Wrapping Up
I hope in this guide you learned the basics of data validation in LibreOffice Calc list.
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?
Make an vb script that removes the old value an insert the new values in the cell each time.
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)
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.
I just checked it 6.3.5. Must be a bug. Which OS you are using?
I’m using Windows 10, and yes, it is a bug… I found https://bugs.documentfoundation.org/show_bug.cgi?id=130325 after posting the above. I couldn’t find an obviously-available pre-built 6.3.6, but I found 6.4.2 and that fixed the problem.
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?
TIA
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.
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
Try formatting the cell it displays in to currency. That might do it.
I’m far from an expert with this stuff but I believe that should work.
Hi, I need to connect a dropdown list item to a specific column. So, when I select “income” in a dropdown list, I want that selection to jump to my “income” column ready to input my businesses income for that day. Also, if I select “cost of goods” in that dropdown list, I want it to jump to my expenditure column, is this possible? Thank you kindly.
This requires macro programming to dynamically change focus.
Hello, Can I use it in shared workbook. Excel disable data validation for shared workbook
Try to do it in .ods file and see if it works. I do not have network to test it out.
is it possible to add these dropdown lists to a calc form? how? thanks.
You can add a VB form in a spreadsheet and program to add values from cells. It requires programming.
Hello,
Help me creating a data base in Excel. For example whatever i do in sheet 1 or enter any figure in any specific cell, it should be saved in Sheet2.
And again i enter any different value in that specific cell in sheet1, then it should also be saved in sheet 2… When i move to sheet 2 I should see both vale in a row .
Thanks
The quickest way to do it is add formula to mirror each cell from Sheet1 to your Sheet2. For example, you can add below in A1 cell of Sheet2:
=$Sheet1.A1What if I want labels on the drop down list values?
Labels are not supported in LibreOffice that way. You can use a helper column to do that.
I used to be able to enter values that were not in the dropdown list, but with the new version 24.2.5.2 when I enter a custom value, it always disappears. Is this a bug or do I need to do something different?
We have exactly the same problem, we used the dropdown list but also custom values, and now the custom data disappears. Thank you
I am having the same problem as Nirol & Bruno. I used to be able to allow the entry of a value not in the original list to be entered in a dropdown cell and there y added to the list. In my case it is a long list of names so periodically need the ability of the input to be added to the list instead of having to go to the list and add the new name before can complete the day’s report. There must be a workaround. If not then PLEASE add that option back in the dropdown cell options