Excel Auto formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-2597798%22%20slang%3D%22en-US%22%3EExcel%20Auto%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2597798%22%20slang%3D%22en-US%22%3E%3CP%3EHave%20an%20ongoing%20issue%20with%20Excel%20where%20it%20auto%20formats%20populated%20cells.%20Example%20I%20have%20client%20reports%20which%20are%20legal%20requirements%20for%20LOLER%20certification.%20If%20the%20serial%20is%2007-0399%20on%20the%20spreadsheet%20it%20will%20read%20correct%20once%20the%20cell%20is%20formatted%20to%20text.%20However%20when%20the%20spreadsheet%20is%20reopened%20Excel%20auto%20formats%20the%20cells%20to%20date%20again!%20There%20must%20be%20a%20way%20to%20stop%20this%20but%20even%20after%20speaking%20to%20Office365%20support%20last%20year%20the%20only%20option%20we%20have%20been%20left%20with%20is%20either%20use%20the%20%22get%20data%22%20option%20in%20Excel%20or%20use%20Google%20Sheets%20which%20doesn't%20auto%20format%20any%20data%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2597798%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

Have an ongoing issue with Excel where it auto formats populated cells. Example I have client reports which are legal requirements for LOLER certification. If the serial is 07-0399 on the spreadsheet it will read correct once the cell is formatted to text. However when the spreadsheet is reopened Excel auto formats the cells to date again! There must be a way to stop this but even after speaking to Office365 support last year the only option we have been left with is either use the "get data" option in Excel or use Google Sheets which doesn't auto format any data,

1 Reply

@StuHow 

 

How to protect formatting

 

  1. Open the relevant document and first select all the cells in your table that you want to protect.
  2. Right-click on the area and select the option "Format cells ..." in the context menu.
  3. Switch to the "Protection" tab and remove the checkmark next to "Locked". Confirm with OK".
  4. Now go to "Check" in the menu bar of Excel and click on "Protect sheet".
  5. Now select the elements that should remain available in the event of protection. In our case, these are all functions except formatting.
  6. You should therefore check all points except for the three fields "Format cells / columns / rows".
  7.  If you want, you can also provide protection with a password.
  8. Then confirm with "OK". If you have used the password option, enter it again and confirm again.
  9.  All cell formatting is then fixed and can no longer be changed.
  10. If you want to remove the protection, go to "Check"> "Remove sheet protection" again.

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.