Select all the items in one of your lists by clicking and dragging your cursor over those cells.Click the icon to the right of the Source field to open a small Data Validation window.In the Allow field on the Settings tab, select List.This opens the Data Validation dialog box. On the Data tab, click the Data Validation button, then select Data Validation.Next you’ll select each list item range to find out their row and column values. Step 4: Find and select the list item range Add the list items for the other data fields.Try to keep them in alphanumeric order as neither Excel nor Word sorts them automatically. Underneath Row 1, Column A, type each list item, one per cell.Repeat step 3 for as many lists as you need to create remember, not every data field in your mail merge dataset will have a selection list.Move to the next column in Row 1 and type the name of the next data field that will have a drop-down list.In Row 1, Column A, type the name of the data field for that particular list (this name is only for you/someone else to identify at a later date - it isn’t used in the mail merge nor is it critical to the drop-down lists you create I tend to use the same name as that in the MailMergeData sheet, but this isn’t essential.).Step 3: Set up the list items for the selection lists increase the font size, add a background color, make the font bold, etc.). For ease of reading, format Row 1 so that the field names are obvious (e.g.(In the screen shot below, I’ve used RecFirstName and RecLastName for the recipient’s first and last names, RecOrganization for the recipient’s organization, DocType for the type of document.) Try to make the names meaningful and recognizable these names will be listed in the mail merge field selection list, so you need to be able to figure out what they are. Don’t use spaces, symbols or punctuation in the names, and don’t duplicate any names - each has to be unique. In Row 1, enter the names of the data fields that you’ll use in your mail merge letter - one in each cell.For this example, call it MailMergeData.xlsx. Change the name of Sheet2 to Lists - this sheet will contain the list items for the various fields.Change the name of Sheet1 to MailMergeData - this will be the data entry sheet.=Lists!$A$2:$A$7 for a range of cells on the Lists worksheet).
Create drop down list in excel 2011 mac how to#
In this tutorial, I’ll show you how to set up a list in Excel 2007 and how to reference it from cells on the main data entry worksheet.īottom line: Reference cells in another worksheet by adding the worksheet name plus an exclamation mark between the = sign and the cell range (e.g. I knew I could create the data lists for the drop-downs in Excel (thanks to a great session on just this by Michael Hughes at the 2009 WritersUA Conference), but I wanted them on their own worksheet, and I wasn’t sure how to reference them from the data entry sheet (I’m an Excel novice!). Such selection lists simplify the process for the person entering the data in the Excel spreadsheet, save them time, and make the data entry more accurate as they can’t make spelling errors or typos.įor example, one of the mail merge fields I created was for the type of document that was referred to in the letter the only acceptable document types were: However, some of the fields would have data in them that would be used in every letter, and this data was ideal for a drop-down selection list. I knew I could create a ‘database’ in Excel and refer to that for the mail merge fields in the Word document. I was asked to set up a mail merge letter for my client.