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)

610 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

    96 comments

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

        The idea of using Data Validation to prevent cell overwriting is flawed. If users paste data into the cell the Data Validation is overwritten.

        On a positive note. I have found that if applied to All the cells in a specific Table column the Data Validation, Cell formatting and conditional formatting IS copied down when a new table row is generated. However, as soon as one cell is 'pasted into' then this "cell properties copy down" feature no longer works.

      • Anonymous commented  ·   ·  Flag as inappropriate

        I started using data validation, no macros or VBA. First construct your table like usual along with the formulas you need. Then select the top formulated cell of that row and go to data validation, select the correct "value" (in my case "whole number", even tho it's currency it still works) then enter the same formula you created in that cell in both the minimum and max bar. Be sure to include absolute references where needed. Important; uncheck "ignore blank" before you hit okay. Then press okay.
        Copy this cell down to the end of the worksheet since data validation does not expand with the tables.
        Hope this helps. In my case it works seamlessly and I don't need to protect that sheet since you can't enter any value in formulated cells other than the calculated value.

      • Jim commented  ·   ·  Flag as inappropriate

        Please include the ability to insert a row into a protected table. This function is long over due.

      • Anonymous commented  ·   ·  Flag as inappropriate

        If you go to control.cs in "referencesource.microsoft.com" which is part of the source code for C# and has been made available for all to read by Microsoft, read line 9801 which is part of the comments for the PaintWithErrorHandling function.

        This may give you some insight as to what at least one Microsoft coder thinks' of their user's (our) coding ability and why our requests here may simply be ignored.

        But just in case, I vote yes to this feature (bug fix) also.

        Apologies in advance if I've broken any TOS here.

      • Bryan Sonnier commented  ·   ·  Flag as inappropriate

        Came across this thread and Yes!! so much Yes! We need to be able to lock the formula column of a table and still keep it expandable without having to go through a bunch of VBA trouble.

      • Bert Neyens commented  ·   ·  Flag as inappropriate

        As one of many others I also believe this would be great: Please implement tomorrow... or better today! Or no: YESTERDAY!

        The table-options are great, but when you cannot protect your formulas while having other people using/adding data to the table, you end up constantly fixing (unintended) formula-errors because not everyone is skilled in excel....

      • Simon Hurst commented  ·   ·  Flag as inappropriate

        Would it help speed things up if you were to think of this as correcting a serious bug rather than as an enhancement?

      • Anonymous commented  ·   ·  Flag as inappropriate

        MICROSOFT! WHY HAVENT YOU DEALT WITH THIS? PREVENTING THIS FUNCTIONALITY IS BEYOND A JOKE, GET IT WORKING NOW!!

      • Eric commented  ·   ·  Flag as inappropriate

        You need to get this straightened out. I just ran into this problem. I created a rather simple Project Tracking tool designed to take advantage of the automatic addition of rows allowed by Tables. Since this tool is to be distributed fairly broadly and each project workbook will be maintained in a Sharepoint environment where the Excel inept can access it, I need to protect the formulas and formats and maintain the designed functionality.

      • Deepak commented  ·   ·  Flag as inappropriate

        I do not want to use VBA to achieve this functionality. Better if Microsoft can provide this without the help of VBA.

      • David Brys commented  ·   ·  Flag as inappropriate

        I just tumbled upon this problem... Nice to know I'm not the only one. Count me in for the petition! :-)

      • Anonymous commented  ·   ·  Flag as inappropriate

        It's difficult to understand why such a basic functional requirement hasn't been addressed yet. My application is similar to many previously mentioned here - it's a glaringly obvious omission that would benefit most if not all users. Resolution required urgently please!

      • Anonymous commented  ·   ·  Flag as inappropriate

        absolutely add the ability to "tab" (add) to the next 'new' table row on a protected sheet. it doesn't make sense (inconsistent) for MS to flag unprotected formula cells in a table and not give the ability to tab to a new row without leaving the sheet unprotected.

      • [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!

      ← Previous 1 3 4 5

      Feedback and Knowledge Base