How to go from R to nice tables in Microsoft Word

A step-by-step tutorial on exporting tables from R

As scientists, we often have data or results in R that we want to export to Microsoft Word for the reports or publications that we’re writing.

In this tutorial I show you how to do just that. You can also watch this tutorial as a video if you want to follow along while I code:

“Video thumbnail of tutorial on exporting a dataframe from R into a table in MS Word”

The first step is to load up some data. We’re going to use the Orange dataset that comes built into R, which describes the growth of orange trees:

# Load the data
data(Orange)

If we view the data, we can see the following columns: “Tree”, which contains an identifier for each tree that was measured; “age”, which contains the age (in days) of the tree at the time of measurement; and “circumference”, which is the circumference of the tree trunk, measured in millimeters.

head(Orange, 15)
##    Tree  age circumference
## 1     1  118            30
## 2     1  484            58
## 3     1  664            87
## 4     1 1004           115
## 5     1 1231           120
## 6     1 1372           142
## 7     1 1582           145
## 8     2  118            33
## 9     2  484            69
## 10    2  664           111
## 11    2 1004           156
## 12    2 1231           172
## 13    2 1372           203
## 14    2 1582           203
## 15    3  118            30

So in this dataset, there are five different trees, each of which have been measured at the same time points (age).

Let’s say we want to summarize this dataset to see how the different age groups compare in their growth. In the script below I’ve organized the data so that now we have a table called Orange_summ, which shows the mean and standard deviation of the tree circumferences for each age group. (To run the code below, just make sure that the 'dplyr' package is installed if not already):

# install.packages("dplyr")
library(dplyr)
Orange_summ <- group_by(Orange, "days"=age) %>% 
  summarize(mean_circ_mm = mean(circumference), sd_circ_mm = round(sd(circumference), 2))

Orange_summ
## # A tibble: 7 x 3
##    days mean_circ_mm sd_circ_mm
##   <dbl>        <dbl>      <dbl>
## 1   118         31         1.41
## 2   484         57.8       8.17
## 3   664         93.2      17.2 
## 4  1004        134.       25.9 
## 5  1231        146.       29.2 
## 6  1372        173.       32.8 
## 7  1582        176.       33.3
If you’re interested in learning more about how to summarize data like this, check out our full online course, “The Basics of R (for ecologists)” here.

Great! Now we have a summary table that we can export to Word. First, we’re going to save our table as a ‘*.csv’ file.

write.csv(Orange_summ, "Orange_summ.csv", row.names = F)

What’s important to note here is that we set row.names to False—doing this eliminates the row numbers in our .csv file, since we don’t need them.

a screenshot from R Studio showing the contents of the Orange_summ dataframe with the row names circled in red

Next, open the .csv file. You can see below that Microsoft Excel is the default software for opening .csv files, but we don’t want that. We’re going to open the file in TextEdit or a similar text editor by right-clicking on our file and choosing the appropriate app.

a screenshot from the Mac OS Finder application showing how to use “open with” for opening the csv file in a text editor

It should look something like this.

a screenshot of the text editor showing the contents of the .csv file

After opening the .csv file in your text editor app, just copy and paste the text onto a blank Microsoft Word document.

In Word, highlight the text, and then go to Table » Convert » Convert Text to Table…

a screenshot from MS word showing how to go to Table » Convert » Convert Text to Table…

That will open a window where you should check that the number of columns is correct, and make sure you have chosen “Commas” in the “Separate text at” section. That’s because you saved the file as a .csv, or “comma-separated values” file.

If you have a Windows computer, the exact method for converting text to tables might be slightly different, but the concept is the same—you can find a tutorial for that here.
a screenshot from MS word showing the settings for converting text to table

Click “OK” and we have a table!

screenshot from MS Word showing the converted table

Next, use the “Find and Replace” function to clean up the table by going to Edit » Find » Replace. (The Mac keyboard shortcut for this is Shift + Command + H).

screenshot from MS Word showing how to find “find and replace” for cleaning up the table

We want to get rid of all the double quotes in our table, so put double quotes “ in the top bar, and leave the bottom bar blank. Then click “Replace all”. Word should have found 6 replacements. This is definitely something that could have been fixed manually in this case since there are only 6 occurrences, but if your table contains a character or factor column, all the values in that column will end up having double quotes around them, so that’s where this trick comes in handy…

screenshot from MS Word showing how to use Find and Replace

Looking good!

Screenshot from MS Word showing a cleaner table

Now just rename the columns and reformat the table to make it nice and polished. Word has several border editing tools that allow you to change which borders are visible. I like to remove all borders first. Then, by putting your cursor in a table cell, you can go to Table Design » Border Painter, which lets you “paint” in whichever borders you do want to add.

Screenshot from MS Word showing the final formatted and clean table

And that’s it! You’ve just exported your first table from R into Microsoft Word.



If you liked this post and want to learn more, then check out my online course on the complete basics of R for ecology:

Also be sure to check out R-bloggers for other great tutorials on learning R

Related