Calculation Issues

How to Fix Excel and XML Showing Different Decimal Values

Chanuka Geekiyanage Oct 10, 2026 870 views

Question details

Users need to understand and resolve the discrepancy where decimal numbers are displayed differently in XML files compared to when they are opened in Microsoft Excel.

Why XML and Excel Show Different Decimal Values

Product: Microsoft Excel
Device & OS: not provided
Scenario: Importing, viewing, or analyzing numerical data from an XML file inside a spreadsheet application.
Observed behavior: XML displays long decimal expansions (e.g., -1.9219999999999999) while Excel rounds and displays a shorter version (e.g., -1.922) due to floating-point storage limits.

Before you start

Before importing your XML file, determine whether you need to perform mathematical calculations on these numbers in Excel, or if you strictly need to preserve their exact text representation for data integrity.

Solution 1Recommended

Import XML Data as Text Using Power Query

Importing the XML values as text prevents Excel from automatically parsing and rounding the decimals according to its 15-digit floating-point limit.

Excel uses the 64-bit binary floating-point standard to store numbers, which restricts precision to a maximum of 15 significant digits. XML, on the other hand, can store up to 17 significant digits to ensure exact round-trip conversion. To bypass Excel's automatic numerical rounding, you must instruct the software to treat the incoming data purely as text strings.

Open Data Import

Open a blank workbook in Excel, navigate to the 'Data' tab on the ribbon, and click on 'Get Data' > 'From File' > 'From XML'.

Select the XML File

Locate your XML file in the file browser, select it, and click 'Import'.

Transform Data

When the Navigator window appears, click the 'Transform Data' button at the bottom to open the Power Query Editor.

Change Data Type to Text

Select the column containing your long decimal values, click the data type icon in the column header (usually '1.2' for decimals), and change it to 'Text'. Choose 'Replace Current' if prompted.

Load the Data

Click the 'Close & Load' button in the top-left corner to bring the exact XML text values into your spreadsheet without any truncation or rounding.

Import XML Data as Text Using Power Query

Calculation Limitation: Numbers formatted as text cannot be used in standard mathematical equations directly. Converting them back to numbers later will reintroduce the 15-digit rounding.

Solution 2

Use VBA with Custom XML Parsing

For advanced users, writing a custom VBA script ensures precise control over how XML nodes are read and formatted in the spreadsheet.

Use WPS Spreadsheet for Data Handling

Handle XML and CSV Data Accurately with WPS Office

WPS Spreadsheet offers an intuitive Data Import Wizard that allows you to effortlessly define column formats—such as text—to preserve the exact precision of your imported data without unwanted floating-point rounding.

1. Open a Blank Spreadsheet: Launch WPS Office, select Spreadsheet, and open a new blank workbook.
2. Initiate Data Import: Navigate to the 'Data' tab on the main ribbon and select 'Import Data'.
3. Select Your File: Choose the data file you wish to import and proceed to the Text Import Wizard.
4. Set Column Formats: In the data preview step of the wizard, highlight the columns containing your precise decimals and set their Column Data Format to 'Text'.
5. Finish Import: Click 'Finish' to successfully load your data exactly as it was written in the source file.

Free, lightweight, and fast alternative to Microsoft Excel.Highly compatible with .xlsx, .xls, .csv, and XML data files.Flexible import options to prevent automatic decimal rounding.Familiar tabbed interface for a zero-learning-curve transition.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel change my decimal numbers automatically?

Excel relies on the 64-bit binary floating-point standard to calculate and store numbers. Because most decimal fractions cannot be represented exactly in binary, Excel stores a close approximation and limits its display to 15 significant digits.

Can I force Excel to show more than 15 significant digits?

No, the 15-digit limit is hardcoded into Excel's calculation engine. If you need to view or store numbers with more digits (like those from a 17-digit XML file), you must import and format them as text.

Is the underlying value actually changed when imported from XML?

Often, the discrepancy is purely a representation issue. XML may store up to 17 significant digits to ensure a binary value can be perfectly restored. Excel stores the same binary approximation but truncates the visual display to 15 digits.

How do I fix floating-point inaccuracies if I still need to calculate the data?

If mathematical operations are required, you can utilize the ROUND function within your formulas to standardise the significant digits, or enable the 'Set precision as displayed' option in Excel's advanced settings.

Excel & Data Problems