how to hide formula in excel without protecting sheet?

Question

I want to hide formula in selected cells of microsoft excel, but i don't want to protect sheet.

Answer 1

i agree with Alan
it is the only way

Answer 2

Hide Formulas In Excel

Answer 3

Depends on what you want to accomplish by hiding the formula. If you're just trying to protect the user from them screwing it up, then a simpler way can be used.

I use defined names as formulas. That is particularly helpful with long or complex formulas. So if a user types over the formula I can easily replace it by typing in the name instead of the long or complex formula.

How to do it:

Lets say cell D30 contains a formula.

1) Position cursor in D30, press , highlight the entire formula, copy formula.
2) Click on "Insert" drop down, choose "Name", choose "Define"
3) In the Define Names window, type in a name, Example "QwertyKPH"
4) in "Refers to" box, paste in formula copied in step1
5) Click Add button and OK button
6) In cell D30, type "=QwertKPH"

You should get exactly the same result as actual formula. You can copy the formula as you would any other and cell references remain relative or fixed accordingly.

The other cool benefit of this method is recalculation times are reduced significantly. You will notice this especially when copying a long or complex formula into multiple cells.

Anyway, if user over types one of these its easier to replace than reconstructing an entire formula.


Hope this helps!
Kind Regards,
QwertyKPH @ Yahoo!

Answer 4

When I went into Format Cells, the Protection tab instructions said that it had to be protected for the Lock and Hidden to work.

If you don't mind creating duplicate sheets and doing a little work, there is a workaround.

Sheet1:
A1: 1
A2: 2
A3: =Sheet2!A3

Sheet2:
A1: =Sheet1!A1
A2: =Sheet1!A2
A3: =A1+A2

If you want to protect the formula used in cell A3 of Sheet2, hide that formula, and turn the protection on for Sheet2 only. The user will enter the data in Sheet1. They will only know that cell A3 in Sheet1 is equal to cell A3 in Sheet2. But they won't see how cell A3 in Sheet2 was actually calculated, because you hid the formula in Sheet2 and protected the sheet.

Answer 5

Dear
qwertykph

Amazing Answer.. It's Perfect for saving formula from preying eyes

Thanks