Office Settings & Configuration

Create an Excel Dropdown with Descriptions and Numeric Values

Steve KSteve K Nov 18, 2026 868 views

Question details

Create a dropdown that shows a description and its kilometer value while retaining the numeric value for calculations.

Create an Excel Dropdown with Descriptions and Numeric Values
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user wants to select a route from a data validation list and use its corresponding distance in calculations.
Observed behavior
The dropdown should display both the route description and distance, while the stored result is a numeric kilometer value.
Before you start

Enter route descriptions in E6:E8 and their numeric kilometer values in F6:F8. Make sure each combined dropdown entry is unique and the values in column F are numbers.

Solution 1Recommended

Use VBA to replace the selected dropdown entry with its numeric value

Use this method in desktop Excel when the dropdown cell itself must contain only the kilometer value.

An Excel data validation list stores the selected source text; it cannot independently display a label and store a different value. A worksheet event macro can replace the selected text with the corresponding number after selection.

1
Create the list entries

In G6, enter =E6&" : "&F6&" km" and fill the formula down through G8. The cells will show entries such as Route A : 20 km.

2
Set up data validation

Select A2, open Data > Data Validation, choose List, and set Source to =$G$6:$G$8. On the Error Alert tab, clear Show error alert after invalid data is entered, then select OK.

3
Add the worksheet macro

Right-click the worksheet tab, choose View Code, and paste this code into that sheet's code window: Private Sub Worksheet_Change(ByVal Target As Range) Dim idx As Variant On Error GoTo HandleError If Target.CountLarge <> 1 Then Exit Sub If Intersect(Target, Me.Range("A2")) Is Nothing Then Exit Sub If IsEmpty(Target.Value2) Then Exit Sub idx = Application.Match(Target.Value2, Me.Range("G6:G8"), 0) If IsError(idx) Then Application.EnableEvents = False Target.ClearContents Application.EnableEvents = True MsgBox "Select an entry from the dropdown." Exit Sub End If Application.EnableEvents = False Target.Value2 = Me.Cells(5 + CLng(idx), "F").Value2 Application.EnableEvents = True Exit Sub HandleError: Application.EnableEvents = True MsgBox "Could not update the dropdown value: " & Err.Description End Sub

4
Save and test

Use File > Save As to save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Enable macros when prompted, then select an entry in A2; the cell should change to its numeric kilometer value.

Use VBA to replace the selected dropdown entry with its numeric value
The selected label is replaced: After selection, A2 shows only the number, not the route description. The macro checks entries against G6:G8; if macros are disabled, the replacement and this check will not run.
Work with dropdown values

Create a descriptive dropdown and numeric lookup in WPS Spreadsheet

Use a data validation list to show each route and distance, then return the numeric kilometer value in a separate cell with a lookup formula.

  1. 1. Enter the source data: In WPS Spreadsheet, enter route descriptions in E6:E8 and numeric kilometer values in F6:F8.
  2. 2. Combine the labels: In G6, enter =E6&" : "&F6&" km" and fill the formula down through G8.
  3. 3. Add the dropdown: Select A2, open Data > Data Validation, choose List, and use =$G$6:$G$8 as the source.
  4. 4. Return the numeric value: Enter =IFERROR(INDEX($F$6:$F$8,MATCH(A2,$G$6:$G$8,0)),"") in B2, then choose a route in A2.
Show route descriptions and kilometer values together in a dropdownReturn a numeric value for calculations without a macroWork with Microsoft Excel XLSX and XLS formatsUse a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Can an Excel validation list display a description but store a different value in the same cell?

Not by itself. Data validation stores the selected source text. Use a worksheet VBA macro to replace it with the numeric value, or use a formula in a separate cell.

Why does the lookup formula return a blank?

Check that A2 contains an entry from G6:G8 and that the descriptions in the source range are unique. The IFERROR formula returns a blank when it cannot find a match.

Will the VBA method work if macros are disabled?

No. Save the file as an .xlsm workbook and enable macros in desktop Excel when prompted. Without the macro, the selected text remains in the cell.

Can I show a description without adding it to every dropdown entry?

Yes. Select the validated cell, open Data > Data Validation, and use the Input Message tab to show guidance when the cell is selected. The message is not stored as the selected value.