Drop-down lists can vastly facilitate information entry. Here is a take a look at the way to use Excel’s information validation characteristic to create helpful lists inside your worksheets.
In kinds, it may be helpful to restrict the alternatives for a range with a easy drop-down listing. That is additionally potential in an Excel spreadsheet, however the course of is not well-known or intuitive.
In internet kinds, surveys, and polls, it may be very helpful to restrict the alternatives for a range with a easy drop-down listing. That is additionally potential in an Excel spreadsheet, however the course of is not very well-known or very intuitive.
In Entry, you may restrict consumer entries by forcing customers to decide on a price from an inventory management. Workplace purposes use the identical performance in built-in drop-down lists. As an example, the Spotlight and Font Shade controls on most Formatting toolbars use this versatile software. Merely click on the small triangle to the suitable of the icon to show an inventory of decisions.
You possibly can create the identical sort of management in your customers in an Excel sheet, however the course of is not intuitive. The choice is within the Information Validation characteristic. Thankfully, as soon as you realize the characteristic exists, it is simple to implement. You want solely two issues: an inventory and a knowledge entry cell. The next sheet exhibits a easy drop-down listing in an Excel sheet.
Customers click on the drop-down arrow to show an inventory of things from A1:A4. If a consumer tries to enter one thing that is not within the listing, Excel rejects the entry. So as to add this drop-down listing to a sheet, do the next:
- Create the listing in cells A1:A4. Equally, you may enter the gadgets in a single row, reminiscent of A1:D1.
- Choose cell E3. (You possibly can place the drop-down listing in most any cell and even a number of cells.)
- Select Validation from the Information menu.
- Select Record from the Permit choice’s drop-down listing. (See, they’re in every single place.)
- Click on the Supply management and drag to spotlight the cells A1:A4. Alternately, merely enter the reference (=$A$1:$A$four).
- Ensure that the In-Cell Dropdown choice is checked. In the event you uncheck this feature, Excel nonetheless forces customers to enter solely listing values (A1:A4), however it will not current a drop-down listing.
- Click on OK.
You possibly can add the drop-down listing to a number of cells. Choose the vary of knowledge enter cells (step 2) as an alternative of a single cell. It even works for noncontiguous cells. Maintain down the Shift key when you click on the suitable cells.
A number of fast notes:
- You possibly can solely see the drop-down for those who click on on the cell.
- Your customers can now solely select one of many choices within the drop-down. In the event that they attempt to enter their very own information, then they’re going to obtain an error message.
- You possibly can copy-and-paste this drop-down cell to every other cells in your spreadsheet, and you may create as many various drop-downs like this as you would like.
SEE: Software program utilization coverage (Tech Professional Analysis)
A bonus Microsoft Excel tip
From the article 10 issues it’s best to by no means do in Excel by Susan Harkins:
Permit error values
In the event you share or distribute Excel information with different customers, embrace error-handling features to keep away from error values that others will not perceive and to supply data that they may. Error values aren’t unsuitable. Actually, they’re nice clues to issues, however they’re complicated to finish customers. They’re going to waste their time (and maybe yours) attempting to repair one thing that is probably not damaged.
Excel has a number of error-handling features:
- ISNA() evaluates #N/A errors.
- ISERR() evaluates all error varieties aside from #N/A.
- ISERROR() evaluates all errors.
- ERROR.TYPE() returns an integer worth that represents the error by sort.
- IFERROR() evaluates the error and permits you to return alternate content material.
This bonus Excel tip can be out there within the free PDF 30 issues it’s best to by no means do in Microsoft Workplace.
Editor’s word on March 12, 2019: This Excel article was first printed in June 2008. Since then, we have now included a video tutorial, added a bonus tip, and up to date the associated sources.