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.

NEWS

IRS sets start date for tax season

The IRS announced that tax season will start in late January and that it will issue refunds to taxpayers despite the partial shutdown of the federal government.

PODCAST

Why CPAs can’t wait on automation tools

What do accounting firms waiting on others to develop AI, automation, and data analytics tools have in common with a baseball fan sitting in a stadium filling with water at an exponential rate? The answer could determine your firm’s fate.