How to Protect Columns with Password in Excel: 4 Methods

Method 1 – Use Format Cells Option to Protect Columns with Password in Excel

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.

The Format Cells dialog box will appear.
➤ Click on Protection >> Uncheck the Locked option >> Select OK.

We will do that process again for the cells that we want to lock.
➤ Select the columns; Selling Price, and Cost Price, and go to the Home tab >> Cells group >> Format dropdown >> Format Cells option.

The Format Cells dialog box will appear again.
➤ Click Protection >> Check the Locked option >> Select OK.

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.

The Protect Sheet wizard will pop up.
➤ Type the password and press OK.

➤ Retype the password and press OK again.

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.

Method 2 – Using Allow Edit Ranges Option to Protect Columns with Password

Steps:
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.

The Allow Users to Edit Ranges dialog box will open up.
➤ Click the New option.

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

The Allow Users to Edit Ranges dialog box will appear again.
➤ Click on Apply.

➤ Select the Protect Sheet option.

The Protect Sheet wizard will pop up.
➤ Type the password and press OK.

➤ Retype the password and press OK again.

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

Method 3 – Protecting Columns in Excel with Formulas

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.

The Format Cells dialog box will appear.
➤ Click on the Protection tab >> Uncheck the Locked option >> Select OK.

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.

The Go To Special dialog box will open.
➤ Click on the Formulas option, and press OK.

You can see that the cells with formulas have been selected.

Open up the Format Cells dialog box by pressing CTRL+1.
➤ Click on the Protection tab >> Check the Locked option >> Select OK.

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.

The Protect Sheet wizard will pop up.
➤ Type the password and press OK.

➤ Retype the password and press OK again.

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

Method 4 – Applying VBA Code to Protect Columns in Excel with Password

Steps:
➤ Go to the Developer tab >> Visual Basic option.

The Visual Basic Editor will open up.
➤ Go to the Insert tab >> Module option.

A Module will be created.

➤ Write the following code in the module.

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

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

➤ Press F5.
We secured the Selling Price and Cost Price columns. Changing any value of these columns, the following error message will appear.