Locking formulas while allowing data editing

Scarlettrecords

New Member
Joined
Apr 6, 2010
Messages
14
Hi, I am new to Excel and finding the other answers unclear as to this distinction. This is URGENT as our accounts info is overdue to our tax agent & I still have to enter it all.

I have created an income property accounts workbook with 12 worksheets, one for each month of the year. I have written in formulas so that all income and outgoings are automatically entered into the "In" and "Out" columns and so that all columns are automatically added up at the bottom of each sheet and those totals are carried through to the "year-to-date" cells in subsequent sheet.

I want to be able to input and correct data (rents in, expenses out) without being able to accidentally edit or erase formulas.

How do I do this?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
By default, cells are locked. However, this only has effect when the worksheet is protected; by default, it isn't.

Here's what you want to do:

1. Unlock the cells that contain data entry. Go to Format->Cells, select the Protection tab, and uncheck the Locked box; click OK.
2. To protect the sheet once you've entered all the formulas, go to Tools->Protection->Protect Sheet.

Hope that helps.
 
Upvote 0
Welcome to the Board!

If you wish to just protect the formulas, do the following:

1. Select all Cells on your sheet
2. Go to Format Cells -> Protection and uncheck the Locked box
3. Hit the F5 key, click the Special button, select the Formulas radio box and select OK.
4. Go to Format Cells -> Protection and check the Locked box
5. Protect the worksheet (you'll probably want to assign a password)

Now just your cells with formulas will be locked.
 
Upvote 0
Welcome to the Board!

If you wish to just protect the formulas, do the following:

1. Select all Cells on your sheet
2. Go to Format Cells -> Protection and uncheck the Locked box
3. Hit the F5 key, click the Special button, select the Formulas radio box and select OK.
4. Go to Format Cells -> Protection and check the Locked box
5. Protect the worksheet (you'll probably want to assign a password)

Now just your cells with formulas will be locked.

Thank you! Regarding Direction '5', how do I protect the worksheet? Under tools I can protect the "workbook" - is that the same thing? If so, do I checkmark both 'Structure' and 'Windows'?
 
Upvote 0

Thank you - however, when I attempt that, the "protect sheet" option is in grey and unavailable but the "protect workbook" option is available. And when I select "workbook" I then have a decision to make about whether to checkmark "structure" and "windows". What should I do?
 
Upvote 0
Is the file currently protected and needs unprotecting first?
Is it a valid Excel file with a valid Excel extension (like .xls or .xlsx)?
 
Upvote 0
Strange. Do you have other Macros on the workbook or AddIn that may be greying out the Protect Sheet Menu?
 
Upvote 0
Is the file currently protected and needs unprotecting first?
Is it a valid Excel file with a valid Excel extension (like .xls or .xlsx)?

Regarding the second question: They are .xls so I think they are legit excel files. At the top left they say Microsoft Excel.

Regarding your first question, I do not know how to tell if the file is currently protected & needs unprotecting first. How do I check this?
 
Upvote 0
Protecting the workbook won't affect locked/unlocked cells. It's protecting the sheets that does it.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top