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

  • Anonymous commented  ·   ·  Flag as inappropriate

    Yes please this needs to be working. It will bring easy of work to groups of people trying to update lists without accessing the whole document.

  • Greg Mulhern commented  ·   ·  Flag as inappropriate

    I've been beating my brains out trying to figure what I could've done wrong. Then I finally narrowed it down to the fact that I can only expand my table (and hidden columns with formulas) if it's unprotected. Well, when it's unprotected and the data entry person finds they messed up, they merely select the row and delete to start over. Guess what? The hidden formulas are also deleted. I am baffled that MS can have such a major mistake pointed out to them (as I can read here it has been for a long time) and they refuse to correct it.
    YES, PLEASE FIX THIS. Lord only knows how many people are also experiencing the problem but don't end up on this page by shear luck to "vote" to fix. C'mon MS, this is the real world.

  • jamiet commented  ·   ·  Flag as inappropriate

    Please do not propose macros as an acceptable solution for this. Macros are an overly complicated solution for this scenario and moreover macros are not supported in most Excel clients.

    Also, buttons in a spreadsheet are aesthetically displeasing (aka fugly).

  • Michael commented  ·   ·  Flag as inappropriate

    You could always create a macro that unprotects the sheet, adds a new row, then protects it again. You could even add a button that activates the macro and label it "Add Row" so it is simple for the user.

Feedback and Knowledge Base