Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Hiding and Protecting Columns.
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.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
If you have a worksheet protected, it may not be immediately evident that it really is protected. This tip explains some ...
Discover MoreWhen you are developing a worksheet for others to use, you might want to protect some of the information in that ...
Discover MoreWhen you protect a worksheet, one of the benefits is that you can limit which cells can be used for data entry. How a ...
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 © 2024 Sharon Parq Associates, Inc.
Comments