{"id":447,"date":"2023-07-27T11:15:53","date_gmt":"2023-07-27T11:15:53","guid":{"rendered":"https:\/\/businessyield.com\/tech\/?p=447"},"modified":"2023-07-27T11:15:54","modified_gmt":"2023-07-27T11:15:54","slug":"how-to-create-a-drop-down-list-in-excel","status":"publish","type":"post","link":"https:\/\/businessyield.com\/tech\/how-to\/how-to-create-a-drop-down-list-in-excel\/","title":{"rendered":"How To Create A Drop-Down List In Excel: Quick & Easy Methods","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"\n

Do you want to know how to create a drop-down list in Excel to increase your efficiency and proficiency? Microsoft Excel is more than just an old-school computer program that our parents use at their desk jobs. When used effectively, it can be a rewarding tool in your arsenal.<\/p>\n\n\n\n

Whether you are a business owner, a professional, need to manage your household finances or schedule, or simply need a budget, keeping things organized is key to success. There are tons of apps and websites to help you stay organized nowadays and, old as it is, Microsoft Excel remains dependable and faithful to that task.<\/p>\n\n\n\n

Guess you cannot beat the classic indeed.<\/p>\n\n\n\n

What are drop-down lists in Excel?<\/strong><\/span><\/h2>\n\n\n\n

A drop-down list helps the user select text or values from a list of options, instead of typing them manually in a cell. This allows you (the creator) to control all data entries for important cells.<\/p>\n\n\n\n

Microsoft Excel<\/a> drop-down lists are one way to simplify data input within Excel, which is helpful where time is money. The drop-down list is the most popular data validation tool in Excel.<\/p>\n\n\n\n

Here’s a quick step-by-step guide to creating drop-down lists.<\/p>\n\n\n\n

How To Create A Drop-Down List In Excel<\/strong><\/h2>\n\n\n\n

Drop-down lists control data entry. However, before you insert the drop-down list, you need to decide what data entries should be allowed. This is what we call the \u2018list items\u2019.<\/p>\n\n\n\n

Pick anywhere you want to store the drop-down list of items. It could be in the same sheet, another sheet, or even in another Excel file. Write the desired drop-down menu options in a list.<\/p>\n\n\n

\n
\"\"<\/figure><\/div>\n\n\n

Simply move your cursor to the \u2018Data\u2019 tab and left-click it.<\/p>\n\n\n

\n
\"Excel<\/figure><\/div>\n\n\n

Now click \u2018Data validation\u2019 in the middle of the ribbon.<\/p>\n\n\n\n

Note: (Do not click the little arrow \u2013 simply click the top part of the button).<\/em><\/p>\n\n\n\n

\"Click<\/figure>\n\n\n\n

Now, a dialog box called \u2018Data validation\u2019 should appear. This is where you choose the settings for your drop-down list.<\/p>\n\n\n\n

In the data validation dialog box, click the drop-down list<\/strong> and pick what kind of data validation method you want to use. Select \u2018List\u2019 from the\u2026 well\u2026 list<\/em>!<\/p>\n\n\n

\n
\"Data<\/figure><\/div>\n\n\n

Remember back in step 1 where you chose the allowed options (list items) for your drop-down list? Now it is time to connect that list of options with the drop-down menu.<\/p>\n\n\n\n

Left-click once in the \u2018Source:\u2019 field.<\/p>\n\n\n

\n
\"Drop-down<\/figure><\/div>\n\n\n

Then go to the location of your list with the allowed values that you wrote in Step 1. Select the cells containing your drop-down list items.<\/p>\n\n\n\n

\"Selected<\/figure>\n\n\n\n

And click \u2018OK\u2019.<\/p>\n\n\n\n

Pro Tip<\/strong>: As you can see from the picture above, a few empty cells are included at the bottom of the source range. This is so you can add new drop-down list items to the source just by writing in the blank cells.<\/p>\n\n\n\n

This makes it so much easier than going back and changing the source range size every time you add new items. Also, it is much easier than creating a dynamic drop-down list, where the source range automatically adjusts to the data.<\/p>\n\n\n\n

Once you click \u2018OK\u2019, your drop-down list is ready for use.<\/p>\n\n\n\n

\"Drop-down<\/figure>\n\n\n\n

What to do if you get an Error alert<\/strong><\/span><\/h2>\n\n\n\n

Drop-down lists are mainly used for selecting data entry instead of typing it manually. However, the user can do manual data entry in the cell that contains the drop-down list.<\/p>\n\n\n\n

But if the user enters invalid data, an error alert will show. The default error message doesn\u2019t give a great indication of what the user can actually write in the cell.<\/p>\n\n\n\n

To fix it, simply go to the \u2018Error alert\u2019 tab after clicking \u2018Data validation\u2019.<\/p>\n\n\n

\n
\"Edit<\/figure><\/div>\n\n\n

From here, make sure there is a checkmark in the \u201cShow error alert after invalid data is entered\u201d. Then, add the title and error message and choose an icon if you don\u2019t like the default.<\/p>\n\n\n\n

Hit \u2018OK\u2019.<\/p>\n\n\n\n

Now, this error message pops up whenever invalid data is entered into the cell.<\/p>\n\n\n

\n
\"Error<\/figure><\/div>\n\n\n

If you don\u2019t check the \u201cShow error alert after invalid data is entered\u201d checkbox, anyone can write anything in the cell with the drop-down list.<\/p>\n\n\n\n

Input message in Excel Drop-down lists<\/strong><\/span><\/h2>\n\n\n\n

Drop-down menus make it harder to input something wrong in your spreadsheet. But the \u2018Input message\u2019 addition makes data entry even easier \u2013 and more bulletproof.<\/p>\n\n\n\n

When creating a drop-down list, you can add an input message from the \u2018Input message tab\u2019 in the data validation dialog box.<\/p>\n\n\n

\n
\"Drop-down<\/figure><\/div>\n\n\n

Simply type your input message and it will appear when the user selects the cell containing the drop-down list.<\/p>\n\n\n

\n
\"Input<\/figure><\/div>\n\n\n

The input message is an easy way to improve the user experience of your spreadsheets. This is something most spreadsheet creators neglect.<\/p>\n\n\n\n

How to add color to a drop-down list in Excel<\/strong><\/h2>\n\n\n\n

Color can be a powerful element in an Excel drop-down list, and it is very easy to incorporate. You can add conditional formatting<\/a> rules to the cell containing the drop-down list. <\/p>\n\n\n\n

Here’s how to create a color-coded drop-down list with conditional formatting<\/p>\n\n\n\n

    \n
  1. Mark your drop-down cells, which in this case are in column A, and go to Home > Conditional Formatting<\/strong> then select New Rule.<\/strong><\/li>\n\n\n\n
  2. Click the New Rule<\/strong> option. Here, you can see the New Formatting Rule<\/strong> dialog box; click the Format-only cells that contain the option under the Select a Rule Type<\/strong> section. <\/li>\n\n\n\n
  3. Under the Format-only cells with section,<\/strong> choose Specific Text from the first drop-down list and containing from the second drop-down list. Then, click the doc button 1 button to choose the value you want to format with a certain color, see screenshot.<\/li>\n\n\n\n
  4. Click the Format<\/strong> button, then click the Fill<\/strong> tab.<\/li>\n\n\n\n
  5. Choose a color you like, and then click OK<\/strong> to close the dialog.<\/li>\n\n\n\n
  6. Repeat steps 1 to 4 for each other drop-down selection.<\/li>\n\n\n\n
  7. Now, click Conditional Formatting,<\/strong> then click Manage Rules<\/strong>. Select the cell range to Apply<\/strong> the Conditional Formatting.<\/strong><\/li>\n\n\n\n
  8. Once you’ve set the colors for the values, you can choose any value from the drop-down menu and the cell will automatically change to the color you chose.<\/li>\n<\/ol>\n\n\n\n

    What is the shortcut key for the drop-down list?<\/strong><\/h2>\n\n\n\n

    By using the keyboard shortcut “Alt + D + L<\/strong>“, you can create drop-down lists quickly and easily.<\/p>\n\n\n\n

    How do I create a yes\/no drop-down in Excel?<\/strong><\/h2>\n\n\n\n

    Creating a yes\/no drop-down in Excel uses the same process we followed for the example above. All you need to do is follow these steps:<\/p>\n\n\n\n

      \n
    1. Select the cells you want to include in the drop-down lists.<\/li>\n\n\n\n
    2. Click on Data<\/strong> Validation<\/strong>.<\/li>\n\n\n\n
    3. Insert Yes, No<\/strong> in the Source field on the pop-up, only separated by a comma.<\/li>\n\n\n\n
    4. Click OK<\/strong> to save your yes\/no drop-down list.<\/li>\n<\/ol>\n\n\n\n

      How to create a drop-down list in Excel Google Sheet<\/strong><\/h2>\n\n\n\n

      Create a dropdown list on your computer<\/strong><\/h3>\n\n\n\n
        \n
      1. In Google Sheets, open a spreadsheet.<\/li>\n\n\n\n
      2. Select the cell or cells where you want to create a dropdown list.<\/li>\n\n\n\n
      3. Select an option:\n