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.


How the election may affect taxation of business income

This report summarizes recent proposals to reform the U.S. business income tax system and considers the path to enactment of any such legislation.


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.


Did you follow 2016’s biggest accounting news?

CPAs will remember 2016 as a year of new standards and new faces. How well did you follow the biggest accounting events? The 7 questions in this quiz will help you find out