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

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

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.

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.

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.

   

Comments (4) | Trackbacks (0)

How to Import a Sheet from one File to Another File?



This article helps you to make an exact copy of your worksheet including all formatting into another file.

Let us take two files, ‘File1’ and ‘File2’ viagra generico for example. Now I will explain how to import a sheet from ‘File1’ to ‘File2.

Select the sheet tab you want to import from ‘File1’ to ‘File2’ and right click on it as shown in the picture below:

Choose “Move or copy” from the window.

At the top, choose the workbook you want to make a copy to, (example: File2.xls).

At the bottom of the window check the “Create a copy” box
then select ‘OK’.

Now you can see the imported sheet in File2.

If you

want to import a sheet from a file to a new workbook, then follow below steps:

At the top, choose new book.

At the bottom of the window check the “Create a copy” box
then select ‘OK’.

 

   

Comments (0) | Trackbacks (0)

How to Avoid Screen Updating in Excel VBA?

This article helps to handle the screen updates using macro. We can speed up our macro by turning off the screen updating while the macro runs, by adding the below line of code to the macro:
Sub Macro ()
Application.ScreenUpdating = False

Write the necessary code Here

——————-

——————-
Application.ScreenUpdating = True
End Sub.

The prior versions of excel 2000, it was not required to turn ScreenUpdating back to

true. When the macro is finished, Excel would revert back to turning the screen updating to true.

But now whether you are writing macros in Excel 97, Excel 2000, Excel 2002, 2003, 2007 or 2010 always we have to turn the screen updating back to true with the below code:
Application.ScreenUpdating = True

Vani is a Business Associate with p2w2, a Spreadsheet Solutions company. p2w2 has expertise in Excel Modeling, Excel Dashboards, Profitability Analysis, Excel Invoicesand Excel Bid sheets. You can contact us by email: cs [at] p2w2.com or call us at 305.600.0950.

   

Comments (0) | Trackbacks (0)

More Articles

How to do a Two-Way Lookup in Excel?

How to get a "Print PDF" Button in Excel?