Excel is a great spreadsheet application developed by Microsoft, It features several tools to help you in making a spreadsheet or any sales chart, etc. But some of the features of MS Excel are little known, one of them is to create an Excel drop down list.

An Excel drop down list can be used when you want the user to choose from a given set of answers. It’s not something you would do everyday, but when opportunity strikes make sure you know how to create Drop down lists with ease. I’ll illustrate different methods, you can go with anyone you like.

So, actually you can create Drop Down List by 3 Steps

  • Using the Data from Cells
  • Entering the Data manually
  • Using an OFFSET formula

Let’s start by entering the data from the cells.

  • First, write down the list of items in the spreadsheet.
  • Now go to Data tab and click Data Validation.
  • A new window will appear, select the validation criteria as “List”.
  • As you select list, the source field will appear.
  • Click on the source field and select all the cells using the mouse pointer.
  • Click OK and your drop-down list will be created at the desired location.

→ Make sure you don’t disable the In-cell dropdown option. If it is disabled, you will have to type down the values manually. And the cell won’t take any value that is not on the list.

Now, moving on to entering data manually.

The above example works on the address of the cells, but you can directly add data in the source field as shown.

  • Select the cell where you want to create the list.
  • Again navigate to Data validation.
  • Select List from the dialogue box.
  • Now, the source field will appear. Type in the options separated by “, “.
  • Click OK.

This will create a drop down list from the elements entered in the source field manually.

Now, last but not least we can do it by using some excel formulas.

  • Write down the data in the cells as in the first method.
  • Select the target cell.
  • Navigate to Data Validation.
  • Select list as validation criteria.
  • In the source, field type the formula.

=OFFSET(REFERENCE,ROW,COLOUMN,[HEIGHT],[WIDTH]) “.

  • For example for the list given in the above image, the formula will be =OFFSET($C$3,0,0,7).

Now, these were the steps which can help you make a drop down list in no time, now let’s see something interesting using OFFSET.

Dynamic Drop Down List

The above formula works fine for creating a basic dropdown list. If you use the OFFSET function as shown above, even if you add more items to the list, the drop down list would not update automatically. You will have to update it manually everytime.

Using a Dynamic Drop down list saves this effort and automatically updates the list as you append any changes in it. So let’s begin with the dynamic list.

  • Select the target cell.
  • Navigate to Data Validation
  • Select List as the Validation Criteria.
  • In the source field enter the below mentioned formula.
  • =OFFSET($C$3,0,0,COUNTIF($C$3:$C$10,”<>”)).
  • Click OK.

I replaced the argument 7 with a COUNTIF statement. It will look for non-blank cells from C3 to C10 and include them in the list accordingly. So, we can add and delete any number of elements from our drop-down list.

Remark: Make sure there is no blank cell in between the filled cells. The function stops as soon as it encounters any blank cell.

Pro Tips

Pro Tip 1: You can cut/copy the drop-down list as, like any other element of the spreadsheet, it will retain the drop-down list.

But, if you place an empty cell on a drop-down list, it won’t be able to retain the list.

Pro Tip 2: It is hard to point out which cell contains the drop-down list as the arrow disappears if the cell is not selected. So to highlight all the cells with the drop-down list you can follow the below-mentioned steps.

  • Go to the Home tab.
  • Select Find & Select then Go to Special.
  • Select Data Validation and hit Enter.
  • This would highlight all the cells that have the data validation rule applied i.e. the drop-down cells.
  • You can change the border color of the cells to identify them easily.

Pro tip 3: You can create a fake drop-down icon on the right of the drop-down list which will appear as a real icon and will help to identify the drop-down list without messing with the colour formatting of the cell.

  • Select the cell to the right of the cell that contains a validation list.
  • Select the Insert tab on the ribbon and press the Symbol button.
  • On the Symbol window, select “Wingdings 3” from drop-down.
  • Find the symbol that looks like the down-arrow. Character code 128.
  • Press the Insert button, then the Close button.

So, that’s all for now, you can visit the official micosoft store to download the latest version of Excel and start working on all this.

 

Did you like the Article? Feel free to comment and share the post. Do bookmark Kickgadget for more amazing content!

LEAVE A REPLY

Please enter your comment!
Please enter your name here