Formatting Cells is a way of changing the view of the Cell. All Cells by default is set as general with no specific format being assigned. Although as you would notice Excel tries and figures out the best format like Dates.

To Get to Number Formatting you can right-click on the specified Cell, Row or column and Choose Format Cells… alternatively press CTRL + 1

Scientific

Scientific Category is the Ninth of the explicit option you can choose from. The thing we need to remember is Excel does not read data the same we do. This is very evident when working with exponential notation.

What is Scientific Notation?

In all fairness, if you are here you probably know what Scientific Notation is but I am going to assume not everyone reading this know.

Scientific notation is a way of reading\writing numbers that are too big or too small to be conveniently worked with People.  This is done by writing the number you need to be able to view and then multiplying by 10 to the n-th power.

Reading this makes no sense so let’s look at an example of this.
Say you have the number 12345678901234567890 trying to work with this number although it is probably small enough that it can be done it does get difficult if you have multiple of these numbers that you need to work with.

This can be displayed as 1.234567 x 10 to 19th Power
Or as Excel would display it 1.234567E+19

This is as far as I am going to explain the Theory but below are some links with experts in Maths that explain this much better.

Back to Excel

Getting back to how Excel handles Scientific Notation. Excel never changes data only the View of the Data. So even if you Read it as 1.1E+2 the actual data still is 110. See table below as an example.

Excel will automatically do this for any number larger than 12 digits but you can adjust this explicitly. The only option you have is to decide how many Decimal places you want to be able to see.

So what happens when you type it directly into Excel as Scientific Notation. Well, it still works it out correctly in the sense it will still multiply it by 10 to the N-th power. But as you did not enter all the additional information all the extra information will be Zeroes.

It is important to note this will affect your calculation when working with formulas as with the first Tabel above it will take into consideration everything you are not seeing as it never changes your data so 123456789 x 123456789 will hield the right results does not matter which Scientific Notation you use or how many Decimals are displayed.

Compared to typing indirectly to Scientific Notation and choosing to lose some of the smaller numbers. Now I don’t work in those fields that uses this and I am sure there will be a degree to which the information\Data does not make a difference, but be aware of the difference when doing this.