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)
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!
Lead Program Manager
This is necessary. I admin a table where multiple other users add new rows to the table. many of the columns data are auto populated with formulas and i have to be constantly fixing formulas because some end users are not exactly excel proficient and either overwrite of delete formulas that I can't protect properly. I've used vba workarounds, but thats more of a hassle. please implement this feature!
jeffrey Weir commented
@Alexis Taylor I published another ugly workaround at http://dailydoseofexcel.com/archives/2017/12/10/seven-steps-to-almost-protected-calculated-columns/
But best bet is to lobby people to vote on this, and lobby bloggers to blog about the glaring hole in functionality.
My solution has always been to protect the workbook and add a button “Add two lines to table”. It runs a vba script that unlocks the sheet, adds two blank lines to the table and then relocks the sheet. Not ideal, but it gets the job done while keeping the mad clickers from messing up the sheet.
Alexis Taylor commented
I work in a regulated industry where it's important to be able to demonstrate that once set up and validated to work a certain way, a spreadsheet isn't going to be accidentally or maliciously changed to alter that behaviour. At the same time, most of our spreadsheets are only of any use if they can be continually expanded to add new list entries at the bottom. So what's needed is the ability to 'lock' a table while allowing it to add new lines which will duplicate the formulae, formatting (including conditional formatting) and data validation rules of previous rows.
Medical devices, pharmaceuticals and other regulated industries are huge, affluent and subject to these and similar rules. I can't be the first person to have these two simultaneous - and, within the way Excel currently works, conflicting - requirements. How does everybody else get round this contradiction? Simply by not using Excel?
jeffrey Weir commented
How much more interest do you need to prioritize this, Ashvini? There's plenty of votes, other functionality depends on Tables, and users need to be protected from inadvertently breaking mission-critical spreadsheets.
Well said Malcolm. Given the obvious importance that the Excel team are attaching to the Power Query/Get & Transform developments, it does seem strange that they are ignoring an area that is so vital to the whole approach.
Getting even more important now that Bi and get & transform are becoming an everyday tool. We critically need to be able to protect the cells around these tables whilst allowing rows and columns (don't forget pivot tables) to be added and deleted.
ines ferrer commented
" For example--how would the feature handle cells with content that were placed below a table, but not part of said table". The same way as it handles them now on row insert: it moves them down if it can, or otherwise displays an error.
It is my assumption that the Excel team follows some form of Agile development process. This request we have made is almost certainly in the product backlog, but has been triaged according to both demand and ease of implementation. Implementation for this feature is likely *not* easy. For example--how would the feature handle cells with content that were placed below a table, but not part of said table?
I for one am very eager for this feature to come out, but I also expect that its implementation may impact the high-level architecture of Excel in ways we don't understand.
Oz du Soleil commented
The number of votes isn't the main criteria. I posted the 3rd most popular item (smooth scrolling) and there's no action on it. But there's been action on things that have less than 200 votes. I don't get it.
Thanks DMT, but I've been doing that for the past 18 months or so...
Hi Simon - no matter how good are the arguments, I am quite sure the mian things which counts at the end is the number of votes. So if having 465 votes as it is right now is not enough to place this issues among the top ones, then it will not be prioritized. Thus get your own colleagues and other people you know to vote for this and push idea activelyin order to raise the total number of voters.
Hi Ashwini - is there anything else we can do to convice you of the importance and urgency of this requirement?
Also expanding the table format would be a great improvement.
Brad Brown commented
I agree exactly with this previously made comment:
"While making an Excel sheet I found out this does not work. Searching the internet brought me here. This is indeed a must have feature."
Yes please this needs to be working. It will bring easy of work to groups of people trying to update lists without accessing the whole document.
Greg Mulhern commented
I've been beating my brains out trying to figure what I could've done wrong. Then I finally narrowed it down to the fact that I can only expand my table (and hidden columns with formulas) if it's unprotected. Well, when it's unprotected and the data entry person finds they messed up, they merely select the row and delete to start over. Guess what? The hidden formulas are also deleted. I am baffled that MS can have such a major mistake pointed out to them (as I can read here it has been for a long time) and they refuse to correct it.
YES, PLEASE FIX THIS. Lord only knows how many people are also experiencing the problem but don't end up on this page by shear luck to "vote" to fix. C'mon MS, this is the real world.
Please do not propose macros as an acceptable solution for this. Macros are an overly complicated solution for this scenario and moreover macros are not supported in most Excel clients.
Also, buttons in a spreadsheet are aesthetically displeasing (aka fugly).
You could always create a macro that unprotects the sheet, adds a new row, then protects it again. You could even add a button that activates the macro and label it "Add Row" so it is simple for the user.
Tables are great and it's so frustrating to be unable to use them (or at least many of their advantages) in protected sheets!
I mostly use protected sheets when casual users will have to do some data entry, the fact tables resize automatically and formatting, CF and formulas in the different columns are "copied" to the new rows would make them a natural tool... except they don't work if I want to prevent users from accidentally messing up the formulas or the column "titles"!
Protecting cells is also a good way to facilitate data entry by preventing the selection of (protected) cells outside the entry "table" so that using "tab" after the last cell of the row entered moves to the beginning of a new row...
I love structured references, they make formulas so much easier to read and maintain, but since I can't use tables in the data entry sheets, it generally means I can't use structured references much.
(Since I can't use macros, I can't use forms to facilitate data entry or in a workaround to add rows to a table)
I agree with others stating this is a very common and important use case: needing to protect a sheet (an workbook) so that casual users can do data entry by adding rows (sometimes deleting) without risking messing up the formulas or the structure and logic (columns).