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
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).
@John Pequeno - I support this idea, but for your case it seems that you are trying to push Excel for much more complicated use than it has been intened, so I doubt you can Expect excel to work smoothly even with this implemented. I suggest you start looking for inspiration for migrating somewhere else - recently Microsoft had "MS business applications" launch event with lots of good stuff to come soon - try to find and watch it.
Yossi Geretz commented
This is a great idea - MUST HAVE. I am publishing a table. I'd like to solicit feedback by having the audience fill out the table and return to me. Several columns in the table are calculated. I want to lock the audience out of those columns. But as soon as I protect the sheet, the entire table is effectively locked.
As an aside, I would suggest that the protection paradigm in excel has been broken, or at least lacking a major use case for some time now. It should be much easier to lock a column while leaving the ability to add rows in an unfettered manner. This would effectively ensure that each row added would conform to the locked in columnar structure. This is a major use case. Can we get this to work with tables?
Jan-Willem Aikens commented
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.
John Pequeno commented
This feature is greatly needed. Without it I have to leave entire sheets unprotected in complex application / table based docs, which could allow for corruption of the entire excel if a user enters the wrong thing or clears a formula. The primary excel I'm working with has 4 types of users, basic (staff), intermediate (managers), advanced (HR), and admin (programming). Because of this, I have to use a few layers of permissions, both worksheet and workbook. I can almost accomplish my entire needs with excel as is, but this one issue doesn't allow me to properly limit the permissions of HR users which are able to edit some source table data on hidden tabs, but which should not be able to edit some of the formulas and structure of tables. I hope this gets corrected some time soon. As a sidenote, in addition to the recommendations given in comments below, once this is hopefully fixed, you should also allow Tabbing on a protected sheet table, so you can enter in a new record, and tab through the fields in the table record you are entering.
Adrian L. commented
Thanks for highlighting this, I'm no slouch at Excel (although I'd not call myself an expert) and I have been trying to work out how to do this for ages, believing it was a shortfall in my knowledge on protecting cells. I agree it's a bug and also that it needs to be fixed as a priority.
this is a must feature.
This would be fantastic. Please do it!!
Tomaz Tacla commented
I need to SORT a protected sheet. It's no longer available in Office 365. Is there a way around it?
Heather R commented
Yes, I've had to resort to a hybrid VBA/Named Range solution instead of tables :(
jeffrey Weir commented
I've posted a work-around at http://dailydoseofexcel.com/archives/2017/12/10/seven-steps-to-almost-protected-calculated-columns/ that gives you a way to leave the calculated column unprotected while stopping users from directly selecting the cells in calculated columns with the mouse. This works by parking a locked invisible shape over the column. Users can still select the cells by navigating them using the arrow keys, but are then prohibited by overwriting the formulas via a DV condition. They can still delete the formulas however...although I've got a pop-up DV input message warning them against doing so. Convoluted work-around that mostly works, but no substitute for the kind of usability improvement we would get if MS changed Table protection in line with this thread.
Jeff Zhang (MSFT) commented
It's indeed a reasonable scenario. We'll consider and evaluate/prioritize it together with all other requests.
Please make this possible
Agreed ... definitely a true need if Microsoft wants to continue to push the use of tables to emulate database functionality within Excel
Agree. This would be a major help.
I wholeheartedly agree with the need to fix this ASAP. The structure interaction of worksheet and table is clunky in many ways but this is unacceptable.
Please make this possible