Home > Programming > Retaining an Excel cell’s format when using putexcel

## Retaining an Excel cell’s format when using putexcel

In a previous blog entry, I talked about the new Stata 13 command putexcel and how we could use putexcel with a Stata command’s stored results to create tables in an Excel file.

After the entry was posted, a few users pointed out two features they wanted added to putexcel:

1. Retain a cell’s format after writing numeric data to it.
2. Allow putexcel to format a cell.

In Stata 13.1, we added the new option keepcellformat to putexcel. This option retains a cell’s format after writing numeric data to it. keepcellformat is useful for people who want to automate the updating of a report or paper.

To review, the basic syntax of putexcel is as follows:

putexcel excel_cell=(expression) … using filename[, options]


If you are working with matrices, the syntax is

putexcel excel_cell=matrix(expression) … using filename[, options]


In the previous blog post, we exported a simple table created by the correlate command by using the commands below.

. sysuse auto
(1978 Automobile Data)

. correlate foreign mpg
(obs=74)

|  foreign      mpg
-------------+------------------
foreign |   1.0000
mpg |   0.3934   1.0000

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


These commands created the file corr.xlsx, which contained the table below in the first worksheet.

As you can see, this table is not formatted. So, I formatted the table by hand in Excel so that the correlations were rounded to two digits and the column and row headers were bold with a blue background.

putexcel‘s default behavior is to remove the formatting of cells. Thus, if we want to change the correlated variables in our command from foreign and mpg to foreign and weight using the below commands, the new correlations shown in Excel will revert to the default format:

. sysuse auto, clear
(1978 Automobile Data)

. correlate foreign weight
(obs=74)

|  foreign   weight
-------------+------------------
foreign |   1.0000
weight |  -0.5928   1.0000

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


As of Stata 13.1, you can now use the keepcellformat option to preserve a numeric cell’s format when writing to it. For example, the command

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


will produce

Let’s look at a real-world problem and really see how the keepcellformat option can help us. Suppose we need to export the following tabulate table to a report we wrote in Word.

. webuse auto2, clear
(1978 Automobile Data)

. label variable rep78 "Repair Record"

. tabulate rep78

Repair |
Record |      Freq.     Percent        Cum.
------------+-----------------------------------
Poor |          2        2.90        2.90
Fair |          8       11.59       14.49
Average |         30       43.48       57.97
Good |         18       26.09       84.06
Excellent |         11       15.94      100.00
------------+-----------------------------------
Total |         69      100.00


In the previous putexcel blog post, I mentioned my user-written command tab2xl, which exports a one-way tabulation to an Excel file. I have since updated the command so that it uses the new keepcellformat option to preserve cell formatting. You can download the updated tab2xl command by typing the following:

. net install http://www.stata.com/users/kcrow/tab2xl, replace


Using this command, I can now export my tabulate table to Excel by typing

. tab2xl rep78 using tables, row(1) col(1)


Once the table is in Excel, I format it by hand so that it looks like this:

I then link this Excel table to a Word document. When you link an Excel table to a Word document, it

1. preserves the formatting of the table and
2. automatically updates the Word document when you update the Excel table.

It is fairly easy to link an Excel table to a Word document or PowerPoint presentation. In Excel/Word 2010, you would do as follows:

1. Highlight the table/data in Excel.
2. On the Home tab, click on the Copy button.
3. Open the Word document and scroll to where you want the table pasted.
4. On the Home tab of Word, click on the Paste button.
5. Select Link & Keep Source Formatting, , from the Paste icon menu.

My report now looks like this:

With the Excel table linked into Word, any time we update our Excel table using putexcel, we also update our table in Word.

Suppose that after a few weeks, we get more repair record data. We now need to update our report, and our new tabulate table looks like this:

. tabulate rep78

Repair |
Record |      Freq.     Percent        Cum.
------------+-----------------------------------
Poor |          4        2.90        2.90
Fair |          8        5.80        8.70
Average |         60       43.48       52.17
Good |         44       31.88       84.06
Excellent |         22       15.94      100.00
------------+-----------------------------------
Total |        138      100.00


To update the report, we simply need to reissue the putexcel command after tabulate.

. tabulate rep78
. tab2xl rep78 using tables, row(1) col(1)


The linked Word report will automatically reflect the changes:

Categories: Programming Tags:
• Meezer

Thanks for this terrific command as well as this enhancement to maintain formatting. After using putexcel for the last couple months, I realized that another great enhancement would be to be able to write to a named range (e.g., MyRange, where the MyRange is defined as A3:C7) as well as a plain old cell reference (e.g., A3). This is because inserting rows can make the regular cell reference wrong, but doesn’t change the cell that a named range refers to. Any plans to add this feature? Any other users see the value here? Thanks.

• Kevin

Can you email me at tech-support@stata.com
and give me a little more detail about exactly what you wish putexcel could do.

• Kevin

Can you email me at tech-support@stata.com
and give me a little more detail about exactly what you wish putexcel could do.

• Billy Buchanan

Does this work for conditional formatting in MS Excel files as well? And if we wanted to populate an MS Excel file in a sheet that is hidden and/or locked would that be possible? For example, if we need to send files in MS Excel to non-analysts/programmers to validate their data and want to prevent them from altering a specified set of columns/rows, could we use -putexcel- to populate those cells?

• Kevin

You can use putexcel to populate the data, but not to control cell formatting (hiding/locking/conditional formatting).

• ahaynes

This looks like a very handy command…with one drawback – it doesnt seem to work on data. I construct results tables in Stata to the side of my actual data, then use export excel…, sheetreplace. In excel I then link a second sheet and format that one. Is there an equivalent to putexcel that works with dataframes to retain formating?

• Susan Morpeth

Is it possible to use putexcel for tabulate twoway? I can’t seem to figure out the correct syntax from looking at tabulate oneway examples.

• Dona

I also want to know if it is possible to use putexcel for tabluate twoway with row frequency. I especially want to save the row frequency.

Can you help? Thank you.

• Kevin Crow

Can you send an example into tech-support@stata.com

• Pingback: jesse()

• Pingback: max()

• Kevin

There currently isn’t an easy way to export a twoway tabulation table. I will try and write a program to do this soon. You can get the row frequencies using the following code.

 . tabulate for rep, matcell(a) | Repair Record 1978 Car type | 1 2 3 4 5 | Total -----------+-------------------------------------------------------+---------- Domestic | 2 8 27 9 2 | 48 Foreign | 0 0 3 9 9 | 21 -----------+-------------------------------------------------------+---------- Total | 2 8 30 18 11 | 69 . matrix list a a[2,5] c1 c2 c3 c4 c5 r1 2 8 27 9 2 r2 0 0 3 9 9 . putexcel B2=matrix(a) using test.xlsx file test.xlsx saved 

• Dong lian

It doesn’t work when the variable is a string.Can you help?Thank you.

• Dona

Thank you for the code above!
I look for forward to seeing an easy way to export a twoway tabulation too.

• Digi

Hi Kevin,

I am trying to export a table which has 8 rows and 13 columns generated from tab var1 var2 command.

Can putexcel or tab2xl command be used for this purpose?

Best regards,
Digi

• Digi

A bit more deetail: Both var1 and var2 are categorical variables.

Also, where does the stata store the excel file created by using putexcel syntax? I tried by creating an excel file in the desktop and giving the same name in syntax. Syntax seemed to have worked as it says “file xyz.xlsx saved” however I can’t find the output in my excel file that I created and there is not any other file of that name in my computer.

I will appreciate your help. Thanks.
Digi

• Kevin Crow

I plan on writing or expanding tab2xl to handle this case. Write now, there is not an easy way to do this

• Severin

This would be most interesting. Do you have an update on that matter?

• Kevin Crow

Hopefully in the next few weeks I can find the time to write a twoway tabulate to excel command.

• Cristián Labra

it would be appreciated.

• Elizabeth_inMKE

That would be fantastic – especially if there was a way to export row and/or column percentages as well. Thanks for this!

• Scott

It would be helpful if it would maintain cell format when there are missing values in matrix. Currently cells in a matrix with missing values remove formatting of a cell like borders and shading.

• Kevin Crow

You are correct. tab2xl does not work with string variables. I will have to update the command to handle this.

• Leonardo Goes Shibata

I wrote an alpha version of a program that can export two-way tabulations to excel