A Drop-down List To The Rescue

BY STANLEY ZAROWIN

Key to Instructions

To help readers follow the instructions in this article, we used two different typefaces:

Boldface type is used to identify the names of icons, agendas and URLs.

Sans serif type shows the names of files and the names of commands and instructions that users should type into the computer.


Each week I distribute Excel worksheets to a group of line managers who aren’t handy with Excel. I ask them to enter certain product numbers in a single worksheet cell. Because they often accidentally put the information in the wrong cell or enter incorrect numbers, I end up with a weekly headache. Short of prescribing aspirin, what do you suggest I do?

A drop-down list would solve your problem. You can set up each worksheet with a list of all the available product numbers; when a manager’s cursor passes over the target cell, the list with all numbers drops down. When they click on one of the numbers, Excel stores it and the list disappears.

Setting up the list is a snap. Begin by placing each product number in numerical order in contiguous cells. Then highlight the cells and give the collection a name ( Products ) by entering the name in the Name Box above cell A1 (see screenshot below) or by clicking on Insert , Name , Define .

Now go to the worksheet you’ll be distributing to the managers and select the first cell that will contain the drop-down list. Go to Data and click on Validation , opening the Data Validation dialog box, with the Settings tab displayed.

Press the arrow to the right of Allow and display List . Be sure the Ignore blank and In-cell dropdown boxes are checked. In the Source box type the name you gave to the list preceded by an equal sign: =Products . The equal sign is critical. Then click on OK .

When you pass your cursor over the box that contains a downward pointing arrow, this is what will appear:

Option : Instead of typing the names into the worksheet, you can enter them directly into the Data Validation dialog box. Type them in the Source box, with each name separated by a comma. Do not use an equal sign.

SPONSORED REPORT

Revenue recognition: A complex effort

Implementing the new standard requires careful judgment. Learn how to make significant accounting judgments and document them and collaborate with peers for consistent application.

VIDEO

How to Excel pivot a general ledger

The general ledger is a vast historical data archive of your company's financial activities, including revenue, expenses, adjustments, and account balances. J. Carlton Collins, CPA, shows how to prepare data for, and mine data with, PivotTables.

QUIZ

News quiz: Taking an economic snapshot and looking to the future

Recent news included IRS actions that affect individuals and partnerships and a possibly influential move by a Big Four accounting firm.Take this short quiz to see how much you know about the news.