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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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

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

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.


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

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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