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.


Download Practice Workbook

You can download the practice workbook here.


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

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.

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

  • 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.

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

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

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

  • Now, 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: Excel VBA: Open Word Document and Paste (3 Suitable Examples)


Similar Readings


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.

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

  • Then, open the Word file.
  • Go to the Developer tab.
  • 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.

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

  • 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.

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

  • 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.

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

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

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

Read More: How to Open Word Document and Save As PDF or Docx with VBA Excel


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.


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

Adnan Masruf

Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo