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)

741 votes
Sign in
(thinking…)
Password icon
Signed in as (Sign out)

We’ll send you updates on this idea

Zack Barresse shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
Accepting Votes  ·  AdminExcel Team [MSFT] (Admin, Office.com) responded  · 

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

118 comments

Sign in
(thinking…)
Password icon
Signed in as (Sign out)
Submitting...
  • Ayana Andrews-Joseph commented  ·   ·  Flag as inappropriate

    I agree with another comment - VBA may create a workaround, however VBA does not work on all cross-platforms where Excel is used.

    Please address this Microsoft!

  • JMoore commented  ·   ·  Flag as inappropriate

    Long, long overdue. C'mon Microsoft, it's time to address this already.

  • Petr commented  ·   ·  Flag as inappropriate

    1) Table? No, it's name is the List, ListObject. Yes, I know, databases have tables, not Lists, with the same structure, but...
    2) Table's behaviour as Names? Yes. Why we need use INDIRECT in Data / Validation etc.
    3) MS Query is old, yes, but we need use Tables as Excel database source, not only Names
    4) Tables and sheet protection? Stupid, stupid, stupid...
    5) Expanding tables are unreliable. Sometimes Table not expand correctly with cell formatting, alignment, formulas not copied... Reduction of Table? I expect, that "over data" will be deleted.
    6) Table and CSV as direct source? Why not?
    7) Rename Table sometimes causes crash of application.

  • Ricardo Diaz commented  ·   ·  Flag as inappropriate

    As a power user, you can find an alternative in VBA, but this is a common scenario in business.

  • Jason Schaeffer commented  ·   ·  Flag as inappropriate

    I look at Excel tables as the first step towards allowing Excel to store more structured data where the row is more strongly encouraged to have meaning (a lot like a database table). There needs to be a form of protection that allows rows to be added and deleted while preventing calculated columns from being modified, similar to the way updatable Access queries work. Right now, there is no way to make an Excel table function as a designed list of rows -- either the end user can't add and remove rows, or the end user can change the design of the table to circumvent the wishes of the designer.

  • Dick Kusleika commented  ·   ·  Flag as inappropriate

    Here's how I'd set it up: Four check boxes that are specific to tables.

    1. Allow new record (only allows adding a new record to the bottom of the table, default checked)
    2. Allow insert/remove record (can add or delete rows anywhere)
    3. Allow editing non-formula cells (default checked)
    4. Allow editing formula cells

    When you protect a sheet, and don't change any defaults, tables will be locked down except that you can modify existing rows and add a new one at the bottom. Then, if you prefer, you can allow the other two.

    Table permissions would always override non-table permissions. That is, whatever the state of the cells below or to the right of the table, they can get overwritten by adding rows or columns. Also, you can always add/remove header row and total row.

    If you want to do stuff like add or move columns, you have to unprotect the sheet.

  • Joana commented  ·   ·  Flag as inappropriate

    I would like to add that it shlould be also allowed to modify the tables in protected sheets via VBA.

    Now you can't, even if when you protect the sheet with the VBA option UserInterFaceOnly: = True.

    To be able to work with tables, through VBA, we have to unprotect and protect the sheets each time.

    Thanks

  • jamiet commented  ·   ·  Flag as inappropriate

    This is by far my #1 ask for future versions of Excel. I've already commented on https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/11561115-allow-inserting-and-deleting-structured-table-rows (which other people have already posted a link to) but no harm in having my two-penneth worth here.

    I simply can't believe this feature doesn't exist. Its a really glaring omission and I'm really hoping it gets fixed sooner rather than later.

  • Corey Becker commented  ·   ·  Flag as inappropriate

    Great idea. Being able to specify "Locked" by column would be nice. Also, unlocking the structure of the table itself so you can insert rows as you suggested.

  • TSyrstad commented  ·   ·  Flag as inappropriate

    What about protecting formulas in the table? You want users to be able to enter day - but you don't want them messing with the formulas.

  • Roger Govier commented  ·   ·  Flag as inappropriate

    Just used my last vote on this.
    Equally, if there is a Total row in a table, it has to be removed before entering more data else the table will not automatically expand.
    I have made the suggestion before to Microsoft to have a switch allowing Totals to be placed above the header row ( a much more logical place as Totals would always be in view), if required, and then table extension would happen automatically.

  • Roger Govier commented  ·   ·  Flag as inappropriate

    I'm out of votes but agree completely with Doug on this.
    Equally, if there is a Total row in a table, it has to be removed before entering more data else the table will not automatically expand.
    I have made the suggestion before to Microsoft to have a switch allowing Totals to be placed above the header row ( a much more logical place as Totals would always be in view), if require, and then table extension would happen automatically.

  • jamiet commented  ·   ·  Flag as inappropriate

    Stunned that this only had 3 votes before I got here. Surely there are more people that get hit by this?
    VBA simply is not a solution in this day and age which are only supported on Windows desktop. This is a cross-platform world (Mac, iOS, Android, web) and hence VBA is a non-starter as far as I'm concerned.

Feedback and Knowledge Base