HOWTO: Swap Rows and Columns in Microsoft Excel

Need to swap your Excel rows to columns or vice verse, watch this video produced by my guest author.

Pin It

Upload Failed You are required to sign in to upload your changes to this location

I checked out an Excel file from a SharePoint 2010 document library.  After the Excel file had been checked out, I proceed to edit it with Microsoft Excel 2010.  I saved the Excel file after editing but when I tried to check in the Excel file, it failed!

The error message is Upload Failed You are required to sign in to upload your changes to this location.

Pin It

Unprotect a password protected Excel 2007 or Excel 2010 worksheet

Just wanted to share how easy it is to unprotect or unlock a password protected Excel 2007 or Excel 2010 worksheet.  It can come in handy when you need to edit your password protected worksheet but has forgotten the password.

Remember that Office 2007/2010 is using Open XML format which is a zipped XML format.  To unprotect the Excel file without knowing the password, just follow the steps below.

Rename the file extension from xlsx to zip.

Use a zip tool (e.g. 7-zip, Winzip) to open the file.  Go into the xl folder.

  Then go into the worksheets folder.

Right click on the worksheet you want to unprotect and select edit.

Look for the sheetProtection tag.  Note that the password has been hash so it is not the real password.  To unprotect the worksheet, you can choose to delete the entire tag which will make the worksheet unprotected or you can set an empty password.

Protected with a password

<sheetProtection password="96E1" sheet="1" objects="1" scenarios="1"/>

Protected with empty password

<sheetProtection password="" sheet="1" objects="1" scenarios="1"/>

If you choose to set an empty password, the worksheet is still protected but you can unprotect it in Excel without entering any password.

After making the change, save and close the editor (e.g. notepad).  When prompted whether to update the archive, select “Yes”.

Rename the file extension from zip back to xlsx.  Open the Excel file and you can edit the previously protected worksheet.

Here is a video showing how to do it. Enjoy!

UPDATE: If you find difficulty doing it, you can use the tool in my entry http://deinfotech.blogspot.com/2011/12/simple-tool-to-unprotect-password.html.

Pin It

Microsoft Excel–Search From The Right

If you ever need to search a text in an Excel cell for a specific character starting from the right and cannot find the formula to do it, this might be some help to you.

I found this nice formula on MREXCEL.COM by “IML”.

=RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1,"-","@",LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))))

It is a rather long formula, so let’s break it up and see how it works. Using the following text in cell A2 as an example.

D:\Backup\Documents\Pictures\Bike.jpg

The objective is to extract the remaining of the text after the last “\”. So the desire result is Bike.jpg and the formula will be

=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"\","@",LEN(A2)-LEN(SUBSTITUTE(A2,"\","")))))

This is how the formula works.

First, replace the last “\” with another character. The SUBSTITUTE(A2,"\","@",LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))) portion of the formula does that. The LEN(SUBSTITUTE(A2,"\","")) returns the length of the text without any “\” character which will be 33. The LEN(A2)-LEN(SUBSTITUTE(A2,"\","")) returns the number of “\”characters which is 4. As you can see, 4 is the last instance of the “\” character in the text. Therefore, by using the formula SUBSTITUTE(A2,"\","@",LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))), it replaced the 4th instance of the “\” character with “@'” character.

Next, find the position of the “@” character using FIND function. The FIND("@",SUBSTITUTE(A2,"\","@",LEN(A2)-LEN(SUBSTITUTE(A2,"\","")))) portion of the formula does that and returns 29.

Now use the RIGHT function to extract out the remaining of the text after the “@” character. We need to tell the RIGHT function how many characters we want to extract starting from the right. To get the number of character, take the length of the text and minus away the position of the “@” character. The LEN(A2)-FIND("@",SUBSTITUTE(A2,"\","@",LEN(A2)-LEN(SUBSTITUTE(A2,"\","")))) portion of the formula does that and returns 8. Finally the RIGHT function will return Bike.jpg.

If we evaluate the formula stop just before the RIGHT function, it will be RIGHT(37, 8) where 37 is the length of the text and 8 is the number of characters we want to extract starting from the right.

Pin It