Over 1,000 votes have now been cast in an attempt to persuade Microsoft to correct a glaring error in the way that Excel Tables work. To put this in context, the next most popular Table suggestion has received just 190 votes, and only 7 of the 241 suggestions in the Tables section have received more than 50 votes.
Some of you might remember our long-running campaign to correct a significant bug in the way that Excel Tables work. Although using Excel Tables has many advantages and can make spreadsheets more automatic and more reliable, when using Tables for data entry, it is impossible to protect the worksheet that they are on. This is because turning on sheet protection prevents data entered into rows or columns adjacent to a Table being include within the Table boundary:
This behaviour is irrespective of whether individual cells are unlocked and also irrespective of whatever specific options are selected in the Protect Sheet dialog.
This inability to turn on sheet protection is relevant when you need to create a Table for users to add data to but you also need to include one or more columns containing a formula. The benefit of adding formula columns to a Table is that the formula is automatically copied down to any new rows, thus automatically ensuring that calculations are applied to all the rows in a Table without any manual intervention.
Principle 20 of the ICAEW 20 Principles for Good Spreadsheet Practice states that:
Worksheet protection is essential to prevent users from changing things that should not be changed, either intentionally or accidentally. Protecting all formulae helps the user to avoid introducing errors, and can also ensure that any formula changes are only made in collaboration with the original author.
Consequently, it is impossible to comply with Principle 20 if you do want to take advantage of the Excel Table ability to incorporate new adjacent rows automatically and also to copy existing formulae down to those new rows.
Regrettably, in spite of more than 1,000 votes and 160 comments, Microsoft's only response was in March 2018 when an Excel Lead Program Manager wrote:
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!
Perhaps a few more votes and more comments might make the difference:
Previous posts have followed the progress of our campaign to correct this issue:
Excel Table protection – progress?
Excel Table Protection bug - latest update
Community members make a difference – Excel Tables request update
Your chance to improve Excel – make Excel Tables even more useful
Excel UserVoice - vote for Tables