How To Make a Dropdown List in Microsoft Excel

You have seen online application forms where the Country Names are selected from a drop-down box. Or to select the Name of an Employee, or Cost Center Codes, or Departments etc.?

Drop Down list of Countries in Excel
This kind of list serves 2 purposes:

1. It makes it easy for the user to select a value, rather than type it.

2. It makes the data consistent. No garbage values come in. The user can only select from the list of values provided.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn theĀ  Pivot Table techniques in our
Pivot Table Masterclass Training
!


Well, it is extremely easy to make a drop down list like this in Microsoft Excel. Here’s how you do it, step by step. And this will work in any version of Excel – Excel 2003 , Excel 2007, Excel 2010 and Excel 2013.

1. First of all, make a list of all the country names you want to display in your drop down list.

2. Select all the country names, and then go to Formula tab > Name Manager, and click New button.

3. Type a name like countries for the range. You must make sure you write it as one word. Range names can not have blanks, spaces or special characters, except for the Underscore.

data_validation_list
4. Now go to the cell where you want to place the dropdown box. So let’s say you go to cell A1. Click and stay in Cell A1. Then click the Data tab > Data Validation.

data_Validation
5. Pick List from the Allow: dropdown box. See screenshot above.

6. Key in the Source as =countries. This must be the name of the range you just created in Step 3. Now click OK to close this dialog box.

7. Once this is done, you will see a drop down arrow showing up in the cell C1. You will see the list of countries showing up here.

drop_down_filled_valuesOnce you have selected a value, it will be displayed in cell C1. No mis-spelt values. No garbage. Pure, good, data validation at its best!

You could have your master list of countries on another sheet in the same workbook, or in the same worksheet. If you do not want your users to see the individual names, you could hide the sheet and even protect it from any changes. But these things will be covered in a separate lesson.

Hope this helps. Do post a comment if you enjoy this little tip!

Cheers,
Vinai

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

Showing The Hidden First Column in Excel

It is quite easy to show or hide columns in Excel. Simply select a column, and press Control + 0 . The column is hidden from the view. You can make it out because of a dark line separating the columns. If you hide the B column, you will only see column A & Column C, and it is apparent that column B is hidden.

To unhide, simply select both column A & Column C. Then right click and select Unhide. Column B is brought back into view.

This works great most of the time. And it works in showing or hiding rows too.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn theĀ  Pivot Table techniques in our
Pivot Table Masterclass Training
!


But the problem arises when you want to hide the first column – Column A. Now it is difficult to select 2 adjacent columns, and you are unable to Unhide Column A.

Steps to Display the Hidden First Column (Column A) in Excel

  1. Press F5. The Go To dialog box will popup.
  2. Key in the cell A1 in Reference, and press Enter.
  3. The cursor would have moved to the cell A1, even if you can not see it. Do not worry.
  4. Now go to the Home Tab (in Excel 2007, 10 & 2013)
  5. Click on Format button – it is near the far end of the screen… toward the right side of the ribbon.
  6. Choose Visibility > Hide & Unhide. Then select to Unhide Columns.

    Unhide columns or Rows in Excel
    Unhide columns or Rows in Excel
  7. Voila! You will now be able to see the column A. It has been un-hidden.

It is a simple trick. Excel is all about simple tips and tricks… The more you practice, the more you try, the more gold shall ye find!

Cheers,
Vinai Prakash

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

Solving Everyday Problems in Excel [Survey]

Each week I receive questions from people facing issues with Excel. Some need help on Excel Formulas, Pivot Tables or Macros.

What issues are you facing in Excel?
[wwm_survey id=”0″]

Thanks. This will help me to create more useful Excel Tips and Tricks, that will address the issues you face with Excel.

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!


Cheers,
Vinai Prakash