Defining Cell Drop-downs

You can add a drop-down menu in the cell to help users select the data easily and quickly. This feature is helpful in creating input form controls, advanced structured forms, and other interactive forms and dashboards within the spreadsheet.

You can configure a drop-down cell with a list of predefined values (using a list drop-down), calculated values (using a calculator drop-down), calendars and events (using the date time picker, month picker and time picker drop-downs), select a color (using the color picker drop-down), create range sliders (using a slider drop-down), and automatically repopulate the next available choices based on the earlier choice (using a workflow list drop-down).

You can define the following cell drop-downs:

Cell Drop-down	Description
Calculator	The calculator drop-down allows you to compute values and populate the evaluated result in the cell.
Color Picker	The color picker drop-down allows you to choose a specific color to populate the cell value with the hex code of the chosen color.
DateTime Picker	The date time picker drop-down allows you to set date and time to populate the evaluated result in the cell.
List	The list drop-down allows users to choose a specific item from the drop-down list to populate the cell value.
Month Picker	The month picker drop-down allows you to configure a specific month in a year to populate the evaluated result in the cell.
Slider	The slider drop-down allows you to add a dynamic range slider in a cell that populates the current value in the cell.
Time Picker	The time picker drop-down allows you to configure a specific time to populate the evaluated result in the cell.
Workflow List	The workflow list drop-down allows you to select a workflow item easily and quickly to populate the chosen value in the cell.
Multicolumn List	You can create a drop-down list as well as an editable area which will allow you to type in the value as well as choose from a displayed list. You can scroll the drop-down list and select an item. The item value will return the current cell value as the object type, and when you re-open the drop-down list, the selected item will be highlighted.

Create a drop-down list

1. In the Home menu, select Styles > Cell Editors > Cell Dropdowns and then select your drop-down type.

2. The Format Cells dialog box opens with the Cell Button tab selected. All drop-down features use the same parameters. Some drop-downs use additional parameters. The other tabs provide the standard formatting parameters. Enter the Cell Button parameters as described below.

3. Click OK.

Cell Button Parameters

Parameter	Description
Command drop-down (button list on the left)	Displays the button selected in the Command drop-down list. It reflects the value of the item you selected in the Cell Dropdowns menu. Click Add to add more buttons and select the command you wish to assign to each button.
UseButtonStyle	Select this option to give the cell a button look.
Position	Specifies the position of the drop-down in the cell.
Width	Set the width of the drop-down in the cell.
Caption	You can enter text here to describe the drop-down.
BackColor	Specifies the background color of the drop-down.
Hover BackColor	Specifies the background color of a drop-down item when your cursor hovers over the item.
Image Type	Specifies the icon type on which the user clicks to view the drop-down. By default, Dropdown is selected (in form of a down arrow). You can change the icon type by selecting one from the list.
Visibility	Specifies if the drop-down is always visible, or on selection or edit only.
Command	Has the type selected by default according to your Cell Dropdowns menu choice. You can change the command if you made the wrong selection without having to reuse the menu. When Color Picker is selected, click the ellipsis button on the right to display the Color Picker dialog box. You can edit theme colors and standard colors using the color drop-downs. When List is selected, you can click the ellipsis button on the right to display the List dialog box. The Multi-select option allows you to select multiple items in the list. The Value Type is String by default. Array allows you to select multiple values in a data source. You can edit the list structure as needed by hovering your mouse next to an item and selecting the desired option. When Month Picker is selected, click the ellipsis button on the right to display the Month Picker dialog box. You can select the start year, the stop year and the height. When Slider is selected, click the ellipsis button on the right to display the Slider dialog box. You can define the minimum and maximum values, and the marks used as reference for those values. When Time Picker is selected, click the ellipsis button on the right to display the Time Picker dialog box. You can define the minimum, maximum and step (time interval) in hour, minute and second. When Workflow List is selected, click the ellipsis button on the right to display the Workflow List dialog box. You can select the order in which the item appear in the drop-down list and the action associated with each item once it is selected in the list. You can also add a new item and select the appropriate action. When Multicolumn List is selected, click the ellipsis button on the right to display the Multicolumn List dialog box. Select the data source. Click Add to add Binding Info Items. Back in the spreadsheet, you will notice that when you select a value from the drop down list, it places [object Object] in the cell rather than the value that was selected from the list. To fix this, click the arrow in the Numbers menu to open the Format Cells dialog box. Choose Custom and enter the appropriate type using the PROPERTY parameter. In our example below, we want to display the month value, so we enter =PROPERTY (@, "Month")
CaptionAlign	By default, the caption appears to the right of the drop-down icon. You can change the caption alignment and put it on the left.
Image Src	You can use an image file instead of the default icon by clicking this button. This feature is not enabled for this release.
Image Width and Height	Specifies the icon size inside the cell.

Drop-down Examples

Calculator drop-down

Color picker drop-down

Here we change one of the theme colors to turquoise.

After saving, the turquoise color is available from the theme colors when you click the cell.

The hexadecimal value of the color is then displayed in the cell when selected.

Date Time picker drop-down

Here we selected the UseButtonStyle parameter and blue as Hover BackColor.

List drop-down

Example after clicking the ellipsis button.

Here we clicked the Edit option at the level of NA10.

This is the result of a drop-down list.

Month picker drop-down

Example after clicking the ellipsis button.

This is the result of a month picker.

Slider drop-down

Example after clicking the ellipsis button.

In this example, the minimum value is 0 and the maximum is 100. We used 0,50,100 as marks.

Time picker drop-down

Example after clicking the ellipsis button.

In this example, we selected 30 minutes as the step (time interval) for the drop-down. The time is in 24-hour format.

Workflow list drop-down

Example after clicking the ellipsis button.

Since New is the first item on the list, it is the first choice that appears when clicking the list.

Once New is selected, the next item available is Open, and so on.

Multicolumn list drop-down

Example animated with steps.

Example with a caption (Revenue by product).

Edit a cell drop-down

1. Right-click the cell.

2. Select Edit Cell Drop-down.

3. Make the changes as described in the above-mentioned procedures.

Remove a cell drop-down

1. Select the cell.

2. In the Home menu, select Styles > Cell Editors > Cell Dropdown > Clear CellType.