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)

843 votes
Sign in
(thinking…)
Sign in with: Facebook Google
Signed in as (Sign out)

We’ll send you updates on this idea

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

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

139 comments

Sign in
(thinking…)
Sign in with: Facebook Google
Signed in as (Sign out)
Submitting...
  • Anonymous commented  ·   ·  Flag as inappropriate

    I don't see how the Excel feature of "tables" (structure references, filters, auto-expand, etc) working with an Excel feature that predates them : the ability to protect sheets, would harm Access.

    Tables working would not make Excel a database management system... and tables not working is not preventing some people from using Excel as if it was a DBMS. (anyway this isn't about Access or even about the MS product marketing strategy, this is about the Excel product responding to it's users needs)

    Tables make Excel formulas easier to read, maintain and audit. Reducing the risk of having a "bad" Excel sheet giving bad information and leading to catastrophic decisions.

    The Excel team made a choice of adding tables to Excel (great choice) and put efforts in developing the tables and structured reference... but they left it unfinished. It works in it's own bubble but not with the parts of Excel that existed before and are key aspects of Excel like the ability to protect a sheet. Before tables, when you had a "data table" on your sheet you could protect the sheet and select to allow rows to be added (among other things)... the use case was supported...
    then they give us all those great tools to develop better tables... you spend time learning and creating something great...
    then you protect the sheet before sending it out, you select the option to allow adding rows (since the dataset keeps growing)... and you realize it doesn't work...
    you do an internet search thinking you forgot something... and then it dawns on you that you lost all that time because you can't use your brand new shiny table in the real world because it doesn't work!
    The use case didn't change, they just stopped midway when implementing their new tables!
    (... and then you realize the wonderful structured references won't work in conditional formatting or data validation! and those are probably the formulas where it would be the most useful [I don't know about you, but some of my most complex formulas are in the conditional formatting... where I can't "see" the names of the columns/cells I have to reference]...but that's another thread)

    There's no real Excel evolution because we can't move on to fully adopting and using tables (and other "new features"), because we keep having to go back to the old way of formatting "tables of data" (so we can protect them), of defining ranges and writing formulas...

    Excel is great, Excel can feel easy to use... but Excel is also great at making it easy to create good looking spreadsheet that give bad information and can cause a lot of damage to an organization. A lot of people using Excel don't understand it enough to be careful and to know all the pitfalls (and there are many), protecting a sheet gives us a chance of having a reliable spreadsheet so it can give reliable information. The Excel team used to understand that : they gave use the ability to protect sheets. But now they seem to have forgotten that basic fact. Sheet protection doesn't work with tables or with data refresh (another thread). It might look good to add "new" features, but if they don't work with what was there before, if they stop supporting the use cases that brought us to Excel, they should be in their own program. If you don't want to make it work with the "old" Excel, then don't try to past it off as Excel...

  • Ed Hansberry commented  ·   ·  Flag as inappropriate

    Personally I think ms would like to see Access die. PowerQuery has replaced virtually all of our “read” queries and SharePoint lists, excel tables and even SQL Express are easy to use. 2 years ago ms started giving access to all office pro licenses in 365. They seem to no longer consider it a premium product.

  • Anonymous commented  ·   ·  Flag as inappropriate

    There might be some concern that this would trespass on the domain of Access, and make Excel able to work even more like a database. This could reduce the demand for Access or the SQL Exchange stuff. I pay for Office. If Excel and Access were one programme, I'd pay more. Just saying.

  • Oz Oscroft commented  ·   ·  Flag as inappropriate

    As a business, we rely on templates which users cannot change (using sheet protection). We also use tables to make our spreadsheets far more efficient. Adding this functionality is vital so please could you provide an update.

  • James Denman commented  ·   ·  Flag as inappropriate

    This has become significantly more acute since the introduction of Power Query. PQ is so powerful, but when you want to use a PQ result (in a table, of course) in a sheet which needs to be published to users and protected from them messing around with it, then... you're screwed. It's not as if the need to protect worksheets is going to go away, nor the usefulness of PQ; and there's already a protection option to let users use PivotTables.
    Time for MS to fix this.

  • Ron commented  ·   ·  Flag as inappropriate

    Re: Simon Hurst commented · May 16, 2019 11:50 PM ·
    <snip>
    Presumably there is no progress on this because the Access team have forbidden the Excel team from dealing with this very significant flaw in case it means that Excel can be used for basic database stuff rather than using a proper database.
    </snip>
    I don't think that is a problem. PowerPivot and DataManager give Excel lots of DB functionality. And take a look at the FREE download PowerBI. It too does a lot of DB "functions".

  • Alexis Taylor commented  ·   ·  Flag as inappropriate

    "Does it also go without saying that you should be able to lock cells in a column of a table in such a way that extending the column extends the cells which are locked?"

    - Yes please.

  • Anonymous commented  ·   ·  Flag as inappropriate

    Does it also go without saying that you should be able to lock cells in a column of a table in such a way that extending the column extends the cells which are locked?

  • Malcolm commented  ·   ·  Flag as inappropriate

    Given that Power Pivot is becoming an ever more promoted part of Excel and Power Pivots and queries often depend in user input tables, it is absolutely mind-boggling surprising that this gross oversight has not been fixed.

  • Simon Hurst commented  ·   ·  Flag as inappropriate

    Well anonymous, I might have done, had not 126 previous comments and 800 odd votes demonstrated the futility of that approach...

  • Anonymous commented  ·   ·  Flag as inappropriate

    Why dont you keep your souped-up speculation to yourself and concentrate your energies on promoting this User Voice vote instead? What a ridiculous notion you concoct. There are already much better database functionality in the Excel power query functionality.

  • Simon Hurst commented  ·   ·  Flag as inappropriate

    Presumably there is no progress on this because the Access team have forbidden the Excel team from dealing with this very significant flaw in case it means that Excel can be used for basic database stuff rather than using a proper database.

  • Anonymous commented  ·   ·  Flag as inappropriate

    It's likely that the reason you're locking the worksheet is that you're trying to protect formulas from accidentally being overwritten during data entry.

    I've a number of applications wherein I use tables to collect data in columns and perform calculations on that data in other other columns. Need a way to add a new row to the table (for new data) replicate the formulas, allow a user to enter data while not allowing them to overwrite the replicated formulas on that new line.

  • Yatin Purohit commented  ·   ·  Flag as inappropriate

    Sometimes it is not just a count of votes. Some features are required to build complex solutions. These may not be "popular" requirements fetching more votes. This is one of them. Not having this feature hampers building Excel forms (with SharePoint) in enterprise scenarios.

  • Anonymous commented  ·   ·  Flag as inappropriate

    Tables are great but are not growing when their sheet is protected.
    Conditional formatting is great but get messed-up directly by copy-paste unless the sheet is protected.
    Conclusion: Tables must be allowed to grow in protected sheet to get both great functions of excel working together.
    Please fix this, it’s a real must!

  • Anonymous commented  ·   ·  Flag as inappropriate

    The post below did not work for me. In general, this is a GROSS OVERSIGHT not to consider Format as Table... feature in Protected sheets. Very common business requirement when creating any kind of form is to lock cells and formatting while allowing users to populate table.

  • Anonymous commented  ·   ·  Flag as inappropriate

    Hi all,

    A work around which I found was to make the table an 'editable range' in the review tab and making all of the columns in the table unlocked, other than the columns involving formulas.

    Then, when protecting the sheet, ensure that 'Select locked cells' is unticked - this will unfortunately mean that the users will not be able to select or see the formulas but will allow you to sort the table.

    Useful related link: https://amazingexcel.com/allow-user-to-edit-a-specific-range-cells-in-protected-sheet/

    (Although there may be a better link/ how to out there!)

    Hope this helps anyone still struggling.

  • jeffrey Weir commented  ·   ·  Flag as inappropriate

    Agree with the comment below...the lack of any response on this thread (and generally across this forum) is VERY disappointing compared to the kind of engagement we see from PMs over at the PowerBI Ideas forum. Charles Sterling is rightly proud of the level of engagement his team provides over at PowerBI Ideas. The silence here is just deafening by comparison.

← Previous 1 3 4 5 6 7

Feedback and Knowledge Base