Search Box to Search and Display Data

---

In this excel tutorial, we are going to learn how to make a search box. After creating search box, we will use it to search and display data from the database. In the above video you can see what we are going to create in this tutorial. We are going to do this in two steps.

1. Creating search box.
2. Displaying search result.

Creating Search Box 

In this section we are going to create search box and the display box. Instruction for creating search box and display box are given below.

1. Select cells "C2:E2" and click on "Merge & Center".
2. Select all the cell around the merged cells and fill it with black color.
3. Click on "B2". Change font color to white, font size to 12 and make it bold then type "Search".
4. Select cells B5 to F5, hold Ctrl and select C6:F6, C7:F7, C8:F8, C9:F9, C10:F10, C11:F11 and C12:F12 and click on "Merge & Center".
5. Fill with color as shown in the picture above.
6. Click "B5" hold Shift and click on F12.
7. Click on "Thick Box Border" from the border options.

Displaying Search Result 

Here is the sample data which I will be using in this tutorial to show you how you can search and data from the data base.

Click Image to Enlarge

1. Go to Sheet 2 in the same workbook.
2. Click on the formula tab and then "Define Names".
3. Create the following named ranges i.e. Name, Age, Gender, Phone, Email, Address, and Country and type in the following formulas in "Refers to:" text box respectively

• "=OFFSET(Sheet2!$A$2,0,0,COUNTA(Shee2!$A:$A)-1)" 
• "=OFFSET(Sheet2!$B$2,0,0,COUNTA(Shee2!$B:$B)-1)"
• "=OFFSET(Sheet2!$C$2,0,0,COUNTA(Shee2!$C:$C)-1)"
• "=OFFSET(Sheet2!$D$2,0,0,COUNTA(Shee2!$D:$D)-1)"
• "=OFFSET(Sheet2!$E$2,0,0,COUNTA(Shee2!$E:$E)-1)"
• "=OFFSET(Sheet2!$F$2,0,0,COUNTA(Shee2!$F:$F)-1)"
• "=OFFSET(Sheet2!$G$2,0,0,COUNTA(Shee2!$G:$G)-1)"

Formula To Enable Search

1. Go back to "Sheet1".
2. Click "C6" and type "=IF($C$2="", "", $C$2)".
3. Click "C7" and type  "=IF($C$2="", "", INDEX(Age,MATCH($C$2,Name,0)))".
4. Click "C8" and type  "=IF($C$2="", "", INDEX(Gender,MATCH($C$2,Name,0)))".
5. Click "C9" and type  "=IF($C$2="", "", INDEX(Phone,MATCH($C$2,Name,0)))".
6. Click "C10" and type  "=IF($C$2="", "", INDEX(Email,MATCH($C$2,Name,0)))".
7. Click "C11" and type  "=IF($C$2="", "", INDEX(Address,MATCH($C$2,Name,0)))".
8. Click "C12" and type  "=IF($C$2="", "", INDEX(Country,MATCH($C$2,Name,0)))".

Now click on the search box and type a name you want to search. If the name you are searching is in the database, it will be displayed in the search "Search Result...". If the name you are searching is not in the database it will display "#N/A" in all the fields except name field. If  you like this tutorial or if you have a query, leave a comment at the end of this tutorial. Happy searching.

Excel 2007: IF Statement

---

In this excel tutorial, we are learn about "if statement" in excel and how to use it in excel.

If statement is used in decision making process in excel. If a condition specified by you satisfied then it gives you one value and if it is false then gives you another value.

IF Statement Syntax

=IF(logical_test, [value_if_true], [value_if_false])

logical_test: It is the condition that you want to test.

value_if_true : It returns this value when above condition is true.

value_if_false : It returns this value when specified condition is false.

Example:

Suppose you want to purchase four equipment. Your budget to purchase these equipment is as shown in the table below. Now, you want to find out whether all the equipment are within your budget or not. 

Using IF Statement

1. Click "D2" in excel sheet.
2. Type " =IF(C2>=B2, "Within Budget", "Out of Budget") " without quotes.
3. Press "Ctrl + Enter" on keyboard.
4. Click at the bottom right corner of the selection box and drag down to fill row D2 TO D5.

If statement can also be used to perform mathematical operations when a specified condition is satisfied as shown in the below example.

1. Click " E2 " in excel spreadsheet.
2. Type " =IF(C2>=B2, B2-C2, - (C2-B2)) ".
3. Press "Ctrl + Enter" on keyboard.
4. Click at the bottom right corner of the selection box and drag downwards to fill row E2 to E5.

The positive value in the above example indicates price more then the budget and negative value indicates price lesser than budgeted price. If you like this post leave a comment. 

Excel 2007 Password Protection

---

Many of my friends were asking me, how they can password protect their excel 2007 sheets. So, this time I am going to show you, how to password protect your excel files without any software.

Scenario I : Data in Single Excel Sheet
You have important data in an excel file and you do not want others to make changes in sheet. They are allowed to make changes in your sheet only when they have password. Follow these instructions.

1.  Right click on Sheet1 tab at the bottom left corner of excel.
2.  Click on "protect sheet".
3.  Un-check all the text boxes (See Picture). If you un-check all the check boxes, then others can only view your excel sheet. But they cannot make any changes without a password. 
4.  Enter your password and click OK
5. Confirm password by typing again in pop up and then click OK.

Your worksheet is now protected. You and others can make changes in the worksheet only after typing in the password to unprotect it. To unprotect it

1. Right click on the sheet.
2. Click "Unprotect Sheet".
3. Type in your password in text box and click OK.

Scenario II : Data in Multiple Excel Sheet

If you have multiple sheets, then choose the sheet you want to password protect. Then follow the instruction given above for data in single excel sheet.

Excel: Combine Charts

---

What are combination charts?

Combining charts are charts made by combining two types of charts in one single chart.

What is the use of combining charts?

It makes the data more interpretable. In my previous tutorial I covered comparison chart. It compared three data in one graph, but it lacks interpretability. Combination charts are very easy to understand, especially when a comparison is made between two data.

Creating Chart

Let us make a comparison between inflation and unemployment rate in India during the period of year 2002 to 2010. These are the actual data I found at Index Mundi. Start with making a table in the picture.

Go to Insert > Charts > Line and click Stacked Line with Markers to make a graph as in the picture below. See create simple charts in excel on how to make graphs in excel.

Combining Chart

1. Right click on line representing inflation (blue line) in the graph.
2. Select "Change series chart type".
3. Click " Clustered Column " from change chart type pop-up.
4. Click OK.

Formatting Chart

1. Click "Chart Tools".
2. Select layout 3 form chart layouts.
3. Right click on chart title and select edit text.
4. Type Inflation Vs Unemployment.

The final result is:

From the above chart you can compare inflation and unemployment with ease as compared to above line chart. It can easily be interpreted that column chart is showing inflation rate and the line graph is showing unemployment rate over the years. If you like this post leave a comment and share with your friends.

Custom Auto Fill in Excel

---

Custom auto fill is a function used to auto fill custom text, name, number etc.. It is particularly useful when you have to type the same text again and again which is very irritating. This trick will help you save time and make you more efficient at work.

Here is what we are going to do today.

1. Look at the basic auto fill function in excel.
2. Creating custom list of auto fill in excel (i.e. user defined auto fill).

Basic Auto Fill Function

This example will give you an idea of auto fill function.

1.  Type "1" in cell A1.
2.  Press Ctrl + Enter on keyboard.
3.  Move your mouse pointer to the bottom right corner of the handler till it changes into a black " + "sign.
4.  Hold Ctrl and then click and drag downwards. You will notice that the column is filled with ascending    number.

In a similar way you can auto fill name of months and days.The only thing you have to remember in case of months and is that you don't need to hold control. Simply type the first month and drag along the direction in which you want months to appear. You can either drag horizontally of vertically depending on your requirement. It works in both the directions.

This function is particularly useful in case of formulas. Suppose you have formula in C1 for adding values in column A and column B. See image below for description.

Type in the formula "=A1+B1" in cell C1.

Press Ctrl + Enter on the keyboard.

Move your pointer to bottom right corner till it turns into plus sign and then click and drag to fill the sum of column A and column B.

Custom Auto Fill Function.

To make custom auto fill possible, we must first define the custom fill i.e what we want to fill. Instruction to do this is below.

Click on Office button at the top of excel 2007 sheet.

Click Excel option at the bottom right corner of the office button window.

Click Popular.

Click on Edit Custom Lists... button

Type the custom list in List entries separated by comma.

Click Add and then OK.

Now go to excel sheet and type India and then click and drag. The columns or rows will auto fill with the custom values we just created. Your comments will be greatly appreciated to improve these tutorials. 

Combo Box Dynamic Charting in Excel

---

In this excel 2007 tutorial I am going to show you how to create a drop down combo box to chart source data. We will create a drop down box in excel, which will show the name of the cities in the drop down list. On selecting a city in the drop down list, it will automatically create and display sales data in the form of chart in excel. So let's get started.

Create Sample Source Data.

Open excel 2007 and make a table similar to this.

Create Drop Down Combo box.

1. Go to  Developer  tab.

2. Click " Insert " in Controls
3. Select " Combo Box " in Form Control.

4.Hold down "Alt " and drag towards right to fill cell " H2 " completely.

Format Control for Combo Box
1. Right click on combo box and select "Format Control".

2. Select City Names in Input range in format object dialogue box.

3. Click " Cell link " and select cell " G1 ".

4. Click OK.

Checking Combo Box
1. Click on black triangle on combo box.
2. Select any city from drop down box.

Creating Chart with Source Data
1. Click " A10 " and type " =INDEX(A3:A6, $G$1) "and hit Enter.

The city you selected earlier from drop down box will appear in cell A10.

2. Again click A10.
3. Click on the bottom right corner of the cell and drag horizontally to auto fill all the months in row 10.
4. Click A9, hold shift on keyboard and then click on cell F10 to select all the data for charting.

5. Go to Insert > Charts > Column and Select Clustered Cylinder chart.

6. Click on chart and Move it Just below the combo box. 

Now try changing city from the drop down box and you will observe that it charts data for a particular city selected from the drop down box. Comments regarding improving the tutorials will be highly appreciated.

Referred Links

Combo Box Animation (Flash Lite Tutorial)