How do you lock formats, but allow data entry?

I've tried that.  If I protect the sheet and unlock a group of cells, I can change both formatting and data.  If I lock the cells, i can't change the data.  Using the protect sheet dialog box, I could do the reverse, protect the data, but allow the format to be changed.  But it doesn't seem to help with what I want.

You haven't seemed to follow exactly the advice given. 

First off, before protecting you highlight the cells you want the data to be able to be changed.  Then you right click and select FORMAT CELLS and then go to the protection tab and UNCHECK the LOCKED checkbox.  Then you go to PROTECTION (in Access 2003 it would be TOOLS > PROTECTION > PROTECT SHEET and then when the dialog comes up it shows a dialog which lets you put the password in, but also has checkboxes for many different things.  So you would want to make sure that FORMAT CELLS is UNchecked but SELECT UNLOCKED CELLS is checked.

Once you do that, you should be able to click OK and then it should be protected for the formatting but data can be entered.  I know it works because I just tested it.

Ah, yes, that helps some.  I can still copy and paste to change the formatting, though.  It looks like it just locks the controls for formatting rather than protecting the cell itself.

Unfortunately the naive user is more likely to goof up cutting and pasting rather than navigating the tool bar to change the format on purpose.

In short: it can't be done. At least not easily.

People have tried writing VBA to re-apply formatting to cells, but it gets very, very complex. There are simply too many ways to paste (ribbon, keyboard shortcut, context menu, macros), then there is drag and drop, and it is near impossible to catch all methods.

The only way out here is user education. If you keep your users dumb, your problem will never go away. Teach them, and you may have a chance.

Not sure why I have been paying thousands of dollars over the years to keep upgrading office suite, it gets harder and harder to use every year and they haven't added any meaningful feature sfor me in a decade. 

Can anyone recommend an alternative to excel that CAN do this?  We do a lot on spreadsheets but we have a ton of novice/data entry users and they need to be able to work within a spreadsheet without accidentally destroying it.  Excel does not suit our needs at the moment.

Most people using the spreadsheets I have prepared do not know enough not to copy and paste. I end up with cell format changes as a result. The way mentioned here does not protect that nor does any other way I have seen.

If a cell is unlocked in a protected sheet, then its format can be changed. 

That's it. 

None of the approaches or workarounds discussed above or found on the web will really work. 

The question is: why would the user want to change the format? 

This whole issue is more about user education than about tech. 

If they change the format inadvertently, because they copy and paste from somewhere else, then instruct them not to do that or show them how to paste values so they don't affect the format. 

If they change the format intentionally, then instruct them not to do that. 

If they don't comply, then that is a behavioural problem and these cannot be solved with technology. 

You're in the wrong here.

You're standing by the statement "These behaviors cannot be solved by technology"? 

All that has to be done is make an option that when pastes are made, it's VALUES ONLY, and not formatting.

Period. 

This shouldn't be difficult for MS to do, and should've been done 20 years ago. 

This is much like how excel still doesn't offer decent sharing of workbooks (our workbooks have become worthless after sharing for a brief moments, if they were even usable with the reduced functionality MS imposes in that mode), yet Google Docs made seamless workbook sharing with realtime updated multiple-user-editing years ago--for free. 

MS Office thinks they can sit by and do nothing because they have a large market share. How did that work out for Internet Explorer? Chrome and Firefox offered the "technology fixes" to accommodate "a lack of user education" that IE decided it didn't need to do because of marketshare. Now it's not even a product.