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)

542 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    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

    82 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • [Deleted User] commented  ·   ·  Flag as inappropriate

        The idea that 'voting up' a suggestion elevates its priority is logically flawed. The priority should be based upon, amongst other things, what competing products can do, how the missing functionality impacts on consultants (like me) and hence on my clients, the risk to data quality and confidence, the level of frustration that users are likely to reach, the ease with which such functionality can be added, the surprise that such functionality is not available and the degree to which uninformed users cannot 'imagine' such functionality. A few random users like us (no disrespect) is not what I'd call a reliable basis for deciding priority. The fact that table rows cannot be inserted in a protected sheet means that Excel tables do not fulfill the promise of 'sets' that I assume they're purporting to represent.

      • Bill's friend commented  ·   ·  Flag as inappropriate

        Needs to be implemented if you would like Excel to remain competitive against real database tools (eg MySQL). The current implementation breaks the semantics of tables once worksheets are protected - I am pretty sure that was not what was intended. Why not sound out the dev team internally and hear what they think?

      • Anonymous commented  ·   ·  Flag as inappropriate

        Definitely needed, especially with an increasing reliance of co-authored and online spreadsheets.

        I can only echo what 'Anonymous' says:
        This is necessary. I admin a table where multiple other users add new rows to the table. many of the columns data are auto populated with formulas and i have to be constantly fixing formulas because some end users are not exactly excel proficient and either overwrite of delete formulas that I can't protect properly. I've used vba workarounds, but thats more of a hassle. please implement this feature!

      • Anonymous commented  ·   ·  Flag as inappropriate

        This is necessary. I admin a table where multiple other users add new rows to the table. many of the columns data are auto populated with formulas and i have to be constantly fixing formulas because some end users are not exactly excel proficient and either overwrite of delete formulas that I can't protect properly. I've used vba workarounds, but thats more of a hassle. please implement this feature!

      • Anonymous commented  ·   ·  Flag as inappropriate

        My solution has always been to protect the workbook and add a button “Add two lines to table”. It runs a vba script that unlocks the sheet, adds two blank lines to the table and then relocks the sheet. Not ideal, but it gets the job done while keeping the mad clickers from messing up the sheet.

      • Alexis Taylor commented  ·   ·  Flag as inappropriate

        I work in a regulated industry where it's important to be able to demonstrate that once set up and validated to work a certain way, a spreadsheet isn't going to be accidentally or maliciously changed to alter that behaviour. At the same time, most of our spreadsheets are only of any use if they can be continually expanded to add new list entries at the bottom. So what's needed is the ability to 'lock' a table while allowing it to add new lines which will duplicate the formulae, formatting (including conditional formatting) and data validation rules of previous rows.

        Medical devices, pharmaceuticals and other regulated industries are huge, affluent and subject to these and similar rules. I can't be the first person to have these two simultaneous - and, within the way Excel currently works, conflicting - requirements. How does everybody else get round this contradiction? Simply by not using Excel?

      • jeffrey Weir commented  ·   ·  Flag as inappropriate

        How much more interest do you need to prioritize this, Ashvini? There's plenty of votes, other functionality depends on Tables, and users need to be protected from inadvertently breaking mission-critical spreadsheets.

      • Simon Hurst commented  ·   ·  Flag as inappropriate

        Well said Malcolm. Given the obvious importance that the Excel team are attaching to the Power Query/Get & Transform developments, it does seem strange that they are ignoring an area that is so vital to the whole approach.

      • Malcolm commented  ·   ·  Flag as inappropriate

        Getting even more important now that Bi and get & transform are becoming an everyday tool. We critically need to be able to protect the cells around these tables whilst allowing rows and columns (don't forget pivot tables) to be added and deleted.

      • ines ferrer commented  ·   ·  Flag as inappropriate

        " For example--how would the feature handle cells with content that were placed below a table, but not part of said table". The same way as it handles them now on row insert: it moves them down if it can, or otherwise displays an error.

      • Anonymous commented  ·   ·  Flag as inappropriate

        It is my assumption that the Excel team follows some form of Agile development process. This request we have made is almost certainly in the product backlog, but has been triaged according to both demand and ease of implementation. Implementation for this feature is likely *not* easy. For example--how would the feature handle cells with content that were placed below a table, but not part of said table?

        I for one am very eager for this feature to come out, but I also expect that its implementation may impact the high-level architecture of Excel in ways we don't understand.

      • Oz du Soleil commented  ·   ·  Flag as inappropriate

        The number of votes isn't the main criteria. I posted the 3rd most popular item (smooth scrolling) and there's no action on it. But there's been action on things that have less than 200 votes. I don't get it.

      • DMT commented  ·   ·  Flag as inappropriate

        Hi Simon - no matter how good are the arguments, I am quite sure the mian things which counts at the end is the number of votes. So if having 465 votes as it is right now is not enough to place this issues among the top ones, then it will not be prioritized. Thus get your own colleagues and other people you know to vote for this and push idea activelyin order to raise the total number of voters.

      • Simon Hurst commented  ·   ·  Flag as inappropriate

        Hi Ashwini - is there anything else we can do to convice you of the importance and urgency of this requirement?

      • Brad Brown commented  ·   ·  Flag as inappropriate

        I agree exactly with this previously made comment:

        "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."

      ← Previous 1 3 4 5

      Feedback and Knowledge Base