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

If you are looking for ways to protect columns in Excel with a password, then this article will be helpful. By protecting your desired columns, you will make the data in those columns secured from changing by other people. So, let’s dive into the main article.

Download Workbook


4 Ways to Protect Columns with Password in Excel

Here, we have the following dataset containing the records of selling prices, cost prices, and corresponding profits of the products of a company. By using the following 4 methods we will try to protect different columns with a password according to our needs.

protect columns in Excel with password

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.


Method-1: Using Format Cells Option to Protect Columns with Password

Here, we will protect the Selling Price, and Cost Price columns with a password by using the Format Cells and Protect Sheet Options.

protect columns in Excel with password

Steps:
➤ Select the whole sheet by clicking on the triangle symbol in the top left corner and then go to the Home Tab >> Cells Group >> Format Dropdown >> Format Cells Option.

Format cells option

Then, the Format Cells dialog box will appear.
➤ Click on Protection, uncheck the Locked option and then select OK.

Format cells option

Now, we will do that process again for the cells which we want to lock.
➤ Select the columns; Selling Price, and Cost Price, and then go to the Home Tab >> Cells Group >> Format Dropdown >> Format Cells Option.

protect columns in Excel with password

Afterward, the Format Cells dialog box will appear again.
➤ Click on Protection, check the Locked option, and then select OK.

Format cells option

It’s time to protect this sheet with a password to lock our desired columns.
➤ Go to the Home Tab >> Cells Group >> Format Dropdown >> Protect Sheet Option.

Format cells option

After that, the Protect Sheet wizard will pop up.
➤ Type the password and press OK.

protect columns in Excel with password

➤ Retype the password and press OK again.

Format cells option

As a result, our selected columns will be secured now, so to change any of the cell’s values of the Selling Price, and Cost Price columns you will get the following error message.

Format cells option

Read More: How to Protect Columns in Excel without Protecting Sheet (4 Easy Ways)


Method-2: Using Allow Edit Ranges Option to Protect Columns in Excel with Password

In this section, we are going to use the Allow Edit Ranges option along with the Protect Sheet option to keep safe the Selling Price and Cost Price columns from unwanted changes with a password.

protect columns in Excel with password

Steps:
Firstly, we will work with the columns which we can change even after protecting the sheet.
➤ Go to the Review Tab >> Protect Group >> Allow Edit Ranges Option.

Allow Edit Ranges Option

After that, the Allow Users to Edit Ranges dialog box will open up.
➤ Click on the New Option.

protect columns in Excel with password

➤ Name the Title box as two ranges or any other thing you want and type the ranges $B$4:$B$11, $E$4:$E$11 in the Refers to cells box and press OK.

Allow Edit Ranges Option

Afterward, the Allow Users to Edit Ranges dialog box will appear again.
➤ Click on Apply.

Allow Edit Ranges Option

➤ Then, select the Protect Sheet option.

Allow Edit Ranges Option

After that, the Protect Sheet wizard will pop up.
➤ Type the password and press OK.

Allow Edit Ranges Option

➤ Retype the password and press OK again.

protect columns in Excel with password

In this way, we have protected the Selling Price, and Cost Price columns, so to change any of the cell’s values of these columns the following error message will pop up.

Allow Edit Ranges Option


Method-3: Protecting Columns with Formulas

Here, we will protect the column with formulas only, and here we have a formula used in the Profit column to get the profits by subtracting the selling prices from the cost prices.

protect columns in Excel with password

Steps:
➤ Select the whole sheet by clicking on the triangle symbol in the top left corner and then go to the Home Tab >> Cells Group >> Format Dropdown >> Format Cells Option.

Protecting columns with columns

Then, the Format Cells dialog box will appear.
➤ Click on the Protection tab, uncheck the Locked option and then select OK.

Protecting columns with columns

Now, we will do the same process for the column with formulas and select those cells using this process.
➤ Go to the Home Tab >> Editing Group >> Find & Select Dropdown >> Go To Special Option.

protect columns in Excel with password

After that, the Go To Special dialog box will open.
➤ Click on the Formulas Option, and press OK.

Protecting columns with columns

Then, you can see that the cells with formulas have been selected.

Protecting columns with columns

After that, open up the Format Cells dialog box by pressing CTRL+1.
➤ Click on the Protection tab, check the Locked option, and then select OK.

protect columns in Excel with password

It’s time to protect this sheet with a password to lock the Profit column with formulas.
➤ Go to the Home Tab >> Cells Group >> Format Dropdown >> Protect Sheet Option.

Protecting columns with columns

After that, the Protect Sheet wizard will pop up.
➤ Type the password and press OK.

Protecting columns with columns

➤ Retype the password and press OK again.

Protecting columns with columns

In this way, we have secured the Profit column with a password containing formulas so that for trying to change any cell of this column you will get an error message.

protect columns in Excel with password


Method-4: Using VBA Code to Protect Columns in Excel with Password

Here, we will use a VBA code to protect the Selling Price, and Cost Price columns from changing by using a password.

protect columns in Excel with password

Steps:
➤ Go to the Developer Tab >> Visual Basic Option.

VBA Code

Then, the Visual Basic Editor will open up.
➤ Go to the Insert Tab >> Module Option.

VBA Code

After that, a Module will be created.

VBA Code

➤ Write the following code

Sub secure_column()
Dim chng_rng1, chng_rng2 As Range
Set chng_rng1 = ActiveSheet.Range("B4:B11")
Set chng_rng2 = ActiveSheet.Range("E4:E11")
chng_rng1.Locked = False
chng_rng2.Locked = False
ActiveSheet.Protect Password:="1234"
End Sub

Here, we have declared chng_rng1 and chng_rng2 as Range, then assigned chng_rng1 to the range B4:B11, chng_rng2 to the range E4:E11.
After that, we have unlocked these ranges which means you can change these ranges even after protecting the sheet. Then, using a simple password the sheet has been protected and so our desired columns will also be protected with this password.

protect columns in Excel with password

➤ Press F5.
In this way, we have secured the Selling Price and Cost Price columns and so for changing any value of these columns the following error message will appear.

VBA Code

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


Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Practice


Conclusion

In this article, we tried to cover the ways to protect columns in Excel with a password. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo