How to Display Currency Values in an Excel Combo Box
Question details
The user needs to display the actual currency value selected from an Excel combo box instead of its numerical index position.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating an interactive financial form or spreadsheet utilizing a combo box to select and display currency values.
- Observed behavior
- The linked cell of a Form Control combo box returns the position number (index) of the selected item, rather than the expected currency value.
Ensure that the Developer tab is enabled in your Excel ribbon, as you will need it to insert combo boxes and access control properties.
Use the INDEX Function for Form Control Combo Boxes
Form Control combo boxes output an index number to the linked cell. You can use the INDEX function to translate this number back into the corresponding currency value.
A Form Control combo box is lightweight and widely used, but it naturally feeds the item's list position (e.g., 1, 2, 3) into the Linked Cell instead of the text itself. To resolve this, you must reference that position against your original data range using the INDEX function.
Go to the Developer tab, insert a Combo Box (Form Control), and right-click it to select 'Format Control'. Set the 'Input range' to your list of currency values and the 'Cell link' to a blank cell (e.g., A1) that will hold the index number.
Select the cell where you want the actual currency value to appear. Enter the formula =INDEX(input_range, linked_cell). For example, if your currencies are in C1:C10 and your linked cell is A1, type =INDEX(C1:C10, A1).
Select the cell containing your new INDEX formula, right-click, choose 'Format Cells', and apply the Currency format to ensure the output displays correctly with the proper currency symbol.
Configure ListFillRange for ActiveX Combo Boxes
If you are using an ActiveX combo box, you can configure its properties to directly output the selected value to a linked cell without needing an extra formula.
Manage Combo Boxes and Formulas Efficiently in WPS Spreadsheet
WPS Spreadsheet provides robust developer tools and an extensive formula library, allowing you to insert interactive form controls and use advanced functions like INDEX to build professional financial dashboards.
- 1. Open WPS Spreadsheet: Launch your workbook in WPS Spreadsheet and navigate to the Developer tab on the top ribbon.
- 2. Insert a Form Control: Click 'Insert', choose the Combo Box icon under Form Controls, and draw it onto your sheet.
- 3. Link your Data: Right-click the combo box, open the format properties, and assign your currency input range and a linked cell.
- 4. Retrieve the Value: Use the =INDEX() function in your desired output cell to cross-reference the linked cell and successfully display the currency value.
Frequently Asked Questions
Why does my Excel drop-down show a number instead of the text I selected?
Form Control combo boxes are designed to output the item's sequential position index (e.g., 1, 2, 3) to the linked cell, not the text of the item itself. You must use a lookup function like INDEX to translate this index number back into the actual list value.
Can I format the items inside the combo box drop-down list to show currency symbols?
The text displayed inside a standard Form Control drop-down list usually strips out specialized cell formatting like currency symbols. To ensure symbols appear, either format the source cells strictly as Text including the symbol, or simply format your final output cell (where the INDEX function is located) as Currency.
What is the difference between a Form Control and an ActiveX combo box?
Form Controls are generally simpler to set up, highly stable, and compatible across platforms, but they output an index number. ActiveX controls offer more granular customization and can output direct values to linked cells, but they are exclusive to Windows and can sometimes trigger macro security warnings.
