Written by Allen Wyatt (last updated April 13, 2019)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
Roger has a worksheet that he needs to distribute to different people so they can add and change some information. He wants to hide some of the columns in the worksheet, however, so that they cannot be viewed by users. He knows how to protect the worksheet and how to hide data in cells but noticed that info is still visible in the formula bar.
Since you already know how to protect a worksheet, you are already on your way to accomplishing your task. These are the steps you should follow:
Figure 1. The Protection tab of the Format Cells dialog box.
Figure 2. The Protect Sheet dialog box.
At this point someone cannot view what is in the hidden column, even if they use F5 to jump to one of the cells in the column; it still won't appear in the formula bar. There is one caveat to all this: If you have some cells in the worksheet (or workbook) that are unlocked, so that the contents of the cell can be changed, it is still possible to see what is in individual cells of the column. How? Two methods, really:
The bottom line is that it is virtually impossible to 100% protect the contents of the column so that they cannot be viewed. Using the protection features of Excel makes it more difficult, but a determined user may be able to still view the contents in the described manner.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8069) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Hiding and Protecting Columns.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Excel allows you to protect your worksheets so they can only be changed as you want to have happen. If you unprotect a ...
Discover MoreWant to stop a user from moving or copying a worksheet? This task (like many) can be more complex than one would hope. ...
Discover MoreYou've protected and saved your worksheet with explicit instructions that you be allowed to insert and delete rows. But ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2021-02-10 13:30:38
Rocio Katsanis
This was very helpful, but it locks all cells in my spreadsheet. I want to complete hide and lock **only** the hidden columns so that, if user changes the width of a column next to it, it will not expand by mistake the hidden one. With this procedure I cannot change the widths of all columns, not just the hidden ones. So what I am looking for is to lock the width of a hidden column, to always stay width=0 but I can modify the widths of all other columns.
2020-07-09 10:35:02
gh
this review is not good. After locking, the whole excel gets locked, not only the specified columns. Doesn't teach you how NOT to lock the whole excel but only the columns.
2020-05-16 05:53:02
Stephen Cobb
This was very helpful Allen. Thank you. As a security professional - who once wrote books about Excel - I was pleased to see that you warn people about the limitations of cell protection. My wife is also a security professional and pointed out that you can access the contents of even a well-protected XLS if you save it as a CSV and open that in a text editor. Stephen Cobb
2020-01-01 18:43:15
Taylor
Still after hidden, locked, and protected when sent to an apple device they can unhide and see everything. They cannot make changes but I need areas hidden completely!
2019-11-13 11:34:21
N/A
Should add more pictures. This was not helpful.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2023 Sharon Parq Associates, Inc.
Comments