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?
 

iliace

Well-known Member
Joined
Jan 15, 2008
Messages
3,541
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,824
Office Version
365
Platform
Windows
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.
 

Scarlettrecords

New Member
Joined
Apr 6, 2010
Messages
14
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'?
 

Scarlettrecords

New Member
Joined
Apr 6, 2010
Messages
14
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?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,824
Office Version
365
Platform
Windows
Is the file currently protected and needs unprotecting first?
Is it a valid Excel file with a valid Excel extension (like .xls or .xlsx)?
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,561
Office Version
2016
Platform
Windows
Strange. Do you have other Macros on the workbook or AddIn that may be greying out the Protect Sheet Menu?
 

Scarlettrecords

New Member
Joined
Apr 6, 2010
Messages
14
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?
 

iliace

Well-known Member
Joined
Jan 15, 2008
Messages
3,541
Protecting the workbook won't affect locked/unlocked cells. It's protecting the sheets that does it.
 

Forum statistics

Threads
1,085,989
Messages
5,387,127
Members
402,045
Latest member
Hidalgo

Some videos you may like

This Week's Hot Topics

Top