Dropdown lists and checkboxes in cells

What it is

Cells with a List or Checkbox rule from the Data validation panel show not just text but a selection control: coloured tags, a dropdown arrow or a checkbox. The chosen option can be spread with autofill and paste.

How to use it

1. Click the cell with a list: it is selected. Click the value or the arrow again, and the options open. The arrow keys and moving the focus do not open the list; Enter on the list button does.
2. Choose an option. If there are more than 20 options, a search appears at the top.
3. For several values, tick the ones you need and click Done. Escape cancels an unfinished selection. Escape also closes the list, and the selected cell stays selected.
4. A checkbox toggles with a click, Space or Enter.
5. To spread the chosen option, drag the autofill handle from the corner of the cell. The rule, the chosen options, the colours and the formatting are copied together. One undo returns the original range.
6. An ordinary paste of a copied cell with a list fills the whole selected range, row or column. The copied block repeats if the size of the selection is a multiple of its size.

Limits

• In Chips, each option is a coloured tag. In Arrow and Text, the colour of the chosen option fills the whole cell, including frozen and merged cells and the print. In Text there is no arrow.
• The horizontal and vertical alignment of the cell applies to all three displays. The value itself is centred, and the arrow stays on the right. In a narrow cell the value shifts only as far as needed so as not to overlap the arrow; only what truly does not fit is shortened. The alignment is kept when the display changes, when the selection is cleared, when cells are merged, and after a reload.
• With several values, the common background is set by the first chosen option in the order of the rule. Conditional formatting takes priority. An empty or free value does not get an option’s colour.
• Text on a coloured background gets a contrasting colour. Clearing the colour removes the fill of the option both in the cell and in the palette of the editor. The cell’s own formatting is kept, and it shows again after the colour is cleared, after a choice is made, when the rule is deleted, or when the display is switched to Chips.
• Autofill repeats the options of the list, including the labels “456”, “001” and “SKU 08”, without increasing the digits. Several source cells repeat in order; the set of chosen tags is copied in full. In neighbouring ordinary columns, numbers and text continue as before.
• When you paste again, neighbouring areas of one rule are merged. Scattered areas are split into groups of 100 per rule. If the sheet would have more than 1000 rules, the paste is refused. The repeat when pasting covers up to 200 000 cells.
• The options menu and the highlight of each item adjust to the width of the content. Long names wrap inside the menu; on a narrow screen the menu does not go beyond the edges. In a list with search, the search field stays wide enough.
• In a spreadsheet opened for viewing only, the list shows the value but does not open, and the checkbox does not toggle.
• In an XLSX file, option colours and several values are not kept. Chips become Arrow on loading, and Text is kept.

If something goes wrong

What you see on the screen	Why	What to do
The first click did not open the list	The first click only selects the cell	Click the value again, or press Enter
Too many rules. Select a smaller range.	Repetition creates more than 1000 rules	Select a smaller range
The selection is larger than 200 000 cells — pasted once, from the top-left corner	The selection is larger than the repeat limit	Select a smaller range and paste again
After loading from XLSX, the options have no colours	The XLSX format does not store option colours	Set the colours in Data validation