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
Vladimir Vladimirovich Putin commented
Эй, я согласен с этим.
У меня есть лист, где у меня есть таблица с несколькими столбцами. Мне нужно убедиться, что мой пользователь никогда не изменяет определенный столбец, который содержит формулу. Я хотел бы, чтобы мой пользователь все еще мог добавлять строки в мою таблицу с помощью клавиши TAB и изменять все другие ячейки таблицы, которых нет в упомянутом столбце.
Donald Trump commented
How i wish excel solve this!
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.
Pavan Kumar Nookala commented
Hey, really ist a big bug in Excel. How can this be ignored.
I am an employee for Projekt-Management, I work with these Excel Sheets which involves data entry from different Clients. Either I should not involve any formulas in my unprotected Excel sheet or share the Password every time form data entry.
Please look into this.
The Problem is same for many People but most of them dont know that they can Vote and prioritize their Problem.
Craig Adamson commented
How is this feature STILL not part of Excel?
I find it very irritating that Microsoft does not respond to this thread, despite the recent spike of activity in it.
I've been watching this thread for nearly two years, patiently waiting for it to be implemented. I see it now makes it onto the first page when sorting Excel User Voice Posts via the Top option. Hopefully this means the number of votes required for action is much closer now.
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 feature does not work if the sheet is protected. So, if we have a table in which the user needs to keep adding data to, we usually keep the sheet unprotected. Hope you would solve this issue to keep our formula’s safe in the new users.
I needed this for a long time. What is the status now? Any update?
Jason DAquin commented
A side note - Can we get Excel Online working the same way once this is done? I can't seem to figure out why Excel Online is missing so many features. No wonder Smartsheet is taking over.
I have the same problem. Excel should prioritize this because my users of my template keep asking for a password when they want to add some rows in the data log.
Peter Gratrex commented
I'm honestly surprised this hasn't been rectified by now, it seems like it would make millions of spreadsheets unusable.
Brent E commented
So, Ashvini, when will this be prioritized???
Vote +1 more
Dear Sharma Ji,
Jai Hind !
Kindly priortize it. Everyone is interested, even if all of them are not voting.
... And a standard Ribbon group menu to manage this functionality would be great :
cf printscreen, from a personal addin excel that I developped
Any progress on this?
Pedro Gonzales Romario Fernando Bristol commented
This has been a pain for a long time.. The workaround is with macros, but now with a shift towards the cloud it is even more important because macros don't work with the online version.
It seems to me that this is a tough issue for Microsoft to fix and they currently don't believe there is enough demand for it. But I am confident there is a huge demand for it. It has bugged me for years and years and I only found out today that I could vote on stuff I wanted changed. There must be thousands of people who are unaware of Excel UserVoice.
Wow....by design? Is that the politically correct way to say we totally missed a whole area in programming...we did it by design.
To be able to track sales information and build a dashboard I need to be able to protect cells so users cannot ***** up formulas.
The dashboard feature is nice and the slicers are a great addition but if my formulas are being overwritten by users your nice feature just became unusable. Essentially it is useless.
I concur. And Zack is right, this is a bug created when table structures were added and considerations of other functions (sheet protections) were not adjusted accordingly. For me the issue is that some columns in a table may have formulas, which tables insert as needed when rows are added. If I protect the formula columns to prevent inadvertent change, a user cannot successfully add a row to the table because although a row is inserted, the automatic process of copying the formulas into it fails due to the protection of those columns, breaking the wonderful table feature of ensuring consistent formulas across a series of similar rows. Ludicrous!
As for the notion that Access should go away, or that good tables in Excel competes with Access, I disagree. Access is a wonderful product that allows rapid development of full function applications. It needs to stay around, period. It's better at large datasets and provides more powerful UI and procedure integrations. I wish I could use it for my current needs but corporate policy and technical architectures keep it on the shelf instead of in my toolbelt
I don't see how the Excel feature of "tables" (structure references, filters, auto-expand, etc) working with an Excel feature that predates them : the ability to protect sheets, would harm Access.
Tables working would not make Excel a database management system... and tables not working is not preventing some people from using Excel as if it was a DBMS. (anyway this isn't about Access or even about the MS product marketing strategy, this is about the Excel product responding to it's users needs)
Tables make Excel formulas easier to read, maintain and audit. Reducing the risk of having a "bad" Excel sheet giving bad information and leading to catastrophic decisions.
The Excel team made a choice of adding tables to Excel (great choice) and put efforts in developing the tables and structured reference... but they left it unfinished. It works in it's own bubble but not with the parts of Excel that existed before and are key aspects of Excel like the ability to protect a sheet. Before tables, when you had a "data table" on your sheet you could protect the sheet and select to allow rows to be added (among other things)... the use case was supported...
then they give us all those great tools to develop better tables... you spend time learning and creating something great...
then you protect the sheet before sending it out, you select the option to allow adding rows (since the dataset keeps growing)... and you realize it doesn't work...
you do an internet search thinking you forgot something... and then it dawns on you that you lost all that time because you can't use your brand new shiny table in the real world because it doesn't work!
The use case didn't change, they just stopped midway when implementing their new tables!
(... and then you realize the wonderful structured references won't work in conditional formatting or data validation! and those are probably the formulas where it would be the most useful [I don't know about you, but some of my most complex formulas are in the conditional formatting... where I can't "see" the names of the columns/cells I have to reference]...but that's another thread)
There's no real Excel evolution because we can't move on to fully adopting and using tables (and other "new features"), because we keep having to go back to the old way of formatting "tables of data" (so we can protect them), of defining ranges and writing formulas...
Excel is great, Excel can feel easy to use... but Excel is also great at making it easy to create good looking spreadsheet that give bad information and can cause a lot of damage to an organization. A lot of people using Excel don't understand it enough to be careful and to know all the pitfalls (and there are many), protecting a sheet gives us a chance of having a reliable spreadsheet so it can give reliable information. The Excel team used to understand that : they gave use the ability to protect sheets. But now they seem to have forgotten that basic fact. Sheet protection doesn't work with tables or with data refresh (another thread). It might look good to add "new" features, but if they don't work with what was there before, if they stop supporting the use cases that brought us to Excel, they should be in their own program. If you don't want to make it work with the "old" Excel, then don't try to past it off as Excel...
Ed Hansberry commented
Personally I think ms would like to see Access die. PowerQuery has replaced virtually all of our “read” queries and SharePoint lists, excel tables and even SQL Express are easy to use. 2 years ago ms started giving access to all office pro licenses in 365. They seem to no longer consider it a premium product.