Excel autoformatting large numbers

Copper Contributor

Hi, im having issues where im pasting large numbers into excel and it automatically formats them to

3,6106E+11

 and similar. How do i turn this off?
Ive tried changing the whole spreadsheet to numbers, text etc with no solution.

I know i can use space or ' before the numbers to stop formatting but this is a big spreadsheet where it will be used everywhere and i want a permanent solution :)

Thanks.

3 Replies

@jtconte 

If you are facing issues with Excel automatically formatting large numbers into scientific notation (e.g., 3.6106E+11), you can adjust the cell formatting to display the numbers in the desired format.

Here are a few steps you can take to address this:

Option 1: Change Cell Format

  1. Select the Range:
    • Highlight the range of cells containing the large numbers.
  2. Right-Click and Format Cells:
    • Right-click on the selected cells, and choose "Format Cells" from the context menu.
  3. Number Tab:
    • In the "Format Cells" dialog box, go to the "Number" tab.
  4. Number Category:
    • Choose "Number" from the list on the left.
  5. Set Decimal Places:
    • Adjust the number of decimal places as needed.
  6. Click OK:
    • Click "OK" to apply the new formatting.

Option 2: Use Custom Number Format

  1. Select the Range:
    • Highlight the range of cells.
  2. Right-Click and Format Cells:
    • Right-click and choose "Format Cells."
  3. Number Tab:
    • Go to the "Number" tab.
  4. Custom Format:
    • In the "Category" list, select "Custom."
  5. Enter Custom Format:
    • In the "Type" field, enter a custom number format. For example, 0 or 0.00.
  6. Click OK:
    • Click "OK" to apply the custom format.

Option 3: Adjust Excel Options

  1. Excel Options:
    • Go to "File" > "Options."
  2. Advanced:
    • In the Excel Options dialog box, select the "Advanced" category.
  3. Editing Options:
    • Under "Editing options," uncheck the option that says "Automatically insert a decimal point."
  4. Click OK:
    • Click "OK" to save the changes.

Option 4: Paste Special as Text

When pasting data into Excel, you can use the "Paste Special" feature:

  1. Copy Data:
    • Copy the data you want to paste.
  2. Right-Click Destination Cell:
    • Right-click on the cell where you want to paste the data.
  3. Choose Paste Special:
    • Choose "Paste Special" from the context menu.
  4. Select Values:
    • In the Paste Special dialog, select "Values" and click "OK."

This pastes the raw values without formatting.

Choose the option that best fits your needs or try a combination of these methods to resolve the issue. The text and steps were edited with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

@NikolinoDE 

I tried all of the options you listed and only one of them fixes it.

I have to use paste special to make it work but thats gonna be a time consuming manual paste i'd rather not do since there is a lot of numbers going into this sheet in the future :\

@jtconte 

If you are looking for a more automated approach without having to use Paste Special each time, you can consider using a helper column to convert the scientific notation to regular numbers. Here's a step-by-step guide:

  1. Assuming your data is in column A:
    • In an empty column (let's say column B), next to your first data entry, enter the following formula in cell B1:

=TEXT(A1, "0")

    • Drag the fill handle (a small square at the bottom-right corner of the cell) down to apply the formula to the entire column.

2. Copy the helper column:

    • Copy the entire helper column (column B).

3. Paste Special as Values:

    • Right-click on the first cell of the helper column (B1), choose "Paste Special," and then select "Values." This will replace the formulas with their calculated values.

4. Replace original column:

    • Copy the entire helper column again (now containing values, not formulas), and paste it back over the original column A using "Paste Special" > "Values."

This method uses a formula to convert the scientific notation to text, and then the "Paste Special" step is used to replace the original values with their formatted versions.

While this approach involves a few steps, it can be faster than using Paste Special each time you paste new data, especially if you have a lot of data to handle.

 

Using VBA (Visual Basic for Applications) provides a way to automate tasks in Excel. You can create a simple VBA macro to handle the conversion of scientific notation to regular numbers. Here's a step-by-step guide:

  1. Open the VBA Editor:
    • Press Alt + F11 to open the VBA Editor.
  2. Insert a Module:
    • In the VBA Editor, right-click on any item in the Project Explorer (usually on the left side) and choose Insert > Module. This will create a new module.
  3. Paste the following VBA code into the module:

vba Code

Sub ConvertScientificToNumber()
    Dim rng As Range
    On Error Resume Next
    ' Set the range to your data range
    Set rng = Selection
    
    ' Loop through each cell in the selected range
    For Each cell In rng
        ' Check if the cell contains a number in scientific notation
        If IsNumeric(cell.Value) Then
            ' Convert the value to a number and assign it back to the cell
            cell.Value = cell.Value + 0
        End If
    Next cell
End Sub

4. Close the VBA Editor:

    • Close the VBA Editor by clicking the "X" button or pressing Alt + Q.

5. Run the Macro:

  • Select the range where you want to convert scientific notation to regular numbers.
  • Press Alt + F8 to open the "Macro" dialog.
  • Select ConvertScientificToNumber and click "Run."
    • Now, you can run the macro:

This VBA macro iterates through the selected range and adds 0 to each cell, which effectively converts any numeric values in scientific notation to regular numbers.

Please note that running macros requires enabling macros in Excel, and it's generally recommended to save your workbook as a macro-enabled workbook (.xlsm) if you're using macros.

This VBA approach can be a more automated solution, especially if you frequently deal with large datasets. The text and steps were edited with the help of AI.