How-To

How to Disable Scientific Notation in Excel

how to disable scientific notation in excel

If you don’t want Excel to convert longer numbers into scientific notation, there are a few ways to disable it.

Excel has a lot of features that can make your life a little easier. These include useful keyboard shortcuts as well as features such as automatic formatting. For example, if you type a number into a cell, and the number is wider than the width of the cell, the column will automatically resize to fit the number inside it. At least, that’s how it’s supposed to work. However, if your number is 12 digits or longer, Excel does something else—it converts your number to scientific notation. If that’s not what you want, you can disable scientific notation in Excel.

What is Scientific Notation?

Some branches of science deal with very large numbers. For example, the Avogadro constant is 24 digits long when written in standard notation.

Other branches of science use very small numbers. The mass of a proton, for example, is a number that has 26 zeros after the decimal point. Writing out such long numbers isn’t practical.

Scientific notation is a way of writing numbers as powers of 10. For example, 1000 is 10 x 10 x 10 (10 cubed), thus can be written as 100^3 when using exponents. 6000 would then become 6 x 10^3. 6543 would become 6.543 x 10^3, and so on. The power of ten saves having to write a huge list of zeros, especially for longer numbers such as 6.02 x 10^23.

For numbers less than 1, negative indices are used. 0.001 is 10^-3, so 0.005 would be 5 x 10^-3, and so on.

Why Does Excel Convert Numbers into Scientific Notation?

Excel converts numbers into scientific notation for the same reason that they were created in the first place: to save space.

If a number is longer than the space available in the cell, Excel will instead show the number in scientific notation. Any number that is 12 digits or longer will also automatically be converted into scientific notation.

Unfortunately, there’s no way to turn this feature off—Excel will always convert longer numbers into scientific notation. However, all is not lost. There are a few ways that you can force Excel to show the number in standard notation instead.

How to Remove Scientific Notation by Changing Cell Width

If Excel has changed your number into scientific notation, and your number is 11 digits or fewer, then you can return it to standard form just by making the cell wide enough to show the full number. Once there is enough room to display all the digits of your number, it will return to the original format.

  1. Locate the cell that has been converted to scientific notation.
  2. At the top of that column, hover over the right-hand side of the column label until the cursor becomes a vertical line with two arrows on either side.
    excel column width
  3. Click and drag to the right until the cell is wide enough to show the number in standard notation.
  4. Release the cursor and the number will convert back to standard notation.
    excel standard notation
  5. If the cell is too wide, you can use the same method to resize it, provided that you don’t make it smaller than the width of your entire number.

How to Remove Scientific Notation by Changing Cell Formats

If your number is 12 digits or more, even if your cell is wide enough to display the whole number, it will still be converted to scientific notation. Since there’s no way to disable this conversion, you have to force Excel to show the number in the format that you want. One way to disable scientific notation from appearing in your Excel spreadsheet is to change the cell format.

  1. Right-click on the cell containing your number.
  2. Select Format Cells.
    excel format cells
  3. In the Category section, select Number.
    excel number format
  4. At the top of the right-hand pane, you’ll see a sample of how your number will look using the current settings.
    excel sample number format
  5. If your number does not include any decimals, use the down arrow to change decimal places to 0. You can check the sample again to ensure this looks right.
    excel zero decimal places
  6. If your number has multiple decimal places, use the up arrow to increase the number of decimal places. Once again, you can use the sample to ensure that you have selected the correct number.
    excel multiple decimal places
  7. If your number has leading zeros, such as the number 0012345 then this won’t display correctly using the Number format. You’ll need instead to select the Custom category.
    excel custom format
  8. Select 0 from the list of formats.
    excel zero
  9. Click in the Type box and add additional zeros until the number of zeros matches the amount of digits in the number you want to display. You can use the sample to check how the final number will display.
    excel custom format
  10. When you’re happy that the sample looks like the number that you want to display in your cell, click OK.
  11. Your number will now display as you want it.

How to Convert Scientific Notation Back Into Standard Notation

You can also use some Excel functions to force Excel to display your number in standard notation. This method puts the correctly formatted number in a new cell, which is something to take into consideration when you’re choosing a method to use.

  1. Click in an empty cell.
  2. Type =CONCAT(
    excel concat function
  3. Click the cell containing the number you want to convert.
    excel select cell
  4. Type closed bracket and press Enter.
    excel concat formula
  5. Your number will appear in standard notation.
    excel concat result
  6. You can also achieve the same effect by replacing =CONCAT( in step 2 with =UPPER( or =TRIM(

How to Force Excel to Display Standard Notation by Using an Apostrophe

One final method that you can use to trick Excel into displaying the number format that you want is to use an apostrophe before your number. The number will display without the apostrophe and will be in standard format regardless of length.

However, this will generate an error message on the cell, informing you that Excel is treating the number as text, but you can happily ignore this if you wish. If you reference the cell in calculations, it will still act as if it is a number.

  1. Click in the cell where you want your number to appear.
  2. Type an apostrophe ().
    excel apostrophe
  3. Enter your number as usual.
    excel long number with apostrophe
  4. Press Enter and the apostrophe will disappear.
    excel long number with hidden apostrophe
  5. If you click the cell, you’ll see the error message indicated by the green triangle.
    excel error message
  6. Feel free to ignore the error!

Managing Your Excel Spreadsheet

Whilst many Excel features are intended to make life easier, sometimes they can end up making things worse. Learning how to disable scientific notation in Excel ensures that your numbers display exactly how you want them to.

Click to comment

Leave a Reply

Your email address will not be published. Required fields are marked *

 

To Top