Get Tables working on protected sheets (add rows, sort, filter, etc.)
I would like to be able to, under the right circumstances, have the ability to add/remove rows of a table which resides on a protected sheet.
Currently if you protect a sheet you can't insert/delete rows of a table. Even if you unprotect all cells and allow inserting/deleting of rows on sheet protection, it doesn't matter and is completely ignored.
What I am proposing would have to follow specific rules:
- Table cells would have to be unprotected
- Cells below table would have to be unprotected (truly optional † )
- Insert Rows would have to be specified in sheet protection
† The way table rows are inserted is different than a standard row insert. If there are blank rows below the table it will "consume" those rows, and not shift things down. If there is data below the table, once it consumes all rows between the two, it will start inserting. This mechanism would make the second item above optional depending on how you [Microsoft] would handle this internally. My preference would be to have the cells unlocked, and honor the protection status of the cell, and not overwrite, or "consume" if there is space available.
This has been BROKEN for some time. Since we can ALREADY do this with standard cells, it should ALSO work for tables. This request has come up multiple times, and has previously been rejected as "BY DESIGN", but I'm here to tell you - and I'm an expert - this is not by design, and is a bug which has been overlooked and should be fixed.
(2016-12-07 Dan B [MS]: updated title to capture the actual ask here, which is to make Tables work better on protected sheets; currently, since protected sheets were built "before" Tables, they don't work well with "Tables" or objects that adjust/expand within the grid as users interact with them)
Zack Barresse
shared this idea
Thanks for logging this great suggestion, Zack, and to others for voting it up. We’ll prioritize this according to the number of votes, so if there’s more interest, please make sure to register your vote!
Thanks
Ashvini Sharma
Lead Program Manager
Excel
174 comments
-
Anonymous
commented
I love this idea.
Let's get this rolling!!
-
Dinesh Natarajan Mohan
commented
This is much needed. I agree with Oz's comments on how this will help user experience.
-
Anonymous
commented
Say if you enter a formula in a cell. The formula refers to a cell that is part of a table. Now you want to copy the formula in adjacent cells by handle-drag. BUT you need to lock the referred cell (either row, or column, or both). Currently you can't do so!
-
Kenneth Barber commented
It should be possible to have the formulas of a table column protected as a whole. This way, a user can delete a row containing a formula, but they would not be allowed to modify the formula.
-
Shirley Moreman
commented
This is definitely something which needs fixing. I use tables in a lot of solutions and always have to resort to VBA workarounds (which even then are not foolproof) to protect the formulas.
-
I've read most of what is on this site. Your post is indeed the first of its kind. I would vote for it if I had votes left. :\
-
Doug Glancy
commented
I'm fairly late to this site, so let me know if this specific request was already made. I searched and found more general things but not this.
-
Doug Glancy
commented
I'd like to be able to lock some cells in a structured table, protect the sheet and still be able to insert rows with the Insert command, add rows by tabbing past the end of the table, and delete rows. This would be very useful in making more foolproof user interfaces.
As it is now, it's risky to use a table for user data entry because if you want them to be able to add rows you have to leave formulas unprotected. (Or use VBA).
-
Oz du Soleil
commented
We need a way to partially protect tables.
Lots of times--especially when developing a tool for someone else--it's necessary to have complex formulas next to empty cells for new entries.
Some of the end users are terrified of the possibility of accidentally typing over a formula that they won't be able to fix.
My solution has been to create a form and use VBA to move new data over to a sheet where the table lives. Writing VBA is a hassle because then I have to consider things like: "what if the user changes the name of a worksheet?"
But that's the solution because a table can't be partially protected.
-
Andreas Thehos
commented
This would be a really helpful change.
We use to protect Sheets, but it would be great, if you could add new rows at the end of a table by using tab. So you would protect the shape (maybe the formulas, too) of a table, but could select:
- edit all rows
- just add new rows
- edit or protect formulasThanks
Andreas -
Gary
commented
This is a great idea. Unfortunately I'm out of votes so I can't support this.
-
The problem goes much deeper than just sorting. Even inserting rows is impossible on a Table with sheet protection turned on. Good idea!
-
Dave S.
commented
When a table is protected, including the cells within it, checking "Allow Sorting" does not allow the table to be sorted without various VBA workarounds.
Please add an option to the user-interface protection checklist that would allow sorting in protected tables.
-
Jon von der Heyden
commented
I love tables! My references are dynamic. Filters are present. Formula's auto-fill down. Presentation is improved. My formulas are more intuitive when being audited.
I would like to be able to support tables properly when sheet protection is enabled. I mean the full set of table out-of-the-box functionality. At the moment protection really does render useless all of the goodness that tables have to offer.