If you are looking for some special tricks to protect hidden columns in Excel, you’ve come to the right place. In Microsoft Excel, there are numerous ways to protect hidden columns in Excel. This article will discuss four methods to protect hidden columns in Excel. Let’s follow the complete guide to learn all of this.
How to Protect Hidden Columns in Excel: 4 Easy Methods
In the following section, we will use four effective and tricky methods to protect hidden columns in Excel. This section provides extensive details on four methods. You should learn and apply all of these to improve your thinking capability and Excel knowledge.
1. Using Format Cells Command to Protect Hidden Columns
Here, we will demonstrate how to protect hidden columns in Excel. Let us first introduce you to our Excel dataset so that you are able to understand what we are trying to accomplish with this article. We have a dataset showing students’ obtained scores in different courses.
Here, we want to hide columns in the following dataset. To do this we have to follow the following steps
📌 Steps:
- First, select column D.
- Then press & hold the Ctrl key and select column G.
- Right-click on the selected columns, and from the context menu select Hide.
- You can see that columns D and G have disappeared.
- Now, we are going to protect the hidden columns by using the Format Cells option.
- Select the whole sheet, right-click your mouse, and select Format Cells.
- When the Format Cells dialog box appears, select Protection, then check the Locked option. Click on OK.
- After placing your cursor on the active sheet name right-click on your mouse and select Protect Sheet.
- When the Protect Sheet dialog box appears, type the password you want to use.
- Click on OK.
- To confirm, enter the password again and click on OK.
- Finally, you will be able to protect the hidden columns like the following. Take a look at the following image. You can see that if we want to unhide hidden columns, we cannot, since the method above protects them.
2. Go To Special Command to Protect Hidden Columns in Excel
Here, we will use another quickest and most effective method to protect the hidden columns by using the Go To Special command.
At first, we want to hide columns in the following dataset. To do this we have to follow the following steps
📌 Steps:
- Select column D.
- Then press & hold the Ctrl key and select column G.
- Right-click on the selected columns, and from the context menu select Hide.
- You can see that columns D and G have disappeared.
- Now, we are going to protect the hidden columns by using the Go To Special option. Let’s walk through the steps to protect hidden columns in Excel.
- Go to the Home tab, select Editing, and then select Find & Select.
- Next, select Go To Special.
- Select the Formulas option and click on OK.
- After placing your cursor on the active sheet name right-click on your mouse and select Protect Sheet.
- When the Protect Sheet dialog box appears, type the password you want to use.
- Click on OK.
- To confirm, enter the password again and click on OK.
- Finally, you will be able to protect the hidden columns like the following.
3. Using Info Option to Protect Hidden Columns
Now, we will use another method to protect the hidden columns by using the Info option.
At first, we want to hide columns in the following dataset.
📌 Steps:
- Select column D.
- Then press & hold the Ctrl key and select column G.
- Next, right-click on the selected columns, and from the context menu select Hide.
- As a result, you can see that columns D and G have disappeared.
- Now, we are going to protect the hidden columns by using the Info option.
- Go to the File tab.
- Select Info, then select Protect Workbook.
- Next, select Protect Current Sheet.
- When the Protect Sheet dialog box appears, type the password you want to use.
- Click on OK.
- To confirm, enter the password again and click on OK.
- The hidden columns are now protected.
Read More: How to Protect Columns with Password in Excel
4. Embedding VBA Code to Protect Hidden Columns
At first, we want to hide columns in the following dataset.
📌 Steps:
- Select column D.
- Press & hold the Ctrl key and select column G.
- Right-click on the selected columns, and from the context menu select Hide.
- The selected columns disappeared.
- Now, it’s time to use the VBA code. By using a simple code, you will be able to protect hidden columns in Excel.
- Press Alt+F11Â to open the VBA editor. Select Insert > Module.
- Type the following code in the module.
Sub Protect_Hidden_Columns()
Dim HPassword As String
Range("B5:H13").Select
Selection.Locked = True
HPassword = InputBox("Type Password to Protect Hidden Columns")
ActiveSheet.Protect Password:=HPassword
End Sub
- Close the Visual Basic window, and press Alt+F8.
- When the Macro dialogue box opens, select Protect_Hidden_Columns in the Macro name and click on Run.
- Then, you need to type a password that you want to use to protect the hidden columns.
- Finally, the hidden columns are protected.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
That’s the end of today’s session. I strongly believe that from now you can protect hidden columns in Excel. If you have any queries or recommendations, please share them in the comments section below.
Don’t forget to check our website for various Excel-related problems and solutions. Keep learning new methods and keep growing!
Related Article
<< Go Back to Protect Excel Columns | Excel Protect | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!