{"id":2000,"date":"2014-12-10T12:35:40","date_gmt":"2014-12-10T12:35:40","guid":{"rendered":"http:\/\/www.microsofttraining.net\/b\/?p=2000"},"modified":"2023-12-31T01:56:23","modified_gmt":"2023-12-31T01:56:23","slug":"create-drop-list-excel","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/create-drop-list-excel\/","title":{"rendered":"Create a drop-down list in Excel"},"content":{"rendered":"

Save time by avoiding\u00a0repetition and errors<\/h3>\n

Excel is great for lists; sales figures, staff rota\u2019s, stock control, to name a few. But an easy trap to fall into is repetitive error prone data-entry that leads to inaccurate business reporting and lost time in troubleshooting.<\/p>\n

If you create a drop-down list in Excel, you can avoid all of this. Imagine the time saved short and long term especially if multiple people are using the same spreadsheet.<\/p>\n

How to create a drop-down list in Excel<\/h2>\n
\"excel_drop_list\"<\/a>
Here’s one we made earlier<\/figcaption><\/figure>\n

Step 1<\/strong>\u00a0Assign the values for your drop-down list. In a new worksheet, just start your list and order it if you wish (better now than later!)<\/p>\n

\"an_excel_list\"<\/a>
advanced Excel courses<\/a><\/figcaption><\/figure>\n

Step 2<\/strong>\u00a0Now select the data and right click, select Define Name<\/strong>.<\/p>\n

Step 3<\/strong>\u00a0In the New<\/strong>\u00a0Name<\/strong> dialogue box you need to give your data name (this is a named range), making sure not to have any spaces in the name. Example, Commute<\/p>\n

\"Name<\/a><\/p>\n

Step 4\u00a0<\/strong>Now go to the worksheet where you wish create a drop-down list in Excel, and click a cell. Go to the Data tab and select Data Validation<\/p>\n

\"select<\/a><\/p>\n

Step 5<\/strong>\u00a0In Settings tab we need to do the following:<\/p>\n

Select List<\/strong> from the Allow<\/strong> box.
\nEnsure\u00a0In-cell dropdown<\/strong> is ticked. If you are okay for blank entries to be made just leave the Ignore blank<\/strong> ticked.
\nIn the Source<\/strong> box we need to type in the name of our list making sure to start with an =. In this case, =Commute<\/p>\n

\"data<\/a><\/p>\n

Now click OK, your drop-down list is ready to go. You may have noticed two other tabs within the Data Validation<\/strong> box. The Input Message<\/strong> and Error Alert<\/strong> give you even more options to control how data is entered and also what messages appear to users when they have not entered data correctly.<\/p>\n

To create a drop-down list in Excel is pretty straight forward giving us some major advantages in saving time from data entry as well as data error. Data validation in it\u2019s own right can really help businesses adopt more consistent and efficient use of Excel spreadsheets.<\/p>\n

More related information:<\/h4>\n

Excel data validation in business<\/a><\/p>\n

A real world\u00a0example of assigning values to a drop-down list in Excel<\/a><\/p>\n

A further look<\/a> at Input Message & Error Alert<\/p>\n

 <\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

Save time by avoiding\u00a0repetition and errors Excel is great for lists; sales figures, staff rota\u2019s, stock control, to name a few. But an easy trap to fall into is repetitive error prone data-entry that leads to inaccurate business reporting and lost time in troubleshooting. If you create a drop-down list in Excel, you can avoid […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[4,6],"tags":[55,126,143,162,310,348,474],"class_list":["post-2000","post","type-post","status-publish","format-standard","hentry","category-excel-training","category-hints-tips","tag-apply-data-validation-in-excel","tag-create-a-drop-down-list","tag-data-validations","tag-drop-down-lists","tag-improve-efficiency-with-data-validation","tag-make-data-entry-easier-in-excel","tag-save-time-with-drop-down-lists"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/2000","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/comments?post=2000"}],"version-history":[{"count":1,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/2000\/revisions"}],"predecessor-version":[{"id":5023,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/2000\/revisions\/5023"}],"wp:attachment":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/media?parent=2000"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/categories?post=2000"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/tags?post=2000"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}