Protect columns in a table while allowing new rows

[v-josephc]

tl;dr - I’m trying to protect certain columns with formulas in them, so nobody will be able to edit them. I also need to allow these formulas to copy down whenever someone adds a new row to the table.

I'm using Microsoft Office on Windows 10. Excel version 2108. I am sort of a noob when it comes to Excel. I share a spreadsheet with several coworkers. They input their data into a table, and certain columns have formulas that will do calculations for them. When they begin to input new data into the row just below the table, the table will expand to include that new row, and the formulas in the columns will automatically fill down.

I don’t know what is happening, but somehow every day someone manages to interrupt this flow. I’ll look into the spreadsheet, and there are 5 or 6 rows at the bottom that are not included in the table. These rows do not have the formulas in the columns as mentioned above.

I cannot hide the columns because my coworkers need to see them. I’ve tried protecting the columns in the sheet, but when I tried this the table wouldn’t include new rows when people added new data.

Any suggestions? Thanks in advance!

[#2 · Accepted solution]

Hi @v-josephc 

you describe a very common issue and unfortunately Excel does still not allow to activate sheet protection while still being able to use the formatted tables functionality (e.g. copy formulas).

The only advise that I have is to add already enough blank rows in your table, so the users would not have to do that by themself. And then activate the sheet protection to lock the formula cells.

It's not ideal and maybe not practical in your situation, but I have no better advise.

[#3]

Thanks for the response Martin!