How to use protected and hidden formula in Excel

Question

I have created an Excel spreadsheet useful for stock market investing. Daily stock price is inputted on each row giving output from the formulas on that row. X-axis in the time axis. The spreadsheet has a special formula that I want to protect and hide so that users cannot see or change. When I protect and hide the formula, I am unable to copy the formula down to the next row.

Answer 1

Hi Terry McCoy2,

Thanks for visiting Microsoft Community.

When using the 'Locked' and 'Hidden' features in Excel cell formatting, and enabling worksheet protection, it's clear that the formula in such cells cannot be applied to other cells. This ensures that the content you wish to protect cannot be obtained or inferred in any form.

Based on your needs, you can first disable worksheet protection, then after editing the formulas and other contents of the worksheet, re-enable protection before sharing it with others.

Please let me know your thoughts on this.

Best Regards,

Jonathan Z - MSFT | Microsoft Community Support Specialist

Answer 2

Jonathon:

May I send you the spreadsheet via email to explain better what I need.

Thanks, Terry McCoy

Answer 3

Hi Terry McCoy2

Please share the file with us via private message. I have enabled permissions for you.

Best Regards,

LucyW-MSFT | Microsoft Community Support Specialist

Answer 4

Thanks, Lucy. I want to protect and hide my formulas (columns E - H) but still be able to add another variable (stock price, row 1694, column C) and copy and paste results of the formulas from row 1693 to row 1694.

Surely Excel is capable of doing this, at least I hope it is!

Thanks, Terry McCoy

Answer 5

LucyW, I have not heard from you about my Excel problem after sending my xls file. Can you let me know what you think?

Thanks, Terry McCoy