Random Tutorials
Browsing all articles in Random Tutorials
0
Posted May 27, 2009 in Random Tutorials by admin

How to create Drop Down Box in MS Excel Cells using Data Validation

The idea behind this tutorial is to learn how to use the Data Validation function of Microsoft Excel to insert a Drop Down Combo Box with Options to select from so that users have the option only to select out of those pre-defined options while filling inputs to an excel cell.

I have taken some screenshots which should be preety self explanatory. Please check them out in order to understand our basic objective. I bet, you will find it really simple!

Okay, to take a peek, this is what we are going to do:

msexcel_data_validation

So, let’s see how it can be done. First of all, we need to make a LIST of options that can be selected from the drop down cell. To do that, write down all the options that you want to insert into another sheet and give it a Name. Confused? Check the following image and you will get it quick! You just need to Select All the options and change the text in the Excel Name Box, say we have selected A1:A4 and gave the name cat1.

drop_down_excel

Once we have given a name to our option List, we can insert it to the cell where it is required. To do that select the cells where you want to insert the Drop Down box and go to Data -> Validation in the menu bar.

excel_data_validation

The Data Validation dialog box will up. You need to select List in the Allow: field and then enter the name of the List that you have created above (cat1) after an equal to sign, just the way shown below. Click OK and there you go!!!!

exceldatavalidation1

I hope it was easy to understand. Please comment if you have any trouble in implementing it and I will sure reply to you. Have a great day.

1
Posted April 12, 2009 in Random Tutorials by admin

Tutorial: How to use Excel vlookup with Examples and Samples

I have been working on excel for the last 6 month for my official data analysis and I have come across many new things which make things much simpler. Of course, these things are not new to many people but for newbies like me, it’s exciting to find many such useful functions integrated in the excel itself. There are lots of useful functions in excel and one very powerful one is the Excel VLookUp function. It’s so easy and so useful once you know what you are doing.

Okay, let’s see what VLookUp is and how it works. Below is the VLookUp definition from Microsoft.

Searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data you want to find.

The V in VLOOKUP stands for “Vertical.”

The Syntax to Microsoft Excel VLookUp is given below:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

You can see below the step by step process to apply vLookup to any cell in excel. Please download the full steps-by-step tutorial with a sample excel file Here.

VLookUp Intro:

vlookup_tutorial

Here we are going to look up for the empty fields in the destination table from the source table for corresponding rows.

How it works (partial):

vlookup_tutorial_1

vlookup_tutorial_2

You can download the Full Tutorial, along with the sample file Here.

And if you have any question, just let me know. Cheers.