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)

253 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)

    We’ll send you updates on this idea

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

    49 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • 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.

      • vicky commented  ·   ·  Flag as inappropriate

        Additionally, this seems like it's been an issue in several versions of Excel

      • vicky commented  ·   ·  Flag as inappropriate

        When protecting sheets, the only way to allow sorting/filtering is to have the cells that you want to sort/filter unlocked which allows users to edit the information in those cells. Ideally, there would be a way to lock the cell formulas while still allowing the cells to be filtered or sorted.

      • Simon Hurst commented  ·   ·  Flag as inappropriate

        Add rows to Table on protected sheet

        A good starting point would be just to allow rows to be added to Tables when a sheet is protected, with the other cell locking functionality working in Table cells in the same way as non-Table cells. As Jon says in his original proposal, the absolute inability to add a row to a Table on a protected sheet makes it impossible to use cell locking and protection with a Table. Fiddling about with how formulae are created as suggested by anonymous is no solution at all as it does nothing to stop someone deleting or overwriting the formula/range name.

        I am sure these things are never as simple as they seem, but an added 'Protect Sheet, Allow all users of this worksheet to:' option to 'Add rows to a Table' would be a substantial step forward.

      • Anonymous commented  ·   ·  Flag as inappropriate

        A solution to aid security of formulas against inadvertent or deliberate editing, is to create the formula as a named range. Benefits are:
        1 when you want to edit a formula, it is done only in NamedRanges
        2 the edit automatically updates or refreshes all instances of its use by default
        3 build complex formulas in stages by nesting another NamedRange in your formula
        4 use a NamedRange to create a parameter for use in more than one formula

        Oz's data entry proposal is in essence use of a Table specifically for data entry (copy/paste can easily be used). All logic is written in a Table specifically reserved for calculation, data is of course pulled in by look ups to data entry Tables. Results can then be presented in a report format created in its own worksheet.

      • Anonymous commented  ·   ·  Flag as inappropriate

        Say if you enter a formula in a cell. The formula refers to a cell that is part of a table. Now you want to copy the formula in adjacent cells by handle-drag. BUT you need to lock the referred cell (either row, or column, or both). Currently you can't do so!

      • Kenneth Barber commented  ·   ·  Flag as inappropriate

        It should be possible to have the formulas of a table column protected as a whole. This way, a user can delete a row containing a formula, but they would not be allowed to modify the formula.

      • Shirley Moreman commented  ·   ·  Flag as inappropriate

        This is definitely something which needs fixing. I use tables in a lot of solutions and always have to resort to VBA workarounds (which even then are not foolproof) to protect the formulas.

      Feedback and Knowledge Base