How do I protect & hide formulas at the same time allow data entry in others? So, the entire sheet isn’t protect, just formula cells? Please help

like
Posting as :
works at
You are currently posting as works at

If by "protect & hide" you mean hide & lock only the cells you don't want them to change/see, that's pretty straightforward.

Now, I'll advise against lock & hide things: it is a bad practice - not only is not seeing well (depends on the market, but in general), but also it's VERY easy to remove the password protection.

Nevertheless, assuming you're on a given tab and only want to lock&hide the cells with formulas in that tab:

Step 1:
Select only the cells with the formulas, either by hand or, if they are many: Select all cells -> Home -> find & select -> go to special -> formulas (or Ctrl A + ALT H FD S F)

Step 2:
After selecting the cells you want to lock & hide, hit Ctrl + 1, go to "protection" tab (should be the last one), and mark both Locked and Hidden checkbox (or just one, depending on your needs)

Step 3:
Protect your sheet. Go to Review -> Protect sheet. You can choose what exactly you want to protect, but for your case the first two should be enough (it's the default option). Then, set a password and any user would need to input the password to see/change the cells you selected in Step 1*

That's it. Oh, and "Home", "Review" are tabs in the excel ribbon (in the top).

*Again, as a reminder, it is very easy to break the password protection in excel

like

Easiest way is to convert to zip, go to the log file, and remove the password

But in this case it's easier to just Google it

P.s.: Just a friendly reminder that hacking into other's excel are still considered a crime in most countries (#notalawyer)

"5. Protect the spreadsheet
Protecting the spreadsheet enables the "Locked" effect. To protect the spreadsheet, start at the "Review" tab, open the "Changes" tab, select "Protect Sheet" to open the "Protect Sheet" dialog, then create and type a password for the spreadsheet into the specified field. The password allows authorized users to access and edit locked cells. Next, select the actions allowed for users. By default, Excel selects the "Select Locked Cells" and "Select Unlocked Cells" actions when protecting the spreadsheet. These allow all users, including those with the password, to only select unlocked and locked cells.
You can choose the actions users can perform, including editing or adding formatting to unlocked cells, by checking or unchecking the boxes next to each action. Once you've chosen the allowed set of actions, select "OK." Excel will prompt you to "Confirm Password" by typing the same password again. Finish protecting the spreadsheet and locking the cells by selecting "OK." -From Indeed.com

like

Thank you, I’ve tried that and it locked the entire sheet. I just want to hide & lock the formulas & and have remaining cells for data entry.

Just hide the tab with the formula enter data in another tab then lock it

like

Related Posts

What would be the best time to let my team know that we are closing down? It will happen next month, so I figured that in about a week or two should be good enough to give them a chance to start looking elsewhere.

like

Back when I was getting started in the food world, I worked flipping burgers at In-n-Out. A man came in and said the burgers he had gotten weren’t right. He dumped the food on the counter and then he started throwing them back behind the counter. We gave him a refund and then his demeanor completely changed. He was calmer and he told us to have a great day as he left us to clean up the mess he made.

funnylike

Any intel on life in the King and Spalding corporate group in Atlanta, particularly the specialty groups (e.g., tax, benefits)?

like

I'm an MBA from the batch of 2020. I got into Social Media marketing (ORM) but want to switch to pure digital marketing as I don't see any growth in ORM & I'm terribly underpaid (Graduate level). I have a 1+ YoE. Looking for a job/profile switch. Any helpful suggestions/refs are appreciated.

like

I've been a government attorney for nearly three years now and have worked in the same office/division since I passed the bar. I've yet to receive a promotion other than two cost of living increases (in sum, a pay increase of 6k over 3 years). I know this is meager, but I'm mostly ok with it, I just want to see some recognition. On the other hand, it's hard for me to imagine negotiating because (given my inexperience), I don't feel I'd be able to do better anywhere else. Any advice?

like

Looking for a male flatmate in Pune for a semi furnished 3bhk
Society name: Marvel citrine
Rent: 45k
Includes basic Amenities such as 3AC, bed, cupboard, sofa, fan, light, gyezer, washing machine, fridge
Looking to close the deal ASAP
Brokerage applicable

like

Stay safe during the Tropical Storm everyone!

like

Hey Guys, Likes Please to activate DM

like

Hi ,

Looking for a job change and interested to join and explore your career in Qualcomm with below jobs, please DM me.

Post Photo

I love watching users go from lurkers to enthusiastic active participants. What do you find the most rewarding part of being a community manager?

like

My company is giving me a reimbursement while I go to school. It’s capped to induce me to study part time. I decided to go full time. Pay the difference out of pocket so I can graduate quicker. I have been working as a manufacturing engineer tech for 4 years without a degree but this May I will get my bachelors in Mechanical Eng. Currently making 63k a year, hourly. What should I ask for when I graduate? Living in Southern Pennsylvania.

like

Are there any broadway-like shows in Brooklyn playing the weekend of Thanksgiving that elderly and foreign relative friendly?

like

What states have a big law market where I can get the most out of my dollar? Also currently work for CA firm, if I move to another state does the state tax rate switch to where I live?

like

How long have you been teaching? Also, what does self care look like for you?

Is a bad relationship still worse than no relationship when you are working 100 hours a week?

like

What are Key questions asked for Program manager role at Amazon by HR?

like

Good lord, how’d he survive that?

like

Red Bull, please drop it!

like

Congrats to team Zoom Zoom on winning the Fish Bowl F1 fantasy league!

Post Photo
like

Additional Posts in Excel Genius

How do I compare two sheets to make sure they are identical?

like

What’s the keyboard shortcut to get into the “insert options” dialogue when you’ve inserted a new row with Ctrl+Shift+Plus?

like

Best way to lookup a value with multiple criteria across rows and Columns, for 3 criteria I usually do sum product. Any other tips?

like

How do I set the formula to calculate “If x falls between 1-10, return 1-10, if it falls between 11-20, return 11-20, and so on”

like

What are some common reasons that a COUNTIF formula might not be working correctly? I'm using the correct range and value to count. I'm not seeing "ERROR" but the values aren't correct (it's not counting everything). Thx.

like

This is dumb, but. How can I pull and average the numbers in column b associated with the 4 values in column a? So, (sum of all As/total number of As) without filtering?

Post Photo
like

I created a macro to help me save time on this onetime task and now whenever I open my excel, a blank excel opens with the name of the macro. How do I get rid of it?

like

Excel Rookie looking for a super simple formula to bring all information linked to a certain date in tab 1 to be organized under said date in tab 2.

like

Would anyone be willing to teach me vlookup?

like

Has anyone had an issue with index match and lookups returning NA when trying to manipulate data from a password protected workbook?

What’s the best way to summarize multiple amortization schedule. I have each schedule on a separate tab. On a master sheet can I do something where I can input a specific month and show my ST/LT ...

like

Hey guys, I'm trying to find a formula to use instead of the Goal Seek function. Anyone know it? I thought I had seen one years ago, but can't find it now.

like

HELP!
I have raw sales data on one tab, which includes a sales rep ID number. My second tab has that sales rep ID number in column 1 and the rep name in column 2. How can I get the rep name added to the correct line items in the raw data?

Can dates be grouped just by Month-Year in pivot tables on a Mac without having the year as its own dimension?

Post Photo
like

Struggling with this:
Suppose I want to shortlist companies that serve the priority industry vertical of a country. So I have the industries served by company (one column for each industry) and (cont)

Is there anything similar to the INDIRECT function that I can use to build a formula reference to another workbook? INDIRECT creates errors when the referenced workbook is closed..

like

How can I add multiple characteristics within one cell and make sure that a pivot table can read both values independently if needed? E.g., I have a column titled “color” and in one cell I write “red” and the other “red, blue”. I’d like a pivot table / formula to be able to count that there are technically two “reds” in this data set. Any tips?

like

Will need to migrate to office 365 soon. What’s the difference for excel from the 2013 version?

likehelpful

Any ideas on turning string data into numerical data in excel that’s faster/easier to automate/better than using text to columns? Wish I could use alteryx, but not an option for this use case.

like

Anybody know if there is a way to automate a Gantt chart in excel when building out a roadmap? Essentially want to recreate the functionality of ms project because my client does not have project and wants a nice visual for the roadmap

like

New to Fishbowl?

Download the Fishbowl app to
unlock all discussions on Fishbowl.
That was just a preview…
Sign Up to see all discussions
  • Discover what it’s like to work at companies from real professionals
  • Get candid advice from people in your field in a safe space
  • Chat and network with other professionals in your field
Sign up in seconds to unlock all discussions on Fishbowl.

Already a user?
Login here

Share

Embed this post

Copy and paste embed code on your site

Preview

Download the
Fishbowl app

See what’s happening in your industry
from the palm of your hand.

A phone with Fishbowl app

Scan your QR code to download
Fishbowl app on your mobile

Download app

Sign up for free to view this conversation on Fishbowl

By continuing you agree to Terms of Use and Privacy Policy

Already have an account? Log in

Sign up for free to continue using Fishbowl

By continuing you agree to Terms of Use(New) and Privacy Policy(New)
Messaging rates may apply

Already have an account? Log in

For account settings, visit Fishbowl on Desktop Browser or

General

Legal