Excel files formatting is lost when opening in Calc program


I am rather new to using LibreOffice and more specific the Calc program. I usually use excel files that multiple spreadsheets with a lot of formatting (such as data validation with different colors), pie charts and formulas. When I open my excel file in calc, most of my formatting is gone, such as colors disappear, hidden rows & columns reappear and when saving and opening in Excel, the formatting is bad there too.

Also when I made an entire document in Calc and then saved it in an Excel format, opening in Excel still made all my formatting look very bad.

Is there any extension or setting that could be used to make the excel documents be more compatible with Calc?

The Libre office version I use is Version: (x64) and MSO 2016.

I am trying to find a solution so that I can work with both Calc and Excel, since most of the people I work with are using the Microsoft Office suite and I cannot send badly formatted documents or spend twice the amount of time to re-edit them.

Also I have noticed that when you open an excel file in Calc, put it in share mode, if selecting the use microsoft format in the save message, the document is not shared and other users cannot modify it.

Thank you,