{"id":1246,"date":"2012-11-15T11:06:29","date_gmt":"2012-11-15T11:06:29","guid":{"rendered":"http:\/\/www.microsofttraining.net\/b\/exceltraining\/?p=1246"},"modified":"2023-12-30T23:11:05","modified_gmt":"2023-12-30T23:11:05","slug":"how-to-add-a-drop-down-list-to-speed-up-data-entry-in-excel","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/how-to-add-a-drop-down-list-to-speed-up-data-entry-in-excel\/","title":{"rendered":"How to add a drop down list to speed up data entry in Excel"},"content":{"rendered":"

Anything that can cut down the time it takes to add data and improve accuracy is good in my book.<\/p>\n

Creating a drop down list isn’t as tough as I thought, so here is how to set up a drop down list using Data Validation<\/strong>.<\/p>\n

I have a set of customers, that come under four regions, I have regular aftercare appointments and I want to allocate each customer to one of my aftercare team.<\/p>\n

Here is my basic worksheet. \u00a0I can type each piece of information into the appropriate cell.<\/p>\n

\"data-validation-list-advanced-excel-courses\"<\/a>
The basic worksheet…do I enter the data manually or is it quicker to set up a drop down menu?<\/figcaption><\/figure>\n

As each column has a clear choice of options, I could speed up data entry using drop down lists.<\/p>\n

For my Sales Region, I have four options: north, south, east and west.<\/p>\n

\"region-list-data-validation-excel-advanced-cours\"<\/a>
I have created the list of regions in column G<\/figcaption><\/figure>\n
    \n
  1. To set these up as a list, I type them in another range of cells. \u00a0These cells are for Excel to refer to, so I put them in my worksheet where they won’t interfere with my actual data. ( I have put them in the G column).<\/li>\n
  2. To set up the drop down list, I select the cells where I want the drop down lists to appear – in this example, I want to apply it to B2 to B6.<\/li>\n
  3. I go to the Data tab<\/strong> and select Data Validation<\/strong>, and in the Settings<\/strong> menu. \u00a0Here I select List.<\/strong>\n

    \"list-data-validation-menu-excel-advanced-course\"<\/a>
    I select “List” so that Excel knows what I want it to do!<\/figcaption><\/figure><\/li>\n
  4. I then need to select the range of cells where my options are set up. I click and drag over the range of cells G2 to G6).\n

    \"range-data-validation-excel-advanced-courses\"<\/a>
    I find it easier to click and drag over the my reference cells rather than type them in manually).<\/figcaption><\/figure><\/li>\n
  5. I make sure that I have ticked the boxes for Ignore blank<\/strong> and In-cell drop down<\/strong>. Then press ok.<\/li>\n
  6. The drop down list is now applied and when I click into the cell, a grey down arrow appears, and I click on the choice to apply it. \u00a0So 2 clicks, and I’ve added the region, rather than typing it manually.\n
    \"drop-down-list-applied-excel-advanced-courses\"<\/a>
    When you click on the cell’s down arrow box, the options appear, and it takes one click to add it.<\/figcaption><\/figure>\n

    I can add drop down lists to the other parts of my worksheet. \u00a0Using the same method as above, I type in my reference lists (from column G to column I) then use the Data Validation to apply drop down menus to all my columns<\/strong>. \u00a0In a few clicks, I’ve set up my worksheet and saved time each time I update my worksheet.<\/li>\n<\/ol>\n

    \n
    \"worksheet-data-validation-excel-advanced-courses\"<\/a>
    Data validation has saved me lots of time when entering data in my worksheet.<\/figcaption><\/figure>\n<\/div>\n

    For more information on how to speed up data entry and get more out of your data,<\/strong> have a look at our Excel advanced courses<\/strong>,\u00a0https:\/\/www.stl-training.co.uk\/excel-2010-advanced.php<\/a><\/p>\n

     <\/p>\n

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

    Anything that can cut down the time it takes to add data and improve accuracy is good in my book. Creating a drop down list isn’t as tough as I thought, so here is how to set up a drop down list using Data Validation. I have a set of customers, that come under four […]<\/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],"tags":[55,181],"class_list":["post-1246","post","type-post","status-publish","format-standard","hentry","category-excel-training","tag-apply-data-validation-in-excel","tag-excel-advanced-courses"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/1246","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=1246"}],"version-history":[{"count":1,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/1246\/revisions"}],"predecessor-version":[{"id":6703,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/1246\/revisions\/6703"}],"wp:attachment":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/media?parent=1246"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/categories?post=1246"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/tags?post=1246"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}