Comments on: How to hide and lock formulas in Excel

This tutorial shows how to hide formulas in Excel so they do not show up in the formula bar. Also, you will learn how to quickly lock a selected formula or all formulas in a worksheet to protect them from being deleted or overwritten by other users. Continue reading →

Comments page 3. Total comments: 92

  1. How can I protect my formula column if i past special in entire sheet

    1. Nothing error should pop up but past past special should be done

    2. Nothing error should pop up

  2. i am having and excell file with recipes and the formulas are locked and i cant edit. when i edit the formula does not activate.

  3. can you point me in right direction?
    Sheet1 is connected to Sheet2
    when A1sheet1 changes row1sheet2 changes.
    When A2sheet1 changes row2sheet2 changes...so on
    I want to lock ColumnAsheet2 so that once I change values in Sheet1, values in ColumnAsheet2 doesn't change.
    Thank you.

  4. Thank you very much for this tips.

  5. how to protect only some cells(like two cells in worksheet)? please mam reply.

  6. Hi,

    I want to lock the cell which is containing a formula of SUM for another few cells, when i lock the Formula cell and then go to protect sheet and open the sheet from other users pc its protecting the whole sheet rather the highlighted cell i.e =SUM(D3:D11) i want D 2 to be locked but the rest of the cells D3 to D11 to be available for entering values.

    Thank you.

  7. i want to protect my formulas but still be able to enter an amount. by protecting the formulas i can't insert anything in those blocks. please help

  8. When adding a a new row to a protected worksheet I would like the formulas of the protected cells to automatically populate into the new row (without having to unprotect the sheet). Is there a way to do this?

    Kindly,
    Bailey

  9. I want to hide the formula only keeping other all cells editable.
    Formula's will be hidden but still it will work. Can you pls suggest ?

  10. hi i just want to lock the formula coulmn but when i lock and protect the sheet whole sheet is protected no value can be changed anyone plz help me

  11. Hi,

    I have an Excel sheet that I would like to send to others. I want them to be able to make their changes and be able to send the sheet back to me. However, I do not want them to have any access to my formulas at all for security purposes.

    Will locking and hiding the formulas ensure that they will not be able to access the formulas?

    Thank you very much!

  12. Hi,

    When I protect the sheet, then all the other buttons are not working. they got disabled. So can you please give me solution on this ?

    Thanks and regards,
    Aniket

  13. Hello,

    I want to hide cell which contain some formula, but when i am hiding the formula cell its automatically getting applied on the cells which are present in formula.

    for example - =IF(ISBLANK(D11),"",IF(((D11/30)*100)>89,"A+",IF(((D11/30)*100)>74,"A",IF(((D11/30)*100)>55,"B",IF(((D11/30)*100)>34,"C","D")))))

    if this formula is on column E but this formula contains col D in it, so when i am hiding col E. col D is also getting hide automatically.

    Please help me

    Regards
    Mayank

  14. Hello!
    I would like to enter a formula that uses =if(and( )) to grade my candidates according to their aggregate for instance: =if(d7<=12,"div1") but that condition is only true if the candidate has not scored a 9 in any of the four subjects making the aggregate 12. I tried entering like this =if(d7<=12(and(a7,b7,c7<9,"div1") but showed error that i couldn't correct so i got stuck.

    1. Hello Oromokoma,
      if you want to check whether no one scored 9, but it could be 10 or 11, then
      =IF(AND(D7<=12, A7<>9, B7<>9, C7<>9),"div1","")

      But if the score number should be lower than 9, then
      =IF(AND(D7<=12, A7<9, B7<9, C7<9),"div1","")

      The formula will return "div1" ONLY when all 4 conditions are met. If you meant something other than that, explain your task in more details and email us at support@ablebits.com with the workbook with data attached. Also, provide a link to this article and your comment.

  15. Hi, How can i lock a cell after data was is entered automatically?
    I want cells to be open for 7 days every month and after a certain date the cell must locked automatically.

  16. Hi, I was wondering if I was to use 5 different cell with each having a different formula like =SUM(A2*A3) and =B4-B5 and =C6/C7 etc. to see the bigger picture like creating a conversion counter. Are there a way to lock it so that when I change one of the cell with whatever number that all will follow and change? Because when I try to change a number in a cell, the formula goes away.
    Sorry this sounds confusing. It is hard to explain in writing.

  17. How you can insert any text in specific cell of excel, keeping range of number “0 to 100”

  18. I want to hide the formula only keeping other all cells editable.
    Formula's will be hidden but still it will work. Can you pls suggest ?

  19. Hi,

    I want your help i dont know how to protect the cells with the formulas to avoid any edition of them, but to allow users to drop or copy the formula to a new cell in case they need to add information.

    Thanks

  20. I have a perpetual spreadsheet and the date column has a circle formula =IF(D15"",IF(C15="",NOW(),C15),"") and I don't want to have the formula deleted. I have tried the protect sheet but this will prevent the spreadsheet to be perpetual like adding in a new row when the tab is hit.

  21. Dear Sir,
    I want Hide Formulas and display values in formula bar

  22. I need to protect and hide my formula cells...

    I did everything and it's protect and hide perfectly but the problem is after i enter the data s in the unprotected cells i cant able to edit option , if i click f2 then the error msg comes.

  23. dear sir,

    how to hide cell value in formula bar without protect sheet and hid formula bar.

    1. Hi Nitin,

      It's not possible to hide a cell value in the formula bar. To hide the formula bar, go to the View tab > Show group, and clear the the Formula Bar check box.

  24. I,am not satisfied with your solution

    i want just to hide formula without protecting the whole sheet

  25. I have used a basic formula =A2+7

    However, I am trying to determine how to prevent any results to display prior to the data being entered. Meaning, I would like the date to only show up once someone has entered it into A2.

    1. =IF(A2="","",A2+7)

      In other words, if cell A2 is blank then the cell in which the formula is located should remain blank. Otherwise the basic formula is performed.

  26. I have 3 value
    1 79
    2 76
    3 256.45*7%
    4 blank cell
    Dear,

    I want you from ur side that how we can automate value in 4th blank sale of highest value when we can change value 1 and 2 manually 4th blank cell can be give highest result.it should be used via IF condition ... i find my result by max function but i want this from if condition

  27. i a have a workbook with sheets and its not my own work
    i need to show the cells formula cause i need to change something in the formula to my own need the broblem is that the cells are protected what can i do ???

  28. I have logical statement formulas in cells and a check box that is used to launch the formula and place a value in the cell based on the formula. I would like to protect the worksheet, but when I protect the sheet and click on the check the box, it won't place the value into the cell.
    How can I use the check box and logical formulas within a protected worksheet?

  29. Hi, I wonder if there's a way to retrieve forgotten password for a protected sheet. Your guidance is highly appreciated...

    Long

      1. Hi Svetlana.
        With this password removal tool, any pass-worded Excel workbook can be breached, right?

      2. Thanks a lot.

  30. Is it possible to not only hide and protect certain formulas on an excel spreadsheet, but to also hide a pivot table that is on a separate sheet within the workbook?

  31. i want an if formula, where if one value is equal to another value, than a third value should come in that cell.
    =if(cell1=cell2,.....)

    pls corrct the formula

    1. ok

      Thanks & Regards

    2. Hi Harji,

      Here you go:

      =IF(A1=A2, A3, "")

      The formula reads as follows: if A1 is equal to A2, copy a value from A3, otherwise return an empty string (blank cell).

      You can find plenty more formula examples in the following tutorial: How to use the IF function in Excel.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)