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

Method 1 – Using the Paste Special Command

Steps:

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

copying cell link to add drop-down list in word from excel

  • Open the Word file.
  • Go to the Home tab.
  • Select the Paste option.
  • Click on the Paste Special command.
  • A prompt will show up on the screen.

applying paste special command in word to add drop-down list in word from excel

  • In the prompt, first, select the Paste Link oval.
  • Sselect the Microsoft Excel Worksheet Object under As.
  • Click OK.

pasting excel link to add drop-down list in word from excel

  • Change the list contained in the drop-down list in the Excel file.

using paste special command of word document to add drop-down list in word from excel

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

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


Method 2 – Applying VBA Code

Steps:

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

creating excel list to add drop-down list in word from excel

  • Open the Word file.
  • Go to the Developer tab.
  • From the Controls group, select the insert Combo Box Content Control command.
  • We will have a drop-down box on the screen.

inserting the combo box content control to add drop-down list in word from excel

  • Select the Developer tab.
  • Choose the Visual Basic command.
  • A prompt will open.

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

using tools and references in the visual basic tab to add drop-down list in word from excel

  • In the References dialog box, choose the Microsoft Excel 16.0 Object Library option under Available References.
  • Click OK.

  • Go to the Insert tab.
  • Select the Module option.

  • In the opened module, enter the following code and save it.

adding vba code to add drop-down list in word from excel

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
  • Run the code by clicking the triangle-shaped green button.

running the code to add drop-down list in word from excel

  • 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

Steps:

  • Select the cell where you want to add the drop-down list. In this case, the cell will be D5.
  • Go to the Data tab.
  • Choose the DataTools group.
  • From the Data Validation option, select the Data Validation command.

  • In the Data Validation dialog box, choose List under the Allow option.
  • 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.
  • Click OK.

  • We will have a drop-down list in our desired cell.

 


Download the Practice Workbook


Related Articles

<< Go Back to Export Excel to Word | Export Data from Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Adnan Masruf
Adnan Masruf

Adnan Masruf, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a pivotal role as an Excel & VBA Content Developer at ExcelDemy. His deep passion for research and innovation seamlessly aligns with his dedication to Excel. In this capacity, Masruf not only skillfully addresses challenging issues but also exhibits enthusiasm and expertise in gracefully navigating intricate situations, underscoring his steadfast commitment to consistently delivering exceptional content. His interests... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo