Ian Jamieson

Do you want to know how to create dropdowns in Excel cells for users to select their input? Or forgotten how to do it?

Then read on and I will show you the steps to create a simple dropdown – the functionality is called Data Validation and for this to work we need to select our criteria.

Data Validation is used to limit the values a user can enter in a cell. This helps keep your data clean by keeping inputs to the required values. Which in turn makes reporting and producing management information (MI) much easier.

Let’s go through the steps to create the example in Excel Office 365 below.

• In column F we enter the values we want to appear on the dropdown – a new value in each cell.
• We then select cell C3 – this is where we want the drop down to be.
• When in cell C3 select ‘Data’ from the menu right at the top of Excel. Then go along the ribbon to get to Data Tools.
• We then want to select the icon that looks like the one below, called Data Validation. This will bring up a few option, select the top one called Data Validation.

• This brings up a new box, see below. The first tab is called Settings.

• Under the ‘Allow’ section, select List. List is the easiest option to understand and manage in the future if you need to make changes.
• Then go further down to ‘Source’ and click on the up arrow at the end of the box.

• Clicking on the arrow opens up a smaller box. We now need to select cells F1 to F4 where we typed in our dropdown values, see below.

• Click on the down arrow at the end of the box and it will take us back to our Data Validation box. Simply click OK to finish.

• In cell C2 you will now see a dropdown which allows the user to select the limited range of predefined values. If a user tries to type anything else they will be presented with the error below.

You have now created a process that limits the values a user can enter in a cell. This helps maintain the cleanliness of your data.

If you ever need to change the values in your dropdowns you can simply type new values in cells F1 to F4.

If you need to make the options list shorter or longer, simply follow the same steps and select the required cell range under Source.