Excel: Reference lists in another worksheet

June 9, 2011


I was asked to set up a mail merge letter for my client. I knew I could create a ‘database’ in Excel and refer to that for the mail merge fields in the Word document. 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.

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.

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

  • Appendices
  • document
  • Plan
  • Program
  • Report
  • Supplement

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!).


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.

Bottom line: Reference cells in another worksheet by adding the worksheet name plus an exclamation mark between the = sign and the cell range (e.g. =Lists!$A$2:$A$7 for a range of cells on the Lists worksheet).

Step 1: Name the worksheets

  1. Open a new Excel document.
  2. Change the name of Sheet1 to MailMergeData — this will be the data entry sheet.
  3. Change the name of Sheet2 to Lists — this sheet will contain the list items for the various fields.
  4. Save the document. For this example, call it MailMergeData.xlsx.

Change the names of the worksheets

Step 2: Name the data columns

  1. Go to the MailMergeData sheet.
  2. In Row 1, enter the names of the data fields that you’ll use in your mail merge letter — one in each cell. Don’t use spaces, symbols or punctuation in the names, and don’t duplicate any names — each has to be unique. 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. (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.)
  3. For ease of reading, format Row 1 so that the field names are obvious (e.g. increase the font size, add a background color, make the font bold, etc.).
  4. Save the document.

Step 3: Set up the list items for the selection lists

  1. Go to the Lists worksheet.
  2. 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.)
    Item list
  3. Move to the next column in Row 1 and type the name of the next data field that will have a drop-down list.
  4. 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.
  5. For ease of reading, format Row 1 so that the field names are obvious (e.g. increase the font size, add a background color, make the font bold, etc.).
  6. Underneath Row 1, Column A, type each list item, one per cell. Try to keep them in alphanumeric order as neither Excel nor Word sorts them automatically.
  7. Add the list items for the other data fields.
  8. Save the document.

Step 4: Find and select the list item range

Next you’ll select each list item range to find out their row and column values.

  1. Go to the Lists worksheet.
  2. On the Data tab, click the Data Validation button, then select Data Validation. This opens the Data Validation dialog box.
  3. In the Allow field on the Settings tab, select List.
    Data Validation dialog box
  4. Click the icon to the right of the Source field to open a small Data Validation window.
    Data Validation panel
  5. Select all the items in one of your lists by clicking and dragging your cursor over those cells.
  6. As you do this, the selection is shown in the Data Validation field as something like =$A$2:$A$7, which indicates everything in column A, rows 2 to 7 inclusive, as shown in the example below.
    Selected items as displayed in the Data Validation panel
  7. Press Enter to show the full Data Validation dialog box. The cell range is displayed in the Source field.
  8. Select everything in the Source field and copy your selection to the clipboard.
  9. Click Cancel as you don’t need this dialog box again for this list.

Step 5: Add the list item range to the mail merge field

In this step, we’ll select the field in the MailMergeData worksheet and use data validation to link to the other worksheet’s item list.

  1. Go to the MailMergeData worksheet.
  2. Select the first empty cell in the column where you want the drop-down list to go. In the example below, I selected the first cell under the DocType heading.
  3. On the Data tab, click the Data Validation button, then select Data Validation. This opens the Data Validation dialog box.
  4. In the Allow field on the Settings tab, select List.
  5. In the Source field, paste in the range you copied to the clipboard a few seconds ago.
  6. Put your cursor IMMEDIATELY after the = sign, and type Lists! You should end up with something like: =Lists!$A$2:$A$7 (Lists is the name of the worksheet you are referencing and the ! tells Excel to look for that cell range on the Lists worksheet, not the current one. Make sure you type the worksheet name correctly, otherwise it won’t work — List is not the same name as Lists.)
    Data Validation dialog box with reference to other worksheet
  7. Click OK to save your changes and close the Data Validation dialog box.
  8. As soon as you click OK, you should see a drop-down arrow next to the cell you’re in.
  9. Click the arrow to test that the list displays as you expect it to.
    Drop-down list referenced from other worksheet
  10. If it works as you expect, apply this range to other cells in this column by dragging the little square icon at the bottom right of the cell down the column to cover as many rows as you want. For example, if your mail merge dataset typically has 20 individuals, then drag it down 20 rows.
    Drag the settings to other cells in this column
  11. Save your changes.

Quick way to link to the same list range but for different columns

If you have several lists set up on the Lists worksheet, then instead of going through many of the steps above, you can just do the steps once, then, once you have your initial range (e.g. =Lists!$A$2:$A$7), you can copy that into other Data Validation dialog boxes and change the column letter and row numbers to reflect the other list. For example, let’s say you have another set of items for a drop-down list that are in column E, rows 2 to 12 — change the ‘A’ in =Lists!$A$2:$A$7  to ‘E’ and the ‘7’ to ’12’, giving you =Lists!$E$2:$E$12. Leave everything else as it is.

Alternative method

Based on the post above, Peter Sanders made this short video on using Range Names instead: http://www.screencast.com/t/jKKSD7zeDzac Being an Excel novice, I knew there would be a more elegant solution than mine, and Peter has provided it. Thanks for your contribution, Peter!

See also:

[Links last checked June 2011; thanks to @michaelhughesua for his conference session related to this, and to Peter Sanders for the video]


  1. Thank you!!!! thank you!!!!! thank you!!!!!! this was such a HELP!!!! you have no idea how long I’ve been trying to do this!!!!! Thank you again!!!!!

  2. How can i appy auto suggest to the cell..
    Please let me know

  3. Hi. I have a list with 7 columns on a separate sheet from the worksheet with the drop down. Most items populate across the 7 columns when I select the drop down but after line 22 only the first column shows in the drop down but the other 6 columns don’t populate. I have looked everywhere and don’t know why these other columns are not populating. Thanks for the help..if you ever get this.

  4. Sorry Steve – I can’t help you as I have no idea what might be causing that. The only thing that springs to mind is that maybe you have a blank cell in/around Line 22 for one of the lists?

    You could try posing your problem on the Microsoft Excel forum where people far more knowledgeable than me may know the answer: http://answers.microsoft.com/en-us/office/forum/office_2010-excel


  5. I have a doubt related to shared spreadsheet.
    Recently I faced a problem related to shared spreadsheet,i was updating all the work details in the local copy,and copied those contents in the shared excel spreadsheet later, Alas! all those copied details were overwritten in other user’s empty cells, how to avoid this problem? or any solution for this?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: