How to Hide Columns in Excel with Password (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

The Excel functions to hide and reveal columns are often used, making it simple to conceal and reveal them. However, there are occasions when you need to hide some columns that contain important information that you don’t want others to see. This article will show you how to Hide Columns in Excel with a Password.


Hide Columns in Excel with Password: 2 Handy Approaches

Your worksheet’s columns holding data that you do not need to see or do not want to print can be hidden. These two techniques will show you how to use the Review tab and VBA Code to Hide Columns in Excel with a Password. Let’s suppose we have a sample data set.

Sample Data


1. Utilizing the Review Tab to Hide Columns in Excel with a Password

In this first method, you will learn how to hide columns in Excel with a password utilizing the Review tab.

Step 1:

  • Firstly, click on column D and then click Shift to select column E as well.
  • Secondly, right-click the mouse button.
  • Thirdly, click on the Hide option to hide the D and E columns.

Sample Data

Step 2:

  • Now, you can see the D and E columns are hidden below.

Sample Data

Step 3:

  • First, go to the Review tab.
  • Next, click on the Protect Sheet command.
  • Then, to protect the sheet columns, you can enter a password to unhide and hide the D and E columns.
  • Finally, click OK.

Sample Data

Step 4:

  • Here, you will reenter your password.
  • Then, click OK.

Sample Data

Step 5:

  • When you need to unhide the D and E columns, go to the Review tab again.
  • Now, click on the Unprotect Sheet command.
  • Besides, click OK.

Sample Data

Step 6:

  • In this step, you will keep your cursor here, which is marked by a red rectangle.
  • Now, Double-click on the red-marked position.

 Handy Approaches to Hide Columns in Excel with Password

Step 7:

  • Finally, you will notice that columns D and E are unhidden.

 Handy Approaches to Hide Columns in Excel with Password

Read More: How to Hide Columns in Excel with Minus or Plus Sign


2. Applying VBA Code to Hide Columns in Excel with Password

In this final session, we will create a VBA Code to Hide Columns in Excel with a Password using the Developer tab.

Step 1:

  • At first, we will use the Developer tab.
  • Then, we will select the Visual Basic command.

 Handy Approaches to Hide Columns in Excel with Password

Step 2:

  • Here, the Visual Basic window will open.
  • After that, from the insert option, we will choose the new Module to write a VBA Code.

 Handy Approaches to Hide Columns in Excel with Password

Step 3:

  • Now, paste the following VBA code into the Module.
  • To run the program, click the “Run” button or press F5.
Sub Hide_Columns_withPassword()
'Declaring Variables
    Dim Mypassword
    Dim zAddress As String
    'Selecting columns D and E
        zAddress = "D:E"
        'Selected columns will be hidden
        Application.ActiveSheet.Columns(zAddress).Hidden = True
tryagain:
'Enter the password here
        Mypassword = InputBox("Enter Password", "Password")
    If Mypassword = "Bishawajit" Then
    ' After entering the correct password, columns will be shown
    Application.ActiveSheet.Columns(zAddress).Hidden = False
    MsgBox ("Correct")
    Else
    'If password is incorrect, you can try again by clicking Yes option
If MsgBox("Password incorrect, try again?", Yes Or No, "Wrong password") = Yes Then
    GoTo tryagain
    Application.ActiveSheet.Columns(zAddress).Hidden = True
    End If
End If
End Sub

 Handy Approaches to Hide Columns in Excel with Password

VBA Code Breakdown

  • Firstly, we call our Sub Procedure as Hide_Columns_withPassword().
  • Secondly, we declare our variables as Dim Mypassword and Dim zAddress As String.
  • Then, we select the D and E columns and put them in a variable as zAddress = “D:E”.
  • And, D and E columns will be hidden in our active sheet as ActiveSheet.Columns(zAddress).Hidden = True
  • Now, we declare our password as Mypassword = InputBox(“Enter Password”, “Password”).
  • After running the program, you will enter the correct password to unhide the D and E columns as If Mypassword = “Bishawajit”.

Step 4:

  • After running the program, this window will open and you will see the D and E columns are hidden.
  • Now, you will input your correct password.
  • Then, click OK to unhide the D and E columns.

 Handy Approaches to Hide Columns in Excel with Password

Step 5:

  • Here, you will see the D and E columns unhidden below.
  • And if your input password is correct, this given window will open.
  • Then, click OK.

 Handy Approaches to Hide Columns in Excel with Password

Step 6:

  • If your password is incorrect, this window will open.
  • Here you will see the D and E columns are still hidden.
  • Then, click OK to re-enter your correct password.

 Handy Approaches to Hide Columns in Excel with Password

Read More: How to Hide Selected Columns in Excel


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.


Conclusion

In this article, I’ve covered 2 handy methods to Hide Columns in Excel with Password. I sincerely hope you enjoyed and learned a lot from this article. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.


Related Articles


<< Go Back to Hide Columns | Columns in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Bishawajit Chakraborty
Bishawajit Chakraborty

Bishawajit Chakraborty, a Rajshahi University of Engineering & Technology graduate with a B.Sc. in Mechanical Engineering, has been associated with ExcelDemy since 2022. Presently, he is a content developer, specializing in Excel Power Query, Data Analysis and VBA. It is worth mentioning that he has authored more than 90 articles on VBA content development. His profound interest lies in the fields of data analytics and data science. He possesses expertise in VBA, Power BI, machine learning, and Python... Read Full Bio

2 Comments
  1. HI Bishawajit, this is really great. On the macro solution, is it possible to re-write so that you password protect “grouped” columns instead of hidden columns? Basically, I want it to be required to type in a password when clicking the “+” sign on grouped columns. Thank you much!

    • Reply Bishawajit Chakraborty
      Bishawajit Chakraborty Nov 13, 2022 at 1:07 PM

      Thank you so much, Peter, for your comment.
      You will find your solution in the below steps. Please follow the steps accordingly.

      Steps:
      • Firstly, go to the Data tab.
      • And, select the group option.
      1
      • Now, you will see here the plus (+) sign.
      • Here, we have grouped the 5th, 6th, and 7th rows.
      • Then, go to the Review tab.
      • And, select the Allow Edit Ranges option.
      • After that, choose the ranges.
      • And, type the password and click OK.
      2
      • Retype the password and click OK.
      3
      • Now remove this window.
      4
      • Firstly, go to the Review tab.
      • Secondly, select the Protect Sheet option.
      5
      • Now, write your password.
      • And, click OK.
      7
      • Then, retype your password and click OK.
      6
      • So, if you click on the plus (+) sign, it will show you the given result you want.
      8

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo