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,

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.

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

online canadian pharmacy viagra

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.

[Bloglines] [del.icio.us] [Digg] [diigo] [Facebook] [Furl] [Google] [kirtsy] [Ma.gnolia] [MySpace] [Propeller] [Reddit] [Simpy] [Sphinn] [StumbleUpon] [Technorati] [Windows Live] [Yahoo!]


5 Comments

  • By Martin, October 3, 2012 @ 5:16 pm

    Maybe basic to you, but very useful to me as I am just starting to find my way around Excel. Many thanks :)

  • By 100% RESIDUAL COMMISSIONS!, October 9, 2013 @ 12:32 am

    100% RESIDUAL COMMISSIONS!
    Discover how you can collect
    100% RESIDUAL COMMISSIONS
    on an industry leading marketing tool
    suite that includes:
    • Leading Autoresponder service
    • Amazing conference software
    • Top notch video email service
    • Amazing lead capture system
    • Get this complete suite of profit pulling tool and training for ONLY $24.95 per month!
    Go To:>> http://www.pureleverage.com/launch/3?id=gandd

  • By shereen, November 26, 2013 @ 10:22 am

    Data Synergo provides services that drive results in customer management cycles. An innovative provider of marketing services, we provide practical solutions for bridging the business relationship between clients and their customers. Our services support customer management activities such as demand generation, lead qualification, customer acquisition and retention, event management, data analysis.

  • By design and drafting, March 12, 2014 @ 11:08 pm

    I will apply and practice it..very interesting and information. It keeps me motivated.

  • By Michaela, March 31, 2014 @ 3:38 pm

    good description! I understand it well. @ all guys: If you have problems with some functions in excel, help yourself with the following website: http://www.excel-aid.com/excel-font-changing-font-font-style-font-size.html It show you step by step how you have to work all of the functions, which maybe have not known.

Other Links to this Post

RSS feed for comments on this post. TrackBack URI

Leave a comment