E-xcel Tuts

Password Protect Excel File

noreply@blogger.com (Anant) — Wed, 21 Nov 2012 15:20:00 +0000

There are two reasons you want to password protect your excel file. Either you do not want other people to see sensitive data in you excel file without your permission or you want to restrict user from modifying your excel file. It can easily be achieved using Microsoft Excel only without using any third party password protection software. In this tutorial I am going to show you how you can password protect your excel file with excel only.

Password Protection Options

Excel files can be password protected using Microsoft excel in two ways.

Password to Open: When you want to prevent the users from seeing the data in your excel file then use this protection. User will need a password to view the contents of the file.
Password to Modify: You want to allow your users to see the data but want to restrict them from making any changes in your excel file, then this is the best option.

How to Password Protect Excel File

Open the file you want to protect and follow these simple steps to password protect you excel files.

Go to File menu and click on "Save As". A pop-up window for saving you document will appear on you screen.
Click on the "Tools" button on the bottom right hand corner and select "General option..". See image below.
Enter a password to protect your document and then click "OK". (See Image below).

Save As

General Options..

Search Box to Search and Display Data

noreply@blogger.com (Anant) — Fri, 30 Dec 2011 19:30:00 +0000

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.

Creating search box.
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.

Select cells "C2:E2" and click on "Merge & Center".
Select all the cell around the merged cells and fill it with black color.
Click on "B2". Change font color to white, font size to 12 and make it bold then type "Search".
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".
Fill with color as shown in the picture above.
Click "B5" hold Shift and click on F12.
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

Go to Sheet 2 in the same workbook.
Click on the formula tab and then "Define Names".
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

Go back to "Sheet1".
Click "C6" and type "=IF($C$2="", "", $C$2)".
Click "C7" and type "=IF($C$2="", "", INDEX(Age,MATCH($C$2,Name,0)))".
Click "C8" and type "=IF($C$2="", "", INDEX(Gender,MATCH($C$2,Name,0)))".
Click "C9" and type "=IF($C$2="", "", INDEX(Phone,MATCH($C$2,Name,0)))".
Click "C10" and type "=IF($C$2="", "", INDEX(Email,MATCH($C$2,Name,0)))".
Click "C11" and type "=IF($C$2="", "", INDEX(Address,MATCH($C$2,Name,0)))".
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

noreply@blogger.com (Anant) — Mon, 19 Dec 2011 05:20:00 +0000

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

Click "D2" in excel sheet.
Type " =IF(C2>=B2, "Within Budget", "Out of Budget") " without quotes.
Press "Ctrl + Enter" on keyboard.
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.

Click " E2 " in excel spreadsheet.
Type " =IF(C2>=B2, B2-C2, - (C2-B2)) ".
Press "Ctrl + Enter" on keyboard.
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

noreply@blogger.com (Anant) — Tue, 13 Dec 2011 17:30:00 +0000

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.

Right click on Sheet1 tab at the bottom left corner of excel.
Click on "protect sheet".
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.
Enter your password and click OK
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

Right click on the sheet.
Click "Unprotect Sheet".
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

noreply@blogger.com (Anant) — Sun, 11 Dec 2011 16:03:00 +0000

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

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

Formatting Chart

Click "Chart Tools".
Select layout 3 form chart layouts.
Right click on chart title and select edit text.
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

noreply@blogger.com (Anant) — Tue, 06 Dec 2011 18:36:00 +0000

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.

Look at the basic auto fill function in excel.
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.

Type "1" in cell A1.
Press Ctrl + Enter on keyboard.
Move your mouse pointer to the bottom right corner of the handler till it changes into a black " + "sign.
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

noreply@blogger.com (Anant) — Thu, 01 Dec 2011 19:53:00 +0000

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)

Comparison Charts With Excel

noreply@blogger.com (Anant) — Sat, 26 Nov 2011 20:08:00 +0000

Today we are going to learn how to make a chart which compares two or more data in a table. It is very useful when we want to compare between two values very easily within no time. As you will agree that comparing data with a graph is far more better than digging into a lot of data in a table. So lets get started. this is what we are going to create.

Follow these easy step by step method to create the chart as in the above image. Lets say we want to compare a list of items A to E for number of units in stock, purchased and sold.

Step 1

Create a sample data as below.

Step 2

Go to Insert > Charts > Column and then select Clustered Column chart from 2D Column.

Step 3

Select Layout 5 from Chart Layouts in Chart tools in Design tab.

Step 4

Right click on chart area and click on Select Data.

Step 5

In the Select Data source window click on Add.

Select the title "In Stock" for Series Name and Data below In Stock title for Series Values and click OK.

Notice a series In Stock is added in the Legend Entries (Series) list box. Now click on Edit button.

Select Item names A to E for Axis label range and click OK.

Similarly Add Two more series Purchased and Sold as illustrated above for Purchase and Sold units.

Step 6

After completing above five steps you should get a graph as shown in the image.

Right click on Axis Title and choose Edit Text. Delete the text and write "Number of Units". Select the text and change the font size to 12 and the result is. . .

Leave me a comment if you like this post.

Dynamic Chart

noreply@blogger.com (Anant) — Thu, 24 Nov 2011 19:44:00 +0000

Today we are going to make our charts dynamic in nature. By dynamic I mean that the chart will update automatically on adding new data to the table. As I always say, just follow these simple steps to make your charts update as you enter new data. You can use this tutorial to create resource chart also.

Step1:- First of all open excel and create sample data as in the image below.

Step 2:- Go to Formula tab in excel and click on Define Names. A pop-up window opens up.

              In Name write "Day".
              In Refer to write " =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1)".
              Click OK.

              Again click on Define Names.
In Name write "Resource".
              In Refer to write "=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1)".
              Now save your book. I saved it as "Dynamic.xls".

Step 3:- Now go to Insert > Charts > Column Chart and select Clustered ColumnType

Step 4:- Go to Chart Layouts and Select Layout 8 from the layouts.

Step 5:- Create the chart as in my previous tutorial Create a Simple Chart in Excel.

While Selecting data do follow these steps

In the series values write "=Dynamic.xls!Resoures".

Remember to replace Dynamic.xls with your own file name.

In the Axis Label Range write "=Dynamic.xls!Day"

Note:- Confusion regarding file extension as pointed out by my friend.

For Excel 2003 "Your_File_Name.xls".

For Excel 2007 "Your_File_Name.xlsx".

Above example is as per 2003 format.

Step 6:- Change Chart style to Style 16 and it should look like this.

Now try adding new data to your table, you will notice that it automatically updates in your graph. Leave a comment if it works.

Referred Links

Dynamic Charts (Peltier Tech Blog)

Add Data Labels to Excel Charts

noreply@blogger.com (Anant) — Tue, 22 Nov 2011 19:05:00 +0000

Last time we created a simple stacked column charts in 6 easy steps. Today I am going to teach you how to add data labels to your charts. This is what we are trying to achieve.

Earlier you learned how to create simple stacked column chart. Now to Add Data Labels to the chart follow these simple steps.

Step 1:- Open the chart which you have prepared. Now right click on the chart and click on Add Data Labels.

Now your chart should appear like the image at the bottom.

Step 2:- Again Right Click on the chart and then Click on Format Data Labels. A pop-up window will appear. In the pop-up window choose Label Option and check Value and Series Name check box and also click on the Inside Base radio button.

Now choose Alignment in the Format Data Labels Options, then from the drop down list of Text direction choose Rotate all text 270 degree.

There you go. You just added data labels to your chart and the final result is this.

Create a Simple Chart in Excel

noreply@blogger.com (Anant) — Mon, 21 Nov 2011 22:26:00 +0000

Today we are going to create a simple Column chart in excel showing sales per year.

Follow these simple steps to create a simple chart in excel. I am using excel 2007 for this example.
Step 1:- Create a simple table in excel as shown in the figure below.

Step 2:- First of all click on an empty cell, then go to Insert > Charts > Column and then click on Stacked Column.

Step 3:- An empty Chart Area will be created. Right click on the Chart Area and then click on Select Data.

Step 4:- Select Data Source dialog box will open. Now click on the Add button as in the image below.

Another pop up dialog box will appear. In the Series name text box select the title sales and in the Series values box click on the first data under sales and then click and drag it up to the last entry of the table.

Click on the first number under Sales hold Shift and then click on the last data.

And then click OK.

Step 5:- Now the earlier pop up window will reappear but a Series named Sales will appear on the Left hand side. Now click on the Edit button on the right hand side of the window.

One more window will appear. This time select all the years under the title Years and click OK.

Step 6:- Now again click OK on the pop-up window and the final chart will look like this.