How to Protect Hidden Columns in Excel (4 Easy Methods)

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.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


4 Easy Methods to Protect Hidden Columns in Excel

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.

Using Format Cells Option to Protect Hidden Columns in Excel

Here, we want to hide columns in the following dataset. To do this we have to follow the following steps

📌 Steps:

  • Firstly, select column D. Then press & hold the Ctrl key and select column G.

Using Format Cells Option to Protect Hidden Columns in Excel

  • Next, right-click on the selected columns, and from the context menu select Hide.

  • As a consequence, you can see that columns D and G are disappeared.

Using Format Cells Option to Protect Hidden Columns in Excel

  • Now, we are going to protect the hidden columns by using the Format Cells option. Let’s walk through the steps to protect hidden columns in Excel.
  • Firstly, select the whole sheet, right-click your mouse, and select Format Cells.

Using Format Cells Option to Protect Hidden Columns in Excel

  • When the Format Cells dialog box appears, select Protection, then check the Locked option. Click on Ok.

  • Next, after placing your cursor on the active sheet name right-click on your mouse and select Protect Sheet.

Using Format Cells Option to Protect Hidden Columns in Excel

  • 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.

Using Format Cells Option to Protect Hidden Columns in Excel

  • Finally, you will be able to protect the hidden columns like the following. Taking 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.

Using Format Cells Option to Protect Hidden Columns in Excel

Read More: How to Protect Columns with Password in Excel (4 Ways)


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:

  • Firstly, select column D. Then press & hold the Ctrl key and select column G.

Applying Go To Special Command to Protect Hidden Columns

  • Next, right-click on the selected columns, and from the context menu select Hide.

  • As a consequence, you can see that columns D and G are 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.
  • Firstly, go to the Home tab, select Editing, and then select Find & Select. Next, select Go To Special.

Applying Go To Special Command to Protect Hidden Columns

  • Then, select the Formulas option and click on OK.

  • Next, after placing your cursor on the active sheet name right-click on your mouse and select Protect Sheet.

Applying Go To Special Command to Protect Hidden Columns

  • When the Protect Sheet dialog box appears, type the password you want to use.
  • Click on OK.

Applying Go To Special Command to Protect Hidden Columns

  • To confirm, enter the password again and click on OK.

  • Finally, you will be able to protect the hidden columns like the following. Taking 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.

Applying Go To Special Command to Protect Hidden Columns

Read More: How to Protect Columns in Excel (6 Easy Methods)


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. To do this we have to follow the following steps.

📌 Steps:

  • Firstly, select column D. Then press & hold the Ctrl key and select column G.

Using Info Option to Protect Hidden Columns

  • Next, right-click on the selected columns, and from the context menu select Hide.

  • As a consequence, you can see that columns D and G are disappeared.

  • Now, we are going to protect the hidden columns by using the Info option. Let’s walk through the steps to protect hidden columns in Excel.
  • Firstly, go to the File tab.

Using Info Option to Protect Hidden Columns

  • Select Info, then select Protect Workbook. Next, select Protect Current Sheet.

Using Info Option to Protect Hidden Columns

  • When the Protect Sheet dialog box appears, type the password you want to use.
  • Click on OK.

Using Info Option to Protect Hidden Columns

  • To confirm, enter the password again and click on OK.

  • Finally, you will be able to protect the hidden columns like the following. Taking 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.

Using Info Option to Protect Hidden Columns


4. Embedding VBA Code to Protect Hidden Columns

At first, we want to hide columns in the following dataset. To do this we have to follow the following steps

📌 Steps:

  • Firstly, select column D. Then press & hold the Ctrl key and select column G.

Embedding VBA Code to Protect Hidden Columns

  • Next, right-click on the selected columns, and from the context menu select Hide.

  • As a consequence, you can see that columns D and G are disappeared.

Embedding VBA Code to Protect Hidden Columns

  • Now, it’s time to use the VBA code. By using a simple code, you will be able to protect hidden columns in Excel. You have to follow the following steps.
  • Firstly, press Alt+F11′ to open the VBA editor. Select Insert > Module.

Embedding VBA Code to Protect Hidden Columns

  • Next, you have to type the following code
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
  • Afterward, close the Visual Basic window, and press Alt+F8′.
  • When the Macro dialogue box opens, select Protect_Hidden_Columns in the Macro name. Click on Run.

  • Then, you need to type a password that you want to use to protect the hidden columns.

Embedding VBA Code to Protect Hidden Columns

  • Finally, you will be able to protect the hidden columns like the following. Taking a look at the following image, you can see that if we want to unhide hidden columns, we cannot, since the method above protects

Embedding VBA Code to Protect Hidden Columns

Read More: How to Protect Specific Columns Using VBA in Excel


Conclusion

That’s the end of today’s session. I strongly believe that from now you may 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 Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!


Further Reading

Saquib
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo