Password protect an excel file with three levels of security

Excel has built in security to password protect your file. You can prevent unauthorized access by setting a password to open the workbook. It helps you to secure your sensitive information by allowing access only to the users with password.

You may have heard about the software which crack excel password. Yes, it works sometimes for excel 2003 and earlier versions. But Excel 2007 and above uses encrypted passwords for file opening, so it’s not easy to crack these, not at least with that downloadable software you find in search results.

Okay, so without deviating much, let’s learn about the security options you have in Excel. In fact, Excel does not only have the option to password protect the file opening, but there are a lot of other options available. Typically, you would like to do any of these for protecting your file.

  1. Password protect opening of the workbook
  2. Password protect modifying the entire workbook
  3. Don’t password protect but give a message and option to open it as read only
  4. Protect one or multiple worksheets from being modified
  5. Protect entire sheet except a few cell
  6. Protect the modification of worksheet without password
  7. Lock the structure of the workbook

Well, the list is longer, but Microsoft excel does not disappoint you. It comes with a number of security options which gives you complete flexibility. In this article, we will discuss the Excel security option at Workbook (excel file) level.

Excel File Protection

The best method to secure your excel file is by using a password protection for file opening. If you password protect an excel workbook, you provide access to only authorized persons to open or view the contents. Passwords used at this level are encrypted by advanced methodology and are highly secured. To set a password to open and view the excel file, go to ‘Microsoft Office Button’ and select ‘Save As’ Excel Workbook.

Excel Password Protection

How to password protect an excel file

Select ‘General Options’ from Tools. In the General Options dialog box, write the password to open and click OK. You will be asked to re-enter the password for confirmation. Now save the file.

password-to-open-excel-file

Set Passwords to open file

You have just set the password to open and view the file. For high security and sensitive data which you want to protect from unauthorized access, you should use this method. All other password protection options available in excel are not encrypted. That means they can be cracked with some effort.

There is another option which allows you to provide one additional password to modify the file. But as I mentioned earlier, this is not encrypted. You may wonder; where should I use this option? Well, in case you just want to prevent someone accidentally change the content, you can use a password to modify the file. In that case, anyone can view the file but only the authorized users can modify it.

But where should you use both these passwords? In a scenario where the data is sensitive and you want only the authorized persons to view the files and from these authorized persons only those can modify it who know both the passwords. This is the maximum level of security in excel you can have at workbook level!

Protecting Excel Workbook

Apart from preventing unauthorized access and modification, you can also restrict users from making changes by locking the structure of worksheets which restricts adding or deleting the sheets and displaying the hidden worksheet. Apart from that you also have the option to lock the size and position of worksheet windows. This will prevent users from moving, resizing or closing the windows.

Go to the Review tab -> Changes Group and select Protect Workbook

Protecting Excel Workbook

How to protect windows structure

Here you have option to lock the Structure and/or Windows. You have the option to provide an optional password to protect the workbook.

protection with additional password

Additional Password for Protection

When you lock the structure, you prevent users from

  • Add a new worksheet
  • Delete one or multiple worksheets
  • Hide a sheet
  • Unhide a sheet
  • Rename a sheet
  • Move a sheet
  • Copy a sheet to another file
  • Recording new macros

Tips

  • Use strong passwords – include numbers, special characters and combination of uppercase and lowercase letters which are not easy to guess for protecting confidential information. You can use phrases in place of words like – I_earned_$500_in_Year_2010. These are quite strong and at the same time easy to remember.
  • Don’t use Excel password protection to protect your other passwords. In other words, don’t write all your passwords in an excel sheet and use a password protection. It is always advisable to remember your passwords and change them periodically.
  • The data in the hidden worksheet cannot be encrypted with Workbook Protection. This is only meant to prevent users from accidental changes.
  • To remove the excel workbook protection, go to Review tab -> Changes Group and uncheck Protect Workbook. You may require the password if it has been used to protect workbook.

How to use Index Match Functions as alternative to VLOOKUP

If you know about VLOOKUP function, you must be aware that it is a powerful and handy tool for finding out values from a large data table. But have you ever heard or use INDEX or MATCH functions of excel? These functions actually don’t do much when used in isolation. But when combined, these become the best alternative to VLOOKUP or HLOOKUP and are even more efficient. There are many excel experts who suggest to use INDEX-MATCH instead of VLOOKUP.

To be frank, the idea of using two totally unknown functions for something which I am already used to is really bad. And using two functions in combination becomes a little more complicated to understand also. But there are situations when VLOOKUP will fail to retrieve what you are looking for and INDEX-MATCH will work happily. For larger worksheets with thousands of data-rows, using INDEX-MATCH is more efficient as it takes lesser time than VLOOKUP. So finally, I decided to start using it and found it really good.

INDEX() Function

INDEX function in excel is really simple to use and understand. Give it the row number and column number and it will throw the value from the defined data range.

Index function syntax

= INDEX(array,row_num,col_num)

If we simplify it in plan English, it will be something like:

=INDEX(data table, Row number of this table where your data is located, Column number of this table where your data is located)

In simplistic use, you will select the data table, put the row number and column number and INDEX will output the value as shown in the example below.

index-function

If we define our data table with range C2:D5 and are interested in getting the price of Banana, which is in row number 3 and column number 2 in this table, we can use this index formula.

=INDEX(C2:D5,3,2) which will return 4.

Notes:

  1. If the array (data table) selected by you contains only one row or one column, the corresponding row_num or col_num argument becomes optional. Thus the formula “=INDEX(D2:D5,3)” will return 4.
  2. The row_num and col_num values must correspond to a cell in the defined table; otherwise Index formula will throw #N/A error.

If you have followed the Index formula correctly, you will understand that you will probably not use this function in isolation.

MATCH() Function

MATCH function in excel is also very easy to understand. Basically it finds the position of a value in a row or column. So, if you are interested in finding just the relative position of a specific value in a list, you can use MATCH() function.

Match function syntax

 = MATCH(lookup_value,lookup_array,[match_type])

match-function

In the example above, Match function will look for the position of “Grapes” in the column range C2 to C5.

=MATCH(“Grapes”,C2:C5,0) returns 4.

The match_type argument is optional and just like VLKOOP, here is the option to find exact match or approximate match. Most of the times, you will be interested in getting the exact match, for which you need to pass the value as 0 for [match_type] argument.

It is better to know a few important things about [match_type] argument:

  1. You can choose from 0, 1 or -1 for this argument. If you leave it blank, excel will use the default value which is 1.
  2. If you choose 1, MATCH function will find the largest value which is either equal to or less than the lookup value. However, the lookup array must be placed in ascending order like 1,2,3,…….,9,10.
  3. If you choose -1, MATCH function will find the smallest value which is either equal to or more than the lookup value. Again in this case, you must first sort the lookup array in descending order like 10,9,8,……,2,1.
  4. Most of the times you will be choosing 0 which will fetch the exact match. Here you don’t need to sort your lookup_array.

Notes:

  1. You can use uppercase or lowercase in the lookup value if it is text. Match function will find the value irrespective of the case.
  2. If Match function is unable to find the value you are looking for, it will throw #N/A error.

In real world just like INDEX(), it is less likely that you will be using MATCH() function in isolation. But when you use these two functions together, it becomes really powerful.

INDEX-MATCH as VLOOKUP Alternate

So, what is actually this Index-match combination going to do for us? The concept is simple; Index will return the value you are looking for by getting a reference number and this reference number will be provided by Match.

The two function put together will look something like this:

=INDEX(The data range, Row number which will be retrieved by MATCH, Column number which will be retrieved by MATCH)

To understand this phenomenon with an example, please download this workbook. Let’s say we are interested to find out the marks of Alex (cell B11). Now, we will use both VLOOKUP function and index-match one by one.

index-match-vs-vlookup

VLOOKUP

As you know the syntax of VLOOKUP is

=VLOOKUP(lookup_value, table_array, col_index_numrange_lookup)

In this case, the VLOOKUP formula will be:

=VLOOKUP(B11,B2:E9, 4, FALSE) which returns 110

INDEX-MATCH

In first step, we will find the relative position of Alex with MATCH function

=MATCH(B11,B2:B9,0) which returns 5

In second step, we will get the marks obtained by Alex which is in the E column at 5th row from top.

=INDEX(E2:E9, 5) which returns 110

But now, we will replace 5 with the MATCH formula. So, the final INDEX-MATCH formula will be:

=INDEX(E2:E9, MATCH(B11, B2:B9,0))

In fact, you can straightaway use both INDEX and MATCH function after a little practice.

OK, we can retrieve the same result with both VLOOKUP and INDEX-MATCH. But why should we go with Index-Match when VlOOKUP can do this task equally well and is relatively easier to use?

Why INDEX-MATCH is better

Suppose, in the above example, we want to know the Imp ID of Alex which is in column A. Can you use VLOOKUP here without making any change in data table? The answer is no. This is because VLOOKUP can’t go in left direction.

With INDEX-MATCH, there is no such issue. Use the following formula:

=INDEX(A2:A9, MATCH(B11, B2:B9,0)) which will return A153.

INDEX-MATCH is Faster

There have been several tests to compare the timings for retrieving data with VLOOKUP and INDEX-MATCH. It has been found that in almost all cases, INDEX-MATCH is faster. I don’t want to take you through the process of testing though!

INDEX MATCH with multiple criteria

Unlike VLOOKUP, here you can use two criteria, one for row number and another for column number. That means it is possible to have two way lookup. For understanding purpose, I have created two drop down lists in cell G2 and H2. G2 consists of the names and H2 the fields which value we wish to have in cell I2.

In cell I2, we are using the following INDEX-MATCH formula:

=INDEX(A2:E9, MATCH(G2,B2:B9,0), MATCH(H2,A1:E1,0))

Basically, we are defining the complete data table here. There are two MATCH formulas here. For row_num; we are finding the relative position of selected name in column B and for col_num; we are looking at the relative position of fields in row 1.

Now, you can easily get all the details of the employees by selecting the name in cell G1 and detail field in cell H1. Thus we are using two lookups here which is not available by default in VLOOKUP.

Conclusion

If you do a lot of data handling, you should start using Index-Match functions now. I understand it will take some time before you get accustomed to it, but even VLOOKUP was quite confusing at the beginning, wasn’t it?

However, if you use VLOOKUP rarely and with less data, better to stick with it. After all, you don’t want to leave using something which you have mastered after so much practice!

Make your data entry easier with Drop down list

Drop down list in Excel allows you to restrict the data entry in any cell and at the same time, it also makes the data entry easier. In this tutorial, we will learn how to create drop down list, add items, remove items and where it can be used for productivity.

Drop down lists are widely used in excel to define a set of values for any cell, thereby letting the user select a value from a set of pre-defined values. These pre-defined values can be taken from anywhere else in the worksheet or workbook. You can easily limit the number of entries and prevent invalid data. Once you create a drop down list in any cell, an arrow is displayed. Any entry can be selected from a set which appears after clicking on this arrow.

Inserting a drop down list

Open a new excel workbook and select cell B2. We want a list to appear here from which one can select one fruit from a set of four. For excel 2003, go to data menu and select data validation. For excel 2007 and 2010, go to data tab and click on data validation from data tools group.

data-tools-group

The data validation dialog box is displayed. In the settings tab, you will find ‘allow’ drop down list; click on list in this menu.

Once you select the list, source text box appears. Write “Apple, Mango, Banana, Grapes”. Let the “ignore blank” and “in- cell dropdown” check boxes remain selected. Click on OK and you are done!

source-selection

fruit-list

Now you will see a little arrow appearing just right side of the cell B2. Click on this arrow and you are presented with the fruit list which you just entered. Try to enter some value other than list items. You get an error message and the entry you made is not accepted.

Source from a list in the worksheet

The above method to create a drop down list is the simplest and good if you have a small number of items. But if you have a huge list to select from, it will not be an efficient one to type all the data manually in the source box.

Let’s say you have a list of 20 numbers in the worksheet (from cell G1 to G20). You want to make a list of these twenty numbers in cell E1. Here, we will repeat the same steps as in above example till we get the source text box. Once the source text box appears, click inside it and then select the range which you want to include in the drop down menu. Optionally, you can type the reference of this range. Click OK.

list-same-sheet

The maximum number of entries that is possible in the drop down list is 32767. Well, that’s quite a huge number!

Source in another worksheet

The drawback using the previous method is that the data is in the same worksheet and is visible unnecessarily. It would be great if the source data is kept in another worksheet. It will also give us the flexibility to hide or protect this worksheet from unauthorized changes.

There are few extra steps in this case. You cannot just select the data source in another sheet. First we need to define the ‘name’ for this data list. Let’s create a drop down list in sheet2 from the same data in sheet1. Follow these steps:

  1. Select the range for list in the worksheet (in our case it is G1:G20)
  2. For excel 2003, go to insert – name – define. For excel 2007 and above, click inside the Name box which is located just at the left end of formula bar.

Name-box

Put a name (say validnumbers) and click enter.

Great! We have just defined the name of the range. Now go to another sheet (sheet2) and select cell E1. Repeat the steps for creating a drop down list till you get the source text box. In the source text box, write the name of the range followed by an “=” (equal sign). If you missed the equal sign (=), it will not work. For our example, type “=validnumbers”. Click on OK and you are done!

Advanced options

There are a few options which you can use depending on your requirements.

Input Message

You can optionally display a message when the cell is clicked. This can be handy when you wish to give some instruction or hint at the time of selection. For displaying the input message, follow these steps.

  1. Select the cell with the drop down list.
  2. Go to Data – Data Validation in Excel 2003 or click on Data Validation from Data tab in Excel 2007 or above.
  3. In the Data Validation dialog box, go to “input message” tab.
  4. The “show input message when the cell is selected” checkbox is already selected. Keep it as it is.
  5. Now type the input message title and message.

input-message-setting

 

Click on OK.

input-message-display

Allowing other entries

Sometimes, you want to allow the entries other than which are already available in the list. For example, you want the user to select his city. You have listed down the important cities for the convenience of entry but the list is not exhaustive. In that case, you would allow the entry of items which are not already in the list. That can be easily achieved with the following steps:

  1. Select the cell with the drop down list.
  2. Open the data validation dialog box and go to “Error Alert” tab.
  3. By default “Show error alert after invalid data is entered” checkbox is selected.

Clear this checkbox and click OK.

allow-other-entries

Now, apart from the existing list, you can also make other entries. In those cases, it is recommended that you also display an input message for clear understanding.

You can also display a warning message when someone tries to make entry which is not in the list. In this case, excel will allow the entry after showing the warning message. We will learn this in the next paragraph.

Customizing the error message

You have the option to customize the error message displayed at the time of invalid entry. By default, excel will display a message “The value you entered is not valid. A user has restricted values that can be entered into this cell” and will prevent the data entry. You have the option of both customization of the message and change the default behavior.

  1. Open the data validation dialog box and go to “Error Alert” tab.
  2. In the “style” dropdown list, you have three options – Stop, Warning and Information. If you select “Stop” (by default), excel will prevent the invalid entry. But if you select “Warning” or “Information”, a message will be displayed but the entry will be allowed.

You can write a custom title and message and click OK.

warning-message

warning-message-display

Using in combination of other functions

The drop down list can be combined with other functions of excel to make it more powerful and do some calculation based on the input value. Typically, you may like to use a nested if function to do some calculation based on the selected value.

Let’s understand it with an example. Suppose in a theater, the cost of the ticket depends on the choice of food served. The user can select from “beverage only”, “vegetarian” and “non-vegetarian”. We can use nested if function to calculate the ticket cost. See the screenshot below:

dropdown-with-if

Here in cell C3, we have used the following nested if function.

=IF(C2<>”Beverage only”, IF(C2=”Vegetarian”, 120, 125), 100)

Tips

  1. While, you can use a nested if function where the selection list is small. For a big list, it will be wise to use VLOOKUP function instead.
  2. You should adjust the width of the cell with drop down list. If it is less, the entry with larger text will be shown truncated.

What is Pivot Table – explained with Examples

If you are reading this article, I am sure you have heard about Pivot Tables. And if you complete this tutorial, I promise you will be expert on using Pivot Tables.

Pivot Table in excel is really amazing with powerful capabilities to sort, summarize and analyze the data. If you deal with a large amount of data and want to get quick summary of anything, Pivot Table is what you should look at. And rest assured, if you know how to use excel pivot table correctly, you are going to save a lot of your time and energy.

In this article, we will use practical examples to quickly understand how to create a pivot table and get the reports we are looking for. You may be wondering what is so special about pivot tables. Is it not like one of the other functions or formula? The answer is Pivot Table is not a formula but a report and much more than that. In fact you can do automatic calculations like sorting, averaging, filtering, editing, formatting and summarizing your data which can help you to quickly analyze the statistics you wish.

So, without wasting any time, let’s start our quick journey of learning Pivot Tables with this example. Well, this is not the usual sales stuff; we will try with something different here.

Excel Pivot Table Example

We take the example of class attended and marks obtained by different students in different year. Every year, the section of students gets shuffled. The data table looks like this.

data-table

With this data, we will extract the following:

  1. The best performing students
  2. The best section
  3. The best year with respect to students’ performance
  4. Average number of class attended for each section
  5. Average marks obtained by each student
  6. The best performing section in year 2009 & 2010 put together
  7. Get section wise total marks obtained in various years

And so on…. In fact you can have hundreds of similar queries and reports. And if your data base is large, it will be difficult to get these answers without using pivot tables. But with Pivot tables, you can get and arrange these reports in just a few minutes. It’s Childs’ play, literally!

It will be better if you download this excel file containing this pivot table example and practice with it as we go ahead.

excel-pivot-table-example

How to create a pivot table

Now the first task in our hand is to create the pivot table layout. Select the range of data table you want to analyze (in our case it is A1:E22). Now go to ‘insert’ ribbon and click on PivotTable. In create PivotTable wizard, you will be asked to select the data range the location where you want your pivot table to be placed. Since, we have already selected the data, just click on OK and a blank pivot table is inserted in a new worksheet.

insert-pivot-table

The playground is now well set for you to play with data!

pivot-table-field-list

Here are the rules of this game.

  1. There are four areas to which you can drag the fields to – Report Filter or Page Fields, Column Labels, Row Labels and Values.
  2. You can move the fields from one area to another. You can remove any field from the all the areas by dragging it outside the PivotTable report.
  3. Any area can contain zero, one or more than one fields.

As you can see in this example, we have five fields to play with – Name of Student, Section, Year, Class Attended and Marks Obtained.

The game begins!

First Goal: Get the best students

Drag the Name of Student field in Row Labels area and Marks Obtained in Values area like the picture below.

pivot-table-drag

And the data is populated exactly as per our requirement. It’s that simple.

But if the number of students is large, you may like to sort the marks obtained to know about the best students.

Here is how to sort the data in pivot table.

  1. Click on the drop down menu next to the ‘Name of Student’.
  2. Select ‘More Sort Options’.
  3. Select ‘Descending’ from the sort options and Sum of marks obtained from the dropdown list. Click OK and you are done.

field-sorting

With this report, John is our best student followed by Raj and Neha.

Second Goal: Get the best Section

  1. Remove ‘Name of Student’ field from ‘Row Labels’ area by dragging and dropping it anywhere outside the pivot table.
  2. Drag the ‘Section’ in ‘Row Labels’.
  3. Now double click on the ‘Sum of Marks Obtained’ in pivot table. Value Field Settings wizard appears.
  4. From ‘Summarize value field by’ selection box, select Average and click OK.

value-field-settings

Section B is the winner followed by C and A. (I know steps 3 & 4 are not required, it is for learning about value field settings)

To make things more interesting, drag the ‘Name of Students’ also in the Row Labels. What you get? It’s the average of marks obtained by each student grouped by sections. Interesting, isn’t it?

group-summary

Goal three, four and five

  • The best year with respect to students’ performance
  • Average number of class attended for each section
  • Average marks obtained by each student

Well, it’s your home work. Go ahead find these out yourself. If you have followed this tutorial well till now, it should not be a difficult task for you.

Goal Six

  • The best performing section in year 2009 & 2010 put together

Now, this is something different as we need to filter the data containing 2009 and 2010. But it is also very simple.

We just need to drag the ‘Year’ field in ‘Report Filter’ area. With this, in pivot table, you will find Year with a dropdown list.

data-filter

Click on the dropdown list and you can select the year by which you want to filter the pivot table data. But here we need to have multiple selections (2009 and 2010). Check the ‘Select Multiple Items’ and select 2009 and 2010. Click on OK and you are done.

filtering-multiple-years

Goal Seven

Our last goal is to get section wise total marks obtained in various years. Follow the following steps.

  1. Drag ‘Marks obtained’ in values field.
  2. Drag ‘Year’ in Row Labels
  3. Drag ‘Section’ in Column Labels

report-filter-alternate-layout

You got the section wise marks obtained in various years. Try interchanging Year and Section fields – i.e. Year in Column Labels and Section in Row Labels. The layout of pivot table changes accordingly.

Conclusion

I am sure you liked playing with Excel Pivot Table. These examples give you the overview of what is possible with pivot table in excel. For better understanding, I recommend you to get a few more pivot table reports from this example and practice.