You are here:

Excel/Regarding excel sheet protection.



Please help me on the below.

1. Actually i want to know how to protect a particular row or column alone.
For ex: i have a excel file in which i need protect entire "B" column alone, and if i have used any formula it has to work as it is.

In addition once i protected entire "B" column,then the other user should not able to touch or edit the data.

2. How to hide the formula which has been used in excel file.

   If i keep the cursor in formula cell it should show only the formula output not formula.

Thanks in advance.


ANSWER: Hi Ravi,

You can follow these steps:
1. Select all cells in the sheet.
2. Click on Format - Cells
3. In the dialog box that opens up, go to the protection tab
4. Uncheck the "Locked" flag. Now all the cells in the sheet are unlocked.
5. Click ok and exit the dialog box
6. Select only column B
7. Go to Format - Cells - Protection tab and select the "Locked" flag. Now only column B cells are protected.
8. If you don't wan the user to see the formula, select the "Hidden" flag.

The locked and hidden flags will work only when you protect the worksheet. To do that, go to Tools - Protection - Protect Sheet.

You can decide what the user can do or not do on the protected sheet, set a password and protect the worksheet.

Hope this helps.


---------- FOLLOW-UP ----------

QUESTION: Hi Gulshan,

Thanks very much for you response.

Now i want to ask you some follow questions,

1. Once i used the above mentioned method some of formula does not working.

  For ex: In my file i am using some formula like now() to enter the date and time by using drop down, but once i protected sheet by using above way i could not able use formula, because it's saying read only.

  So i want to protect sheet and the same way i want to use my formula and my drop down cell also.

2. My next question is i want to protect only the entire "B" column alone with the feature of untouchable or non editable rest of the sheet should be normal.

Kindly assist me on the above.


Hi Ravi,

1. I did a quick test and the formula works fine even with a protected sheet. Are you trying to enter the formula on a locked cell of the protected sheet?

2. For your second question - If you have followed the steps 1-8 in the exact way, you should be getting the result. I'm not able to see how this point is different from your original question.

If you have too much trouble doing it, please mail me the file and I'll create the necessary protections. (; please mention subject as AllExperts)

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts




I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.


I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2016 All rights reserved.