Drop Down Lists in Excel 2007

30 11 2010

If you have to type the same data into cells all the time, then adding a drop down list to your spreadsheet could be the answer. In Excel 2007, this comes under the heading of Data Validation.

In the example below, we have a class of students on a drop down list. We only have to click a cell in the A column to see this same list of students. You’ll see how to do that now. Here’s a picture of your finished spreadsheet:

A Drop Down List in Excel 2007

In the image above, we can simply select a student from the drop down list – no more typing! We can also do the same for the Subject and Grade.

So, create the following headings in a new spreadsheet:

Cell A1 Student
Cell B1 Subject
Cell C1 Grade
Cell E1 Comments

We now need some data to go in our lists. So, type the same data as in the image below. It doesn’t need to go in the same columns as ours. But don’t type in Columns A, B, C or E:

The Data for the lists

The data in Columns F, G and H above will be going in to our list.

Now click on Column A to highlight that entire column:

Highlight the A Column

With Column A highlighted, click on Data from the Excel 2007 menu bar. From the Data menu, locate the Data Tools panel. On the Data Tools panel, click on the Data Validation item. Select Data Validation from the menu:

The Data Tools Panel in Excel 2007

When you click Data Validation, you’ll see the following dialogue box appear:

Data Validation

To create a drop down list, click the down arrow just to the right of “Allow: Any Value” on the Settings tab:

Click on List

Select List from the drop down menu, and you’ll see a new area appear:

Source means which data you want to go in your list. You can either just type in your cell references here, or let Excel do it for you.

To let Excel handle the job, click the icon to the right of the Source textbox:

Select the Source for your lists

When you click this icon, the Data Validation dialogue box will shrink:

Now select the cells on your spreadsheet that you want in your list. For us, this is the Students:

Select the Students

Once you have selected your data, click the same icon on the Data Validation dialogue box. You’ll then be returned to the full size one, with your cell references filled in for you:

The Source has been entered

Click OK, and you’ll see the A column with a drop down list in cell A1:

The A Column now has drop down lists

However, you don’t want a drop down list for your A1 column heading. To get rid of it, click inside of cell A1. Click the Data Validation item on the Data Tools panel again to bring up the dialogue box. From the Allow list, select Any Value:

Select Any Value from the list

Click OK on the Data Validation dialogue box, and your drop down list in cell A1 will be gone.

The rest of the column will still have drop down lists, though. Try it out. Click inside cell A2, and you’ll see a down-pointing arrow:

Click the arrow to see your list:

The Drop Down List has been added

Select an item on your list to enter that name in the cell. Click any other cell in the A column and you’ll see the same list.

Adding a drop down list to your cell can save you a lot of time. And it means that typing errors won’t creep in to your work.

 

Exercise

Add drop down list to the B and C columns. The B column should contain lists of Subjects, and the C column a list of Grades. Make sure that the cells B1 and C1 don’t contain drop down lists. When you’re finished, the Subject column should look like this:

The Subject Drop Down List

And the Grade column should look like this:

The Grade Drop Down List

 

In the next part, you’ll see how to add an error message to an Excel 2007 spreadsheet.


Actions

Information

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: