Protected ranges not adjusted when you edit in previous versions of Excel

In Microsoft Excel, you open a protected workbook that uses the "Allow Users to Edit Ranges" feature. When you do this, the workbook may not protect cells as expected.

This problem may occur if the following conditions are true:

• You create a protected workbook in Excel.
• You use the "Allow Users to Edit Ranges" feature.
• You then edit and save the workbook in a version of Excel that is earlier than Excel 2002.

This problem occurs because previous versions of Excel do not have the "Allow Users to Edit Ranges" feature. Therefore, later versions of Excel do not recognize changes that are made to those ranges in earlier versions of Excel.

You can avoid this problem by protecting the worksheet with a password so that it cannot be easily changed in a previous version of Excel.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

How to use the "Allow Users to Edit Ranges" feature

To use the "Allow Users to Edit Ranges" feature, use one of the following procedures, as appropriate for the version of Excel that you are running:

• Excel 2002 and Excel 2003:
  
  On the Tools menu, point to Protection, and then click Allow Users to Edit Ranges.
• Excel 2007:
  
  On the Review tab, click Allow Users to Edit Ranges.

Steps to reproduce the problem

Microsoft Excel 2002 or Microsoft Office Excel 2003

1. Start Excel.
2. Select cells E2:E10.
3. Type Text in cell E2, and then press CTRL+ENTER.
4. Select cells B2:D10.
5. On the Format menu, click Cells to open the Format Cells dialog box.
6. On the Patterns tab, click Green, and then click OK.
7. On the Tools menu, point to Protection, and then click Allow Users to Edit Ranges.
8. In the Allow Users to Edit Ranges dialog box, click New.
9. Make sure the Refers to Cells box contains the following text:
   =$B$2:$D$10
10. Click OK.
11. In the Allow Users to Edit Ranges dialog box, click Protect Sheet.
12. In the Protect Sheet dialog box, click OK.
13. Name the document "Green.xls," and then save it.
14. Start any previous version of Excel.
15. Open the document that you created in step 12.
16. On the Tools menu, point to Protection, and then click Unprotect Sheet.
17. Right-click column D, and then click Delete.
18. On the Tools menu, point to Protection, and then click Protect Sheet.
19. On the File menu, click Save.
20. In Excel, open Green.xls.
21. In cell D4, type New.

Microsoft Office Excel 2007

1. Start Excel.
2. Select cells E2:E10.
3. Type Text in cell E2, and then press CTRL+ENTER.
4. Select cells B2:D10.
5. On the Home tab, click Format in the Cells group, and then click Format Cells.
6. On the Fill tab, click Green, and then click OK.
7. On the Review tab, click Allow Users to Edit Ranges.
8. In the Allow Users to Edit Ranges dialog box, click New.
9. Make sure that the Refers to Cells box contains the following text:
   =$B$2:$D$10
10. Click OK.
11. In the Allow Users to Edit Ranges dialog box, click Protect Sheet.
12. In the Protect Sheet dialog box, click OK.
13. Name the document "Green.xls," and then save it.
14. Start any previous version of Excel.
15. Open the document that you created in step 12.
16. On the Tools menu, point to Protection, and then click Unprotect Sheet.
17. Right-click column D, and then click Delete.
18. On the Tools menu, point to Protection, and then click Protect Sheet.
19. On the File menu, click Save.
20. In Excel, open Green.xls.
21. In cell D4, type New.

One of the columns in the range that uses the "Allow Users to Edit Ranges" feature was deleted in the earlier version of Excel. However, instead of adjusting the range to one less column, the range stays the same in the later version of Excel.