You will often want to copy the result of an SQL query to Excel and send it on, for example so someone can check the figures, or just as a simple list. You will then also notice that Excel shows the numbers in millions. This article covers the options to deal with this.
Say you copy 10,000 rows from SQL Server to Excel. The result holds a number: 42. In the database this is a decimal(10,2). That means the number looks like this:
After copying, it looks like this in Excel:
This is of course a nuisance for whoever has to work with the numbers, because Excel now thinks it is 42 million!
The first solution is in the SQL query. What you can do is CAST the number to money. This way the dot disappears from the number and is replaced by a comma. Excel will recognize this as a decimal separator (the dot is a thousands separator). The downside is that you lose more than 2 digits after the decimal separator. Whether this is a problem depends on your data.
SELECT CAST(Waarde AS MONEY) AS WaardeOption two is to change a setting in Excel.
Go to File, Options, Advanced and change the separators.
Frequently asked questions
Why does Excel show my SQL number as millions?
A decimal(10,2) such as 42.00 arrives in Excel with a dot. Excel sees that dot as the thousands separator and reads 42 as 42 million.
How do I stop Excel from reading decimals from SQL Server as millions?
Cast the number to MONEY in your query. Then you get a comma instead of a dot. Or you change the separators in Excel.
What is the downside of CAST to MONEY for Excel?
You lose more than 2 digits after the decimal separator. Whether that is a problem depends on your data.
How do I change the separators in Excel?
Go to File, Options, Advanced and change the separators there. That way Excel reads the dot as the decimal separator.
Which data type causes the millions problem when you copy from SQL to Excel?
The example uses a decimal(10,2). The number 42 is then stored in the database with a dot, and after copying, Excel thinks it is 42 million.
Comments
Related blogs
Publish an Excel Pivot online
How to Refresh Reporting Services Cache?
T-TSQL - Create 4 week Moving Total