How to Add Drop-Down List in Word from Excel (2 Easy Ways)

Drop-down lists are an eloquent way to showcase options. It allows users to select effectively from different options. Excel has several ways to create a drop-down list. However, we often require the drop-down list from Excel to show in a text editor like MS Word. It helps to link the data of Excel to a Word file. In this article, we will show how to add drop-down list in Word from Excel.

How to Add Drop-Down List in Word from Excel: 2 Easy Ways

In this article, we will show two handy ways how to add drop-down list in Word from Excel. In the first method, we will use the Paste Special command in Word to add the list. Whereas in the second one, we will use a VBA code to do the task.

1. Using Paste Special Command of Word Document

The Paste Special command is a universal feature in all MS programs. Word is no different. Just like the Paste Special command in Excel, the command in Word also allows users to paste a link in the file from a different file. In this method, we will use this feature to do the task.

Steps:

• Firstly, select the cell that contains the drop-down list in the Excel file.
• In this case, it will be B4.
• Then, press Ctrl+C to copy it.

• After that, open the Word file.
• Firstly, go to the Home tab.
• Secondly, select the Paste option.
• Finally, click on the Paste Special command.
• As a result, a prompt will show up on the screen.

• In the prompt, first, select the Paste Link oval.
• Then, select the Microsoft Excel Worksheet Object under As.
• Finally, click OK.

• Now, change the list contained in the drop-down list in the Excel file.

• The content in the Word file will be changed accordingly.

Read More: How to Put a Large Excel Table into Word

2. Applying VBA Code

VBA codes allow us to do repeated work with a click of a button. It also helps us to perform complicated tasks in Excel. In this method, we will use a VBA code to connect a Word file to an Excel file and add a drop-down list in the Word file from the Excel file.

Steps:

• Firstly, create a list in the Excel file that you want to add as a drop-down list in the Word file.

• Then, open the Word file.
• Go to the Developertab.
• From the Controls group, select the insert Combo Box Content Control command.
• As a result, we will have a drop-down box on the screen.

• After that, select the Developer tab.
• From there, choose the Visual Basic command.
• Consequently, a prompt will be opened.

• In the Visual Basic window, first, choose the Tools option.
• Then, from the drop-down list choose the References option.

• In the References dialogue box, first, choose Microsoft Excel 16.0 Object Library option under Available References.
• Then, click OK.

• After that, go to the Insert tab.
• Select the Module option.

• In the opened module, write down the following code.
• Save the code.

Sub DropDownListFromExcel()
    Dim exlApp As New Excel.Application, xlWrkBok As Excel.Workbook
    Dim wkbkName As String, sheetName As String, LRow As Long, a As Long
    Application.ScreenUpdating = False
    wkbkName = "C:\Users\user\Desktop\Adnan\Drop-Down-List-in-Word-from-Excel.xlsx"
    sheetName = "VBA Code"
    If Dir(wkbkName) = "" Then
      MsgBox "The mentioned Workbook is not found." & wkbkName, vbExclamation
      Exit Sub
    End If
    With exlApp
      .Visible = False
      Set xlWrkBok = .Workbooks.Open(FileName:=wkbkName, ReadOnly:=True, AddToMRU:=False)
      With xlWrkBok
          With .Worksheets(sheetName)
            LRow = .Cells(.Rows.Count, 2).End(xlUp).Row
            Selection.Range.ContentControls(1).DropdownListEntries.Clear
            For a = 5 To LRow
                Selection.Range.ContentControls(1).DropdownListEntries.Add _
                Text:=Trim(.Range("B" & a))
            Next
          End With
        .Close Savechanges:=False
      End With
      .Quit
    End With
    Set xlWrkBok = Nothing: Set exlApp = Nothing
    Application.ScreenUpdating = True
End Sub

• Finally, run the code by clicking the triangle-shaped green button.

• Consequently, we will see that a drop-down list is added to the Word file from the Excel file.

How to Create Drop-Down List in Excel

We often need a drop-down list to present different options in Excel. Excel offers multiple ways to add a drop-down list. In this example, we will use the Data Validation command to create a drop-down list in Excel.

Steps:

• Firstly, select the cell where you want to add the drop-down list.
• In this case, the cell will be D5.
• Secondly, go to the Data tab.
• Thirdly, choose the DataTools group.
• Finally, from the Data Validation option select the Data Validation command.

• In the Data Validation dialogue box, first, choose List under Allow option.
• Then, select the range of data that you want to add to that list under the Source option.
• In our case, the range will be B5:B10.
• Finally, click OK.

• Consequently, we will have a drop-down list in our desired cell.

Download Practice Workbook

You can download the practice workbook here.

Conclusion

In this article, we have discussed 2 ways how to add a drop-down list in Word from Excel. After going through this article, users will be able to present a list from an Excel file as a drop-down list in a Word file.

Related Articles

• How to Insert Excel Table into Word
• How to Copy Table from Excel to Word
• How to Paste Excel Table into Word Fit to Page
• How to Copy Excel Table to Word with Gridlines
• How to Paste Excel Table into Word in Landscape
• How to Auto Populate Word Document from Excel
• How to Create Multiple Word Documents from Excel
• How to Insert Excel Table into Word with Formulas