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.
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.
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.
Then, the Format Cells dialog box will appear.
➤ Click on Protection, uncheck the Locked option and then select OK.
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.
Afterward, the Format Cells dialog box will appear again.
➤ Click on Protection, check the Locked option, and then 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.
After that, the Protect Sheet wizard will pop up.
➤ Type the password and press OK.
➤ Retype the password and press OK again.
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.
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.
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.
After that, the Allow Users to Edit Ranges dialog box will open up.
➤ Click on 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.
Afterward, the Allow Users to Edit Ranges dialog box will appear again.
➤ Click on Apply.
➤ Then, select the Protect Sheet option.
After that, the Protect Sheet wizard will pop up.
➤ Type the password and press OK.
➤ Retype the password and press OK again.
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.
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.
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.
Then, the Format Cells dialog box will appear.
➤ Click on the Protection tab, uncheck the Locked option and then select OK.
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.
After that, the Go To Special dialog box will open.
➤ Click on the Formulas Option, and press OK.
Then, you can see that the cells with formulas have been selected.
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.
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.
After that, the Protect Sheet wizard will pop up.
➤ Type the password and press OK.
➤ Retype the password and press OK again.
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.
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.
Steps:
➤ Go to the Developer Tab >> Visual Basic Option.
Then, the Visual Basic Editor will open up.
➤ Go to the Insert Tab >> Module Option.
After that, a Module will be created.
➤ 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 SubHere, 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.
➤ 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.
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.
