Permanent Link to this article: https://indzara.com/faq-items/adding-rows-to-an-excel-table-in-a-locked-protected-sheet/
In some of our templates, we protect (or lock) the sheets using a password. This is done to prevent the user from accidentally editing/removing the formulas used. This is not done to keep the formulas secret. We give the password to the customer so that they can unprotect and edit if they are familiar with Excel and formulas. The free templates have indzara as password.
Auto-Expanding Excel Tables
If you are completely new to Excel tables, please visit our Introduction to Excel tables .
Excel tables have a default feature by which if we enter data in the row immediately following the last row of the table, it will auto-expand and include the new row as part of the table. This is one of the breakthrough features that almost all the templates from indzara.com depend on. There is a catch though. This feature does not work if the sheet is protected.
A request is pending with Microsoft to enable this. Please vote this request up. It has been there for a while. So, it is not clear if/when this will be implemented.
So, if we have a table in which the user needs to keep adding data to, we usually keep the sheet unprotected. We advise the users to be careful about not editing formulas by mistake.
There are some special scenarios where there is a table we expect the users to enter data, but not add more rows usually. In such cases, we protect the sheet. In this blog post, we will go over how you can add more rows to such tables in locked Excel spreadsheets.
We will use our Small Business PTO Manager as an example here, but the concept can be applied to any template or any Excel worksheet.
In the PTO Manager template, we can set the PTO accrual to be based on a tenure table.
For example, employees start with 12 days of annual PTO accrual, and once they complete 24 months of employment (tenure), then they start earning 15 days of PTO every year. We implement this using the tenure table where the user can enter their choice of tenure months, PTO accrual and Max PTO, according to their company’s PTO/Leave policy.
By default, the table has 3 rows and the sheet is locked. In some companies, there could be more than 3 tenure tiers. Let’s see how we can add more rows to this table in the simple 4 step process.
Step 1: Unprotect or unlock the sheet
Navigate to the Review ribbon and click on Unprotect Sheet.
A dialog box pops up where you can enter the password indzara and click OK.
Step 2: Enter data to add to the table
Now that the sheet is unprotected, we can add rows to the table.
To add one row, type the data in the row immediately following the last row of the table. Here, we will type the number in cell B40 (last row of the table is 39) and press Enter key.
After pressing the Enter key, the table will expand to include row 40. You can confirm this by seeing the border line now move to row 40.
To add one more row we can enter data in row 41 and press Enter key. As simple as that.
Step 3: Protect the sheet
Once we have entered as many rows as possible, we can protect the sheet again to prevent accidental modification of formulas.
In the Review ribbon, choose Protect Sheet option.
Enter a password and then click OK. There is no need to change any of the other options.
Now you have protected the sheet again.
Step 4: Save the changes
After protecting the sheet, please save the changes to the file. Otherwise, the changes you made will not be saved.
Press Ctrl+S or use the File Menu.
If there are any questions about this article, please post them in the Comments below.