# Stata Features: Export tables to Excel

A new feature in Stata 13, designed by Kevin Crow, putexcel, allows you to easily export matrices, expressions and stored results to an Excel file. Combining putexcel with a Stata command’s stored results allows you to create the table displayed in your Stata Results window in an Excel file, as follows.

A stored result is simply a scalar, macro or matrix stored in memory after you run a Stata command. The two main types of stored results are e-class (for estimation commands) and r-class (for general commands). You can list a command’s stored results after it has been run by typing **ereturn list** (for estimation commands) or **return list** (for general commands). Here’s a simple example by loading the auto dataset and running **correlate** on the variables **foreign** and **mpg**:

. sysuse auto (1978 Automobile Data) . correlate foreign mpg (obs=74) | foreign mpg -------------+------------------ foreign | 1.0000 mpg | 0.3934 1.0000

Because **correlate** is not an estimation command, use the **return list** command to see its **stored results**.

. return list scalars: r(N) = 74 r(rho) = .3933974152205484 matrices: r(C) : 2 x 2

Now you can use **putexcel** to export these results to Excel. The basic syntax of **putexcel** is:

putexcelexcel_cell=(expression) … usingfilename[,options]

If you are working with matrices, the syntax is:

putexcelexcel_cell=matrix(expression) … usingfilename[,options]

It’s easy to build the above syntax in the **putexcel** dialog (there’s also helpful YouTube tutorial about the dialog here). List the matrix **r(C)** to show the below:

. matrix list r(C) symmetric r(C)[2,2] foreign mpg foreign 1 mpg .39339742 1

To re-create the table in Excel, you need to export the matrix **r(C)** with the matrix row and column names. The command to type in your Stata Command window is:

putexcel A1=matrix(r(C), names) using corr

Note that to export the matrix row and column names, the example used the **names** option after we specifed the matrix **r(C)**. When corr.xlsx file is opened in Excel, the table below is displayed:

Next let’s try a more involved example. Load the auto dataset, and run a tabulation on the variable **foreign**. Because **tabulate** is not an estimation command, use the **return list** command to see its **stored results**.

. sysuse auto (1978 Automobile Data) . tabulate foreign Car type | Freq. Percent Cum. ------------+----------------------------------- Domestic | 52 70.27 70.27 Foreign | 22 29.73 100.00 ------------+----------------------------------- Total | 74 100.00 . return list scalars: r(N) = 74 r(r) = 2

**tabulate** is different from most commands in Stata in that it does not automatically save all the results we need into the **stored results** (we will use scalar **r(N)**). The **matcell()** and **matrow()** options of **tabulate** are used to save the results produced by the command into two Stata matrices.

. tabulate foreign, matcell(freq) matrow(names) Car type | Freq. Percent Cum. ------------+----------------------------------- Domestic | 52 70.27 70.27 Foreign | 22 29.73 100.00 ------------+----------------------------------- Total | 74 100.00 . matrix list freq freq[2,1] c1 r1 52 r2 22 . matrix list names names[2,1] c1 r1 0 r2 1

The **putexcel** commands used to create a basic tabulation table in Excel column 1 row 1 are:

putexcel A1=("Car type") B1=("Freq.") C1=("Percent") using results, replace putexcel A2=matrix(names) B2=matrix(freq) C2=matrix(freq/r(N)) using results, modify

Below is the table produced in Excel using these commands:

Again this is a basic tabulation table. You probably noticed that the **Cum.** column or the **Total** row in the export table are not displayed. Also the **Car type** column contains the numeric values (0,1), not the value labels (Domestic, Foreign) of the variable **foreign** and that the **Percent** column is not formatted correctly. To get the exact table displayed in the Results window into an Excel file takes a little programming. With a few functions and a **forvalues** loop, users can easily export any table produced by running the **tabulate** command on a numeric variable.

There are two extended macro functions, **label** and **display**, that can help with this. The **label** function can extract the value labels for each variable and the **display** function can correctly format numbers for the numeric columns. Last, you can use **forvalues** to loop over the rows of the returned matrices to produce the final tables.

Back to top |

Return to: Stata Features | Stata | Home |