Insert or delete a drop-down list

To make data entry easier in Excel, or to limit entries to certain items that you define, you can create a drop-down list of valid entries that is compiled from cells elsewhere in the workbook. When you create a drop-down list for a cell, it displays an arrow in that cell. To enter information in that cell, click the arrow, and then click the entry that you want.

To create a drop-down list from a range of cells, use the Data Validation command in the Data Tools group on the Data tab.

1. To create a list of valid entries for the drop-down list, type the entries in a single column or row without blank cells. For example:

2. NOTE: You may want to sort the data in the order that you want it to appear in the drop-down list.

3. If you want to use another worksheet, type the list on that worksheet, and then define a name for the list.

4. Select the cell where you want the drop-down list.

5. On the Data tab, in the Data Tools group, click Data Validation.

6. In the Data Validation dialog box, click the Settings tab.

7. In the Allow box, click List.

8. To specify the location of the list of valid entries, do one of the following:

• If the list is in the current worksheet, enter a reference to your list in the Source box.

• If the list is on a different worksheet, enter the name that you defined for your list in the Source box.

In both cases, make sure that the reference or name is preceded with an equal sign (=). For example, enter=ValidDepts.

9. Make sure that the In-cell dropdown check box is selected.

10. To specify whether the cell can be left blank, select or clear the Ignore blank check box.

11. Optionally, display an input message when the cell is clicked.

12. Specify how you want Microsoft Office Excel to respond when invalid data is entered.

NOTES

• To delete a drop down list, select the cell with the list. Click the Data tab, and then in the Data Group, click Data Validation. In the Data Validation dialog box, click the Settings tab, and then click Clear All. Learn more about removing data validation.

• The width of the drop-down list is determined by the width of the cell that has the data validation. You may need to adjust the width of that cell to prevent truncating the width of valid entries that are wider than the width of the drop-down list.

• The maximum number of entries that you can have in a drop-down list is 32,767.

• If the validation list is on another worksheet and you want to prevent users from seeing it or making changes, consider hiding and protecting that worksheet.

Insert a sort box:

To make data organizing easier in Excel, you can simply add a sort box to the top row of labels. Here are the steps:

Table before sort add:

1. Highlight all of the data that you want to include in the sort. Normally, this will be all the data displayed to keep the integrity of the rows and columns.

2. Choose Filter/Advanced Filter on the Data tab.

3. The list range should automatically populate for you based on your highlight. Click “filter the list in-place” and then click OK.

4. Then click on the big box that says “Filter”.

5. All columns should receive a drop down box to very quickly be able to sort from. The drop down boxes also contain information for more advanced sorts and filters that you can manipulate.

--

--

--

Our firm, Beanstalk CFO Group, delivers financial management & advice to small business owners.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

5 Useful Apollo Gateway Packages for File Uploading and Serving

Folders, cloud images, upload signs, wifi sign, all linked by the cloud.

Rails Sidekiq: Job executes on the wrong service

Feature Flags for True Continuous Deployment

HTML - 7. Global Attributes

Put your points in Agility

Test Parquet float16 Support in Pandas

XMPP — Good as a Open-Source — Deadly as Commercial

Researc/hers that Code Series: Open-Source, Outreach and the Ocean

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Beanstalk CFO Group

Beanstalk CFO Group

Our firm, Beanstalk CFO Group, delivers financial management & advice to small business owners.

More from Medium

What I Learned This Week (12-Apr-2022 to 18-Apr-2022)

Time Habits of a Medical Student, MS2

Tips to Set Accurate Deadline.

Day 20 of #66DaysOfDataChallenge