A question was recently submitted by Barry Shrut, who wanted to know if there is a way he can revise a protected workbook when the password left with a departed employee. This tip may do the trick!
Although you can't make changes to protected cells in a workbook when you don't have the password, you can copy the entire workbook to a new workbook file and the protection will not carry over to the new file.
Select the occupied cells in the current worksheet. If you prefer, you can select the entire worksheet by clicking the "Select All" button which appears as a blank box above the row number 1 and to the left of the column letter A. All cells will be selected, including those that are protected.
Note: If the workbook contains information on more than one sheet, select all utilized sheets by clicking on the first sheet tab, pressing the Shift key, and clicking on the sheet tab for the last sheet containing data.
Execute a Copy command by clicking the Copy button on the standard toolbar, pressing Ctrl+C, or choosing Edit, Copy from the menu. All selected matter will be copied.
Open a new workbook.
Execute a Paste command by clicking the Paste button on the standard toolbar, pressing Ctrl+V, or choosing Edit, Paste from the menu. All selected material from the protected workbook will appear in the new workbook, with formulas intact. No protection will exist in the new workbook, so you will have access to all cells.
If the protected worksheet contained hidden rows or columns, these hidden areas will copy to the new workbook as well, assuming your original selection area overlapped the hidden areas. To view hidden material in the new workbook, select flanking columns or rows (for example, if Column C is hidden, select columns B and D), then choose Format, Column (or Row), Unhide from the menu. The hidden material will be displayed.
View more helpful tips!