Feedback by UserVoice

How can we improve Excel for Windows (Desktop Application)?

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)

843 votes
Sign in
(thinking…)
Sign in with: Facebook Google
Signed in as (Sign out)

We’ll send you updates on this idea

Zack Barresse shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

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

139 comments

Sign in
(thinking…)
Sign in with: Facebook Google
Signed in as (Sign out)
Submitting...
  • Anonymous commented  ·   ·  Flag as inappropriate

    Give up folks! After 752 votes from 2016 and only one response from Microsoft 18 months ago it isn't going to happen. Find the best workaround that you can. Horton hears a Who... Microsoft hears nothing.
    In comparison, I employ the services of a software company in St Petersburg that respond to every email, rectify bugs and actually listen and act to customer requests immediately. Different world... the real world. Sad that Microsoft can't use the same philosophy, no matter how big and how much control they perceive to have.

  • Anonymous commented  ·   ·  Flag as inappropriate

    hey, OMISSION, that's a big word, how about calling it an OM, nice and short. Meaning code that should have been written to do something that everyone thinks should have been done by now that hasn't been written yet. Hey MS, sort this OM, now.

  • Anonymous commented  ·   ·  Flag as inappropriate

    Also, I think MS are balancing this request against the fact that "Professional" developers writing apps and forms etc will be using a database product or programming language that can do all this. So programmers writing code for mere Excel mortals to be able to do the same with XL are eating their own lunch and MS if they make XL able to do this are eating into their SQL server market. They are a business. That said, I think they should eat it and do this. Oh, and another thing, this forum doesn't seem to work for me in Edge.

  • Anonymous commented  ·   ·  Flag as inappropriate

    I have voted for this and I can't wait for them to do it. However, its bugging me that people are referring to this as a bug. It isn't a bug. A bug is an error or usually unfortunate unforeseen consequence of code. This is an OMISSION. They simply haven't written the code to make tables play nicely with worksheet protection. I agree they should get on and do it, but, it isn't a BUG.

  • Anonymous commented  ·   ·  Flag as inappropriate

    This really is a bug and a major one, no voting should be necessary.

    This needs to be fixed!

    Protecting the structure of a sheet from end users with limited knowledge of Excel is a basic need because the people constructing the sheets and analyzing the data are often not the same people that enter the data day to day. A protected sheet helps both with data integrity and with facilitating data entry.

    Tables and structured reference are powerful tools but they just aren't an option when they stop working once you protect a sheet!

    (I was so frustrated when I discovered that bug after weeks perfecting a workbook for distribution to data entry people! I couldn't believe it! I finally had to revert to "classic ranges"... what a nightmare that was!)

  • WebSolProv commented  ·   ·  Flag as inappropriate

    @anonymous below, what a ridiculous comment. Quite simply there isnt another offering that could take its place, and if you are talking about that piece of junk that Google have put out, then your comments are not worthy.

  • Anonymous commented  ·   ·  Flag as inappropriate

    Microsoft.... please listen, respond and react. It will be your downfall if you don't. Many others have and are waiting to take your place. 3 years without a response or resolution to this issue is totally unacceptable. Resting on laurels and assuming blind adherence to your products is no longer a viable business model - you will only encourage your customers to seek alternative solutions. Wake up quickly, or die painfully.

  • Anonymous commented  ·   ·  Flag as inappropriate

    This is a very important feature in Tables that is not working. Please correct this as soon as you can for us, so that we can use the Tables feature to its fullest capacity!

  • Anonymous commented  ·   ·  Flag as inappropriate

    As Cloud services become more and more popular, this bug (yes, I consider this to be a bug) should be fixed in the very near future. We should force customers into using OneDrive, SharePoint and Teams, and such a meanwhile core feature is still not available? Tables are used as a reliable data source by Pivot, PowerBI, … and are part of a LOT of solutions; this limitation makes the experience quite poor.

  • Anonymous commented  ·   ·  Flag as inappropriate

    Hi,

    I also agree on the viewers comments, till date MS should have added this feature at file protection tab check box option, as "Allow structured table =ST Expansion", Instead searching vba work around, the feature should also work with external file/sheet linked to structured columns formula, other tabs can be, "Allow Insert row/column at ST", "Expanding ST formulas from left to right"

    Hopefully this will be considered on asap basis, as a normal user this would be a great functionality

    Thank you in advance

    Regards

  • Doug Glancy commented  ·   ·  Flag as inappropriate

    I posted on this back in January 2016 (my post is now folded into the comments on this one). Today, with hope in my heart I tried inserting a row in a table on a protected sheet and see that it's still not possible. Are there any plans to make this happen?

  • Anonymous commented  ·   ·  Flag as inappropriate

    I totally agree and would need this function. Dear Microsoft please consider that macro based files sometime have issues to get distributed via mail or the macro is switched off by admin and VBA code would not work for the desired user. I think this is really important to resolve!

  • Michael Bohannon commented  ·   ·  Flag as inappropriate

    I too have the same issues and described in great detail in many of the previous comments. Please fix this bug and make the tables have the ability to expand and keep formatting when expanded - while protected.

  • Anonymous commented  ·   ·  Flag as inappropriate

    Dear MS Excel,

    PLEASE make this suggestion a reality. I work with tables that contain numerous formulas and functions that I do not want to inadvertently corrupt because I fail to pay attention while entering data in other cells. Locking down the cells containing formulas in my tables while maintain the functionality of automatically resizing the table by adding new rows for additional data would be a HUGE time saver and ensure data integrity

  • Anonymous commented  ·   ·  Flag as inappropriate

    Data validation does not prevent wiping out everything in a cell simply by pressing the delete key or copying and pasting from another cell, even if Ignore Blanks is unchecked.

    No warnings, it just happens!!!

    Yet another "BUG" mess that needs to be fixed.

    Only a protected worksheet or VBA code can prevent this and most companies don't want macro enabled worksheets. Not out of fear brought on by the malicious code warnings or because they think the code you've written may be doing bad things, but just the administrative nightmare for the IT department to manage which ones there going to allow to be run.

    If you're lucky and the error checking rules haven't been turned off out of frustration you may get a little green triangle that's designed to confuse the average user, i.e. department head, when they hover over it and up pops a vague message designed for programmers. They're not going to think that it's something they've done wrong, nor should they. They're going to call you to come and fix it and when you explain you can't, it's part of how excel works, they won't believe you and you end up turning of the little green triangles and now you're caught in an infinite loop with the understanding of what it must feel like to be a computer with a dodgy programmer.

  • Anonymous commented  ·   ·  Flag as inappropriate

    Data validation is a usable stop gap if you have a limited distribution but I don't think its a substitute for what's being asked for here - which is, the ability to add a row to a table on a protected worksheet.

Feedback and Knowledge Base