Handling “Arithmetic Overflow” when importing BigInt data into Excel.

When importing SQL Server tables into Excel, encountering an arithmetic overflow or seeing large numeric identifiers distorted indicates a mismatch between database architectures and spreadsheet cell memory. In SQL Server, a BIGINT is an eight-byte, 64-bit signed integer capable of storing values up to 9,223,372,036,854,775,807. Excel worksheets, however, adhere strictly to the IEEE 754 floating-point specification [1], which limits numeric precision to 15 significant digits.

When a BIGINT value exceeds 15 digits (values greater than 999,999,999,999,999), Excel either rounds the trailing digits to zeroes, throws an arithmetic overflow error through the ODBC driver, or displays the number in unhelpful scientific notation.

Quick Fix: Import BigInt Without Overflow

To prevent arithmetic overflow and precision loss, cast the BIGINT column to text at the source query using CAST(MyBigIntColumn AS VARCHAR(20)) or open Power Query, select the column header, and change the data type to Text before clicking Close & Load. This preserves numeric identifiers up to 19 digits without alteration.

---

Why BigInt Triggers Arithmetic Overflow in Excel

An arithmetic overflow occurs when a calculation or data stream generates a numeric value that exceeds the memory buffer or data type boundary assigned to receive it. When moving data from SQL Server to Excel, this failure happens across three primary failure points:

1. ODBC/OLE DB Driver Buffer Limits: Legacy connection drivers (such as older SQL Server Native Client drivers) may attempt to map BIGINT values into 32-bit integer structures or standard numeric buffers that cannot allocate 64 bits of integer storage.
2. The 15-Digit Worksheet Ceiling: Standard Excel worksheet cells do not have a native 64-bit integer type. They store all numbers as double-precision floating-point numbers. Any integer larger than 9,007,199,254,740,992 (2^53 – 1) loses precision, and numbers past 15 digits have their lower digits truncated to zeros.
3. Implicit Conversion in Formula Calculations: If an imported big integer is referenced in mathematical operations inside Excel, the calculation engine immediately overflows or truncates the result.

When dealing with database keys, transaction serials, or account numbers, this silent rounding corrupts data integrity. If the failure occurs earlier during query execution, review Fixing “Arithmetic overflow” during server-side aggregations to determine whether the issue originates inside the database engine before reaching Excel.

---

Diagnostic Steps: Pinpointing Where the Overflow Occurs

Follow this sequence to identify whether the overflow stems from the query execution, the connection pipeline, or the worksheet grid:

[SQL Server BIGINT Source]
            │
            ▼
[Execute in SSMS] ──(Fails)──► Resolve SQL-side overflow / aggregation
            │ (Succeeds)
            ▼
[Power Query Preview] ──(Error)──► Change data type to Text or Int64.Type
            │ (Valid)
            ▼
[Load Destination]
   ├──► Worksheet Grid (Truncates at 15 digits / Displays scientific notation)
   └──► Power Pivot Data Model (Preserves full 64-bit BigInt natively)

Step 1: Run the Source Query in SQL Server Management Studio (SSMS)

Execute the exact extraction query in SSMS. If the query throws:
Msg 8115, Level 16, State 2: Arithmetic overflow error converting expression to data type int.
The overflow is happening on the server side, typically caused by a COUNT(), SUM(), or mathematical operation defaulting to a 32-bit INT. Replace COUNT() with COUNT_BIG() and ensure intermediate calculations cast to BIGINT or DECIMAL.

Step 2: Check the Power Query Transformation Step

If the query runs cleanly in SSMS but fails when refreshing in Excel, open Power Query (Data > Queries & Connections, right-click the query, and select Edit). Look at the Changed Type step. Power Query natively supports an Int64.Type. However, if an automatic conversion step attempted to force the column into Int32.Type or a currency format, Power Query will generate Error cells. In that scenario, consult “The value was too large or too small for an Int64”: Fixing Integer Overflows.

Step 3: Inspect the Grid Output

If the data loads into the grid as 1.23457E+18 and changing the format to “0” turns the last three digits into 000, the driver succeeded, but the Excel worksheet calculation engine destroyed the precision.

---

Primary Solutions

Depending on whether the BIGINT column represents an identifier (like a Tracking Number or Customer ID) or a numeric value required for mathematical aggregation, choose the appropriate solution below.

Solution 1: Cast to Text in the SQL Statement (Recommended for Identifiers)

The cleanest way to handle 64-bit numeric keys is to prevent Excel from interpreting them as numbers. Modify your SQL query statement:

SELECT
    CAST(TransactionID AS VARCHAR(20)) AS TransactionID,
    CustomerID,
    TransactionAmount
FROM Sales.Transactions;

By casting to VARCHAR(20), the ODBC/OLE DB driver delivers the payload as a string. Excel writes the value directly into the cell as text, preserving every digit up to the full 19 digits of a signed BIGINT. This also avoids Troubleshooting “Scientific Notation” conversion of SQL ID strings.

Solution 2: Convert to Text Inside Power Query

If you connect directly to database tables without writing custom SQL statements:

1. In the Power Query editor, select the BIGINT column header.
2. In the Transform tab, click Data Type.
3. Select Text instead of Whole Number.
4. If prompted to replace the current step or add a new step, choose Replace Current.
5. Click Home > Close & Load.

let
    Source = Sql.Database("ServerName", "DatabaseName"),
    Sales_Table = Source{[Schema="dbo",Item="Sales"]}[Data],
    #"Changed Type to Text" = Table.TransformColumnTypes(Sales_Table, {{"TransactionID", type text}})
in
    #"Changed Type to Text"

Solution 3: Load Directly to the Power Pivot Data Model (For Math and Aggregations)

If you must perform mathematical operations on 64-bit numbers without truncation, bypass the Excel worksheet grid entirely:

1. Open Power Query and verify the column is typed as Whole Number (Int64.Type).
2. Click the dropdown arrow under Close & Load and select Close & Load To….
3. In the dialog, select Only Create Connection and check Add this data to the Data Model.
4. Click OK.

The Power Pivot Data Model (xVelocity VertiPaq engine) runs on a true 64-bit analytical backend [2]. Unlike worksheet cells, Power Pivot stores and computes full 64-bit BIGINT values without throwing arithmetic overflows or truncating past 15 digits. You can then build DAX measures and summarize your data via PivotTables safely. For schema configuration details between SQL and the data engine, review Troubleshooting “Data Type Mismatch” between SQL Server and Power Pivot.

---

Verifying the Solution

To confirm that your import pipeline is functioning without data corruption:

1. Check Cell Formatting: Click a cell containing an imported 18- or 19-digit number. Look at the formula bar. If the number has an apostrophe prefix (') or aligns to the left by default, it is stored as text and protected from IEEE 754 floating-point truncation.
2. Reconciliation Check: Compare the final digits of the largest BIGINT record in SQL Server against the corresponding cell in Excel. If the database ends in ...8742 and Excel displays ...8000, the column was converted as a worksheet number and must be re-imported as text.

If your dataset contains fractional values or extremely high-precision monetary amounts alongside large integers, refer to Fixing “Precision Loss” when importing SQL DECIMAL(38,18) into Excel. For broader mapping discrepancies across relational schemas, review SQL-to-Excel Data Mapping: Fixing Type Mismatches and Schema Breaks..