SQL SERVER – Excel Losing Decimal Values When Value Pasted from SSMS ResultSet

I often get questions that how to fix the issue where excel loses decimal values when values are pasted from SSMS Resultset.

Well the answer is simple –

It is not a SQL Server Issue or SSMS issue. It is how things work. There is a simple trick to resolve this issue.

It is very common when users are coping the resultset to Excel, the floating point or decimals are missed.

SQL SERVER - Excel Losing Decimal Values When Value Pasted from SSMS ResultSet exceldec

The solution is very much simple and it requires a small adjustment in the Excel. By default Excel is very smart and when it detects the value which is getting pasted is numeric it changes the column format to accommodate that. Now, as Zero which are training any digit after decimal points have no value, Excel automatically hides it. To prevent this to happen user has to convert columns to text format so it can preserve the formatting.

Here is how you can do it.

Select the corner between A and 1 and Right Click on it.

SQL SERVER - Excel Losing Decimal Values When Value Pasted from SSMS ResultSet exceldec1

It will select complete spreadsheet. If you want to change the format of any column you can select an individual column the same way. In the menu Click on Format Cells…

SQL SERVER - Excel Losing Decimal Values When Value Pasted from SSMS ResultSet exceldec2

It will bring up the following menu. Here by default the selected column will be General, change that to Text. It will change the format of all the cells to Text.

SQL SERVER - Excel Losing Decimal Values When Value Pasted from SSMS ResultSet exceldec3

Now once again paste the values from SSMS to the Excel. This time it will preserve the decimal values from SSMS.

SQL SERVER - Excel Losing Decimal Values When Value Pasted from SSMS ResultSet exceldec4

Solved!

Any other trick you do you know to preserve the decimal values? Leave a comment please.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , ,
Previous Post
SQLAuthority News – Download SQL Server 2012 SP1 CTP4
Next Post
SQL SERVER – Basic Calculation and PEMDAS Order of Operation

Related Posts

9 Comments. Leave new

  • manseta (@manseta)
    September 24, 2012 9:32 am

    how about preserving leading zero? may be same way as you mentioned in your article but I wanted to have it for only one column? Any easy idea?

    Reply
  • 1. Right click on cell
    2. select Format cell
    3. select Number
    4. Increase/Decrease Decimal Places according to your need.

    Reply
  • I’m not sure why you’d want to do that? Decimal places are missing intentionally because Excel is simply representing the number, not the textual layout.
    In the solution you’ve given, they’re now text and you can’t sum them up or perform operations on them. If you genuinely only want the exact same representation then fine, otherwise you’re better off correctly formatting them as a custom format using something like 0.00000?
    I find the most common time this causes a problem is with things like order numbers which have leading zeros in which cause using the text formatting before pasting is acceptable because I don’t generally ever have to perform numeric operations on order numbers.

    Reply
    • Great point.

      Quite often people want to do this for display purpose, if they want to add the value or do any mathematical operation then you have to either do this in SQL Server or convert them to INT format in Excel.

      Reply
  • Worked great. Thanks!

    Reply
  • Thank you for this one Pinal. Encountered the issue when dealing with phone numbers without the international prefix.

    Reply
  • andreirantsevich
    June 5, 2013 7:15 am

    Hi Dave, in my SSMSBoost add-in I have implemented “Results Grid scripter”, that exports Results Grid as Open XML Spreadsheet. Benefits:
    -datatypes remain preserved. Numbers remain numbers, text remains text (etc). Excel does not try to convert anything on it’s own
    -you can export selection, one or even ALL ResultGrids, if you have several. In this case each Results Grid is exported as own Worksheet in same Excel document…

    Reply
  • This solution among many others is not working for me, instead I upload data as text after I multiply to get rid of commas into decimal table column and then I update table and divide to cancel previous number

    Reply

Leave a Reply

Menu