A Drop-down List To The Rescue


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.


6 key areas of change for accountants and auditors

New accounting standards on revenue recognition, leases, and credit losses present implementation challenges. This independently-written report identifies the hurdles that accounting professionals face and provides tips for overcoming the challenges.


How tax reform will impact individual taxpayers

Amy Wang, a CPA who is a senior technical manager for tax advocacy at the AICPA, answers to some of the most common questions on how the new tax reform law will impact individual taxpayers.