Scientific notation, also known as scientific form, is a way of representing numbers visually in a shortened format through the use of decimals and exponents.
It is a really useful way of displaying a lot of very large numbers alongside each other easily, even when the actual values may differ significantly.
In this simple guide, we are going to run through how set up formatting for scientific notation in Excel.
We will cover:
- Converting standard numbers to scientific form
- Specifying the number of decimals used
- Preventing numbers from automatic scientific notation
Let’s start by looking at how we can set this up.
Converting standard numbers to scientific notation
Let’s say we have a list of numbers, where the values differ greatly. Some may be in the tens of thousands, while others may go up into the millions, or tens of millions.
Typically these will all simply be represented in Excel as a standard number. Depending on formatting there may or may not be commas separating thousands, or there may be decimal places present, but the general look and feel is generally pretty standard.
If we wanted to represent these with scientific notation instead, this is literally as simple as changing the formatting settings. If we select the format up in the number section of the top ribbon, we can click on the current format to bring up a drop down box, and towards the bottom we can see Scientific as an option:
If we select this, all of the numbers change to now be in scientific form! Super easy:
Specifying the number of decimals used
Once we have the initial formatting set, we can go one step further by specifying the number of decimal places that are used in the displayed number. The default setting is two decimals, but we can change this by clicking on the increase decimal and decrease decimal buttons up in that same ribbon:
Here are some examples when expanded to 3 and 4 decimals in additional columns:
As we can see, tweaking the way this is displayed is also incredibly simple and done with a single button.
Finally, let’s take the opposite approach, and look at how to stop Excel from automatically turning larger numbers into a scientific format.
Preventing numbers from automatic scientific notation
In Excel, if we type a large number that has 12 or more individual numbers, it will automatically end up applying the scientific notation formatting to it. This can be handy with super large numbers as a number that is 50+ characters long can be a pain to read in a spreadsheet.
Sometimes though if our number is only 12 or 13 digits long, and the numbers alongside it we are comparing to are 10 or 11, it can actually be quite annoying when it displays those longer ones in a completely different way.
For example:
As we can see, two of these numbers have been converted into scientific notation. Making sure the entire column is consistent is actually super easy, and essentially the opposite of what we did earlier.
First, select the formatting drop down box up in the top ribbon like we did previously, and select Number. From here the numbers will automatically swap over to standard numbers where needed.
We can also pre-empt any of this by choosing the entire column and selecting number before we start adding in values.
This sums up our guide on working with scientific notation in Excel. Tweaking this formatting is very simple and can be super handy when working with larger numbers in an Excel spreadsheet.
For more handy guides on working with Excel, be sure to check out our Excel Tips page.