How to break password of protected sheet in Excel without any software – In 30 seconds

Break password of protected sheet in Excel without any software

There may be chances that you forget password of protected sheet or you want to remove password of protected to enable edit. You can’t edit sheet if same is protected and if you try to edit then below warning pop up display.

 

So this post is for those people who want remove password of protected sheet & transfer protected sheet from unprotected sheet and that too without any software.

Lets Start 

1. Change file extinction to zip

If you can view extension of file then you can change it to zip easily. In case you are not viewing extension then follow below steps

A. Click on Start

B. Type Folder Option in search box

 

C. Open Folder option

D.   Untick on Hide extension for Known file types

 

Now you can able to see file extension and change it to zip

2.  Edit file

Open zip folder and copy sheet 1 (your sheet name may be different ) from worksheet folder and paste the same in outside zip folder

Open that sheet with Notepad by right click and open with option.

 

Then delete sheetProtection tag and save the file

<sheetProtection password="C746" sheet="1" objects="1" scenarios="1"/><pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>

3. Replace file

Replace this new file with old one in worksheet folder.

4. Change file extension to xlsx again

Now the last step is to change zip folder extension again to xlsx and open the file. You can now able to edit the sheet and no more password restriction for sheet.

Hope you understand

Watch Video

Thank you..Enjoy Excelling…Have a nice day..

Comparison Between Index Match and Vlookup Formulas in Excel

Comparison Between Index Match and Vlookup

There two most powerful lookup formulas in excel. First one is Vlookup and second one is Index Match. Vlookup is most common formula nowdays and it is very much popular in Excel. On the other hand Index Match is not so popular but it works same as Vlookup. In fact Index Match is two different formulas Index and Match which combine to get result of lookup. In this post we compare both formulas with different criteria to understand which one is need to be used.

Lets Start -

1. Easy to Understand - 

Most of the people understand Vlookup is very easy as compare to Index Match. Index Match is more large formula compare to Vlookup and it has include two ranges so user may confuse which range to use.

Syntax of Vlookup - 

=VLOOKUP(lookup value, range containing the lookup value, the column 
number in the range containing the return value, 0) 

 Syntax of Index Match -  

=INDEX (column to return a value from, MATCH (lookup value, column to lookup against, 0)) 

Thus most of the people use Vlookup formula as they may be think Index Match is difficult to remember or they don't aware about index match function.


2. Delete / Insert Column - 

There may be chances that you want to delete or insert extra column in sheet after completing sheet.
If in that sheet vlookup formula is included then after deleting or inserting column that formula has #REF reference error.
But it is not the case in Index Match Formula. You can delete or insert column which is not used in formula iteself but formula works fine.
So Index Match is better than vlookup to avoid errors in future. 

3. Right to Left - 

Vlookup only works in one direction i.e. left to Right. If suppose column to return value from is at left side from column to lookup against then vlookup not works. We have to copy paste that column at right side then we can use it.
But in Index Match it dosen't matter which column is on which side. It works perfectly without any condition. We don't want to adjust data to use Index Match formula.


4. Array

Vlookup is not consist array range. We have to adjust data by adding extra column and by using other formula to get result.
Index Match can be use with array. It works perfectly when we have multiple criteria.

 Syntax of Index Match with multiple criteria -  

{=INDEX(range1,MATCH(1,(Lookup Value1=Lookup range1)*(Lookup Value2=Lookup range2),0))}

 Ctrl + Shift + Enter to complete Array Formula

Conclusion - 

Though Vlookup is more popular than Index Match formula but the fact is Index Match is better and more powerful lookup formula than vlookup. Thus forget Vlookup and start to use
Index Match. 

Please watch video tutorial to understand in better way

Thank you..!! Enjoy Excelling !!

Have a great day !! 

How to Remove Circular Reference Warning in Excel ?

Circular Reference Warning-

Sometime you get Circular Reference Warning when you open Excel document. Though you can simply press OK and but recommend you not to ignore such warning as there are some calculation errors in your sheet.

We have to remove Circular Reference Warning to get perfect result in Excel file.

Why Circular Reference Warning display??

Circular Reference Warning display when formula cell itself include in formula. Means if you type formula in A1 – A1 + B1 then excel display this warning. You should identify and remove warning because the wrong answer display by formula. Generally you get answer zero if any Circular Reference use in formula. Each time when you open file you’ll get Circular Reference Warning until you resolve them.

How to remove Circular Reference Warning display??

To find the cell where circular reference added go to Formula tab and click on drop down of Error Checking option and choose third option Circular References.

In Circular References option you can find cell address where error occurred. Simply click on that cell reference. Excel automatically goes to that cell.

On above screenshot total of all numbers should not be zero but because of Circular References it shows answer zero.

Once you click on cell address from circular option cursor automatically goes to that cell. Now check formula and remove the Circular References cell from formula. It may be include in Range which use in formula or may be only cell.

Click again on Error Checking button from Formula tab and resolve all Circular Reference cell until option of Circular Reference change to grey or unclickable.

Now save and close the file. The Warning Circular Reference will not show any more.


Watch Video - 

Hope you understand

Thank you. Have a nice day. Enjoy Excelling..

Formula Error in Excel - You should Know

Excel formula help you to do lengthy calculation easily but for that you should know exact formula.
Arguments which use in formula should be correct to get correct answer. If we type wrong formula or argument then we get wrong answer and most probably ERROR.

There are mainly four types are error occur when we type wrong formula -

Lets understand how error comes and what we need to do to resolve them.


1. #VALUE!  ERROR

Value error occurred when formula has wrong type of argument means when you want total
of numbers but text is included in number then excel displays Value error.

Error

Solution

Solution – Simply remove wrong argument from formula.

2. #DIV/0! ERROR

DIV error displays when you divided any cell or number with 0. This is simple math rule. You can not divide any number with zero.

Error

Solution

 Solution - Change zero to another number

3. #REF! ERROR

REF errors displays when excel unable to find cell reference. Generally this error displays when
you deleted some row / column from sheet which used in formula.

Error

Solution

Solution – Remove #REF! from formula and / or add proper reference in formula.

4. #NAME? ERROR

NAME error displays when excel does not recognize text in formula. If you have mentioned
wrong range name or function name is incorrect then NAME error occurred.

Wrong name range -

Error

Solution

Wrong function - 

 

Error

Solution

Solution - Correct formula or remove unrecognized text from formula

Hope you understand. Feel free to comment if you have any doubt.

Watch Video - 

Thank you. Have a nice day. Enjoy Excelling