Export Excel and Decimal Places

[Rebecca Ivester]

Hello-

I am exporting data from Stata using export excel; however, the exported numbers are rounded to the integer. I don't see an option for it, but is there a way to specify number of decimal places I'd like to output? Or maybe this is an Excel issue?

Thanks

[Clyde Schechter]

As far as I know, there is no option for this. And, I agree it's a pain. Since it is possible to control this from within Stata using -putexcel-, I don't see why they don't have a -usedisplayformat- option or something like that on -export excel-. But they don't, or if they do, it's not documented and I don't know about it.

But one thing that is helpful: if you are going to be running this same code multiple times, after the first run, go into Excel and set the decimal places you want. Then on subsequent runs of the code, use the -sheet(sheetname, modify)- option and also specify the -keepcellfmt- option. That will preserve the decimal places (and any other cell formatting you specified in Excel) on subsequent runs.

[Isaac Maddow-Zimet]

Another solution to this is to convert the variables you are exporting to string (formatted to the correct number of decimals) before you export. So the code might look something like:

  tostring varname, gen(varnamestring) force format(%9.2f)

This doesn't rely on whatever Excel formatting is present, and also helps with some other unwanted Excel behavior (see the note on how to store dates in Excel in Woo and Broman, Data Organization in Spreadsheets, published in the American Statistician, for an example of this).

[Suzanna Vidmar]

Just checking if anyone's aware whether there's now a direct way to specify the number of decimal places when exporting to Excel via "export excel". In the meantime, here's my workaround:

foreach var of varlist age gestage weight height bmi zweight zheight zbmi {
    tostring `var', gen(`var'_s) force format(%9.2f)
    destring `var'_s, gen(`var'_sn)
    drop `var' `var'_s
    rename `var'_sn `var'
}

export excel "..\Processed Data\anthro_z.xlsx",firstrow(var) keepcellfmt replace

NOTE: I want the final set of variables to be numeric, not string.

[Clyde Schechter]

As far as I know, -export excel- has not changed in regard to this.

A different workaround is possible, using -export delimited-. That has a -datafmt- option which causes the Stata display format for numeric variables to be retained in the exported file. The default file format for -export delimited- is .csv, which is immediately readable by Excel, and you can re-save it as .xlsx. Of course, this has limitations: you can't do multiple sheets within a .csv file.

[Suzanna Vidmar]

Thanks Clyde for your very quick response. I'll check if people would be happy with CSV files.
Regards,
Suzanna

[Nick Cox]

There is a different issue. tostring followed by destring can't be a good way of rounding numeric variables to 2 decimal places. Consider the decimals { 0, 1, ..., 99 } / 100. Of these only { 0, 25, 50, 75 } / 100 can be held exactly in binary; all others are held with binary approximations. Even 0.01 can't be held exactly in binary. This problem is much discussed here under the heading of precision.

Rounding to 2 decimal places for presentation is a really good idea, but that is not the way to do it.

Sorry, but I am no kind of expert on either MS Excel or any other part of MS Office to be able to suggest what you should be doing. But it's a natural general principle to keep as much detail as you can with numbers and round as a cosmetic operation in the last software you use.

As the putative author of tostring and destring, I can say that this dual operation isn't an intended use.

[Rocky Kairn Kelley]

Documenting that I came to look for this same topic on 6/26/2026. I am exporting data into worksheets within a workbook:
export excel using "workbook_name", sheet("sheet_name") sheetreplace firstrow(variables)

All the data are there (spot checks reveal values up to 19 decimal places), but it's an extra step to manually format the column displays in each sheet.
I appreciate being able to save to specific worksheets more than I'm annoyed by having to format.

[Raymond Guiteras]

You can use set_number_format in mata. Here's an example, naturally use at your own risk and there may be a better way.

mata:

  B = xl()  
  B.load_book("output/data/xlsx/project-villages-key-variables.xlsx")
  sheet = st_local("sheet_name")
  B.set_sheet(sheet)
  nObs = st_nobs()
  lastRow = nObs+2

s = ("share_burned","share_ag_inc_hh_secc","secc_pov_rate_tend_rural","land_own_share","sc_share")
for (i=1; i<=cols(s); i++) {
   B.set_number_format((3,lastRow),st_varindex(s[1,i]),"number_d2")
   B.set_column_width(st_varindex(s[1,i]),st_varindex(s[1,i]),strlen(s[1,i]))
}

s = ("_CX","_CY")
for (i=1; i<=cols(s); i++) {
     B.set_number_format((3,lastRow),st_varindex(s[1,i]),"#.000000")
     B.set_column_width(st_varindex(s[1,i]),st_varindex(s[1,i]),9)
}

B.close_book()

end

PS - this was written in Stata 18.0, I'm not sure if it would work for previous versions.

[Mike Lacy]

I can't find documentation of the use of "set_number_format" anywhere in Stata. Neither search set_number_format, not help mata set_number_format reveals anything in my Stata 19. Can you tell us where you learned about it? Perhaps you have some other module loaded that defines a structure containing WhatEver.set_number_format?

[Nick Cox]

Mike Lacy

I hadn't heard of this before either, but this appears to be the magic key to open the door:

help mf_xl

[Raymond Guiteras]

Yes, I think it was where Nick suggested and/or in the Mata manual (M-5 xl())