Dropdowns in Excel Part 1

What is a dropdown?

I’m 100% sure most of you reading this post, know what a dropdown is. We interact with hundreds of websites and user forms every day. Dropdowns are such a beautiful user interface objects, that they make the lives of the user and the one who creates the form much happier. It reduces the pain of typing something in the box for the user and the pain of resolving inconsistencies in the data for the form creator. Let it be choosing the person title (Mr./Ms./Mrs.) to choosing the city you’re from to anything under sun. Let me start with the very basic things in this post, which many of you might already know. But I’ll slowly come to the very interesting ones in the subsequent blog posts.

How is dropdown useful in Excel?

Excel is unarguably one of the best data entry software and the most widely used across the fields and by a cheap viagra wide range of people.

From a user perspective, when I’m entering a number of rows of data, it’s difficult for me to type the text in a particular column every time I create a new row. Let’s say I am entering the transactions happening in a vegetable stall. Then I need to type the name of the vegetable every time a transaction happens, number of Kilograms the customer took, the amount he/she paid etc. And when I’m analyzing at the end of the month to understand the sales of each vegetable, I will have to add new vegetables to the dictionary,

  • Tomoto
  • Tamoto
  • Tamato
  • Tomat etc. 🙂

I don’t make these mistakes because I don’t know how to write a simple word “Tomato”; but while entering loads of data I tend to make small mistakes, which will trouble me a lot at the end. So, a dropdown is inarguably one of the most useful data entry elements in Excel.

How to create a dropdown list in Excel?

There are multiple ways in which you can create a dropdown list in Excel.

  • Data Validation
  • Form Control (Combo Box)
  • ActiveX Control (Combo Box)

But of these methods, Data Validation is the most used method, for its simplicity. Later on, I’ll explain the pros and cons of each method, so that you can choose the best method among these for you.

Creating a dropdown list in Excel through Data Validation

Before going to explain how to create a dropdown list, let me tell you what a Data Validation is? It controls the type of data or the values that users enter into a

cell. For example, you may want to enter only positive values in a cell, only ‘time’ in a column, or a text below the specified length. You can define all these conditions through Data Validation. Even dropdown list is a data validation where you’re constraining the user to use only those set of words in that cell. Again, in creating a dropdown list through data validation, there are different ways in doing it. Here are those…

Writing the complete list in the Source Field

1)  Select the Cell (or) set of Cells in which you want to see the dropdown.
2)  Go to “Data” Tab –> Data Validation –> Data Validation
3)  Select List in the “Allow box”
4)  Just enter the names you want to see in the dropdown list in the “Source” Box

Dropdown in excel through Data Vaidation

That’s it!!! Go to the cell for which you created validation. Click on the dropdown button, you’ll be able to see the names of the vegetables you’ve entered.

Excel Dropdown Names in the Source Field

Selecting a list of items in the same or other sheet

In the aforementioned example, instead of entering the names in the source field, you can choose a list which is already entered in the same or different excel sheet.

The best advantage of this method is, if you remove the vegetable name ‘Carrot’ from the list and replace it with ‘Lady Finger’, it will automatically update the list without your intervention. The only problem with this method is, if you append an item to this list at the end, it won’t get reflected in the dropdown.

Selecting a List of Items from the Same or Other worksheet

Using Dynamic Defined Names

1)  Select the list you want to be shown in the dropdown
2)  In the Name Box which is beside the formula bar, enter the name and click ENTER
3)  That will name the selected table as written in the “Name Box”
4)  In the Source Field, write “=” followed by the Table Name you’ve selected

There it is! You’ve created a dropdown!! It’s as simple as that!

“Is that all about dropdowns?”

“No! Please wait a moment!”.  This is just the beginning and it’s going to get more interesting in the next posts in this series. I will be back with a post on Friday and till then adieu.

How to Make Validation Case Sensitive?

Chaitanya is CEO of p2w2, a Spreadsheet Solutions company. p2w2 has expertise in

Excel Modeling, Excel Dashboards, Profitability Analysis, Excel Invoices and Excel Bid sheets. You can contact us by email: cs [at] p2w2.com or call us at 305.600.0950.

How to Create Dropdowns in Excel

You can learn how to create drop downs in excel. In addition, you can also learn how to allow unlisted entry into

the drop down field.

—————————————————————————————————————————————————-

Chaitanya is CEO of p2w2, a Spreadsheet Solutions company. p2w2 has expertise in Excel Modeling, Excel Dashboards, Profitability Analysis,  Excel Invoices and

Excel Bid sheets. You can contact us by email: cs [at] p2w2.com or call us at 305.600.0950.

How to Change Text Like YYYYMMDD into YYYY/MM/DD format?

Often, you get text like 19950607 and you want to be able to convert it into text like 1995/06/07.  Here’s a formula you can use to convert the text into

a formula. (this assumes the text is in A1)

=DATE(MID(A1,1,4), MID(A1,5,2), MID(A1,7,2))

What this does is this:

Date formula has the following parameters – DATE (Year, Month, Day)

The formula I wrote about simply splits the text

into Year, Month and Day and uses it in the DATE function.

MID function allows you to split text and pick up parts of it. Here’s the syntax

MID(text, start number of digit you want to start from, number of digits)

MID(A1,7,2) – picks up the text 19950607 (i.e. A1) starts from 7 i.e. 0 and gives you two digits from there – 07

—————————————-

Excel/Financial ModelingProfitability/Cost AnalysisDashboard ReportingCustom Invoice