Export Multiple Data Frames to Different Excel Worksheets in R (3 Examples)

In this tutorial you’ll learn how to write multiple data sets to different Excel sheets in R programming.

The article looks as follows:

Let’s jump right to the examples!

Example Data & Add-On Packages

As a first step, I need to create some example data:

data1 <- data.frame(x = 1:5,                                                                                                      # Create three data frames
                    y = 1:5)
data2 <- data.frame(x1 = 5:1,
                    x2 = 9:5,
                    x3 = 7)
data3 <- data.frame(x = letters[1:5],
                    y = letters[1:5])

The previous R syntax created three different data frames called data1, data2, and data3.

In this tutorial, we’ll use the xlsx package to export our data frames as Excel files. If we want to use the functions of the xlsx package, we need to install and load xlsx:

install.packages("xlsx")                                                                                                          # Install & load xlsx
library("xlsx")

Furthermore, we have to specify the path of our currently used working directory:

my_path <- "C:/Users/Joach/Desktop/my_directory/"                                                                                 # Store directory path

That’s it with the preparation. Let’s move on to the examples!

Example 1: Exporting Multiple Data Frames to Different Sheets of Excel File

The following R programming syntax explains how to apply the write.xlsx2 function of the xlsx package to save multiple data frames to the same Excel file.

For this, we first have to create an xlsx file that contains only one data frame (i.e. data1):

write.xlsx2(data1, paste0(my_path, "data_all.xlsx"), row.names = FALSE, sheetName = "data1")                                      # Write xlsx file

Have a look at the working directory on your computer. It should contain an Excel file consisting of only one sheet.

Now, we can use the append = TRUE argument to add the other data frames to new worksheets. Note that it is important to also specify a different sheet name for each data frame that we want to append by using the sheetName argument:

write.xlsx2(data2, paste0(my_path, "data_all.xlsx"), row.names = FALSE, sheetName = "data2", append = TRUE)                       # Append to second sheet
write.xlsx2(data3, paste0(my_path, "data_all.xlsx"), row.names = FALSE, sheetName = "data3", append = TRUE)                       # Append to third sheet

Have another look at the Excel file in your working directory. After running the previous R code it should contain three different data frame each in a different sheet as shown in the following figure.

Example 2: Exporting Multiple Data Frames to Different Sheets of Excel File (Automatized)

The previous example explained how to append new worksheets to an Excel file when exporting data frames.

However, in case you want to export a large amount of data frames, the R code shown in Example 1 might not be efficient enough, because you would have to add another line of code for each data frame.

This example therefore illustrates how to automatize the process of writing data frames to new sheets by using a for-loop.

First, we have to create a vector containing the names of all data frames that we want to write to our Excel file:

data_names <- c("data1", "data2", "data3")                                                                                        # Vector of data names

Then, we have to write the first data frame to a new Excel file using the write.xlsx2 function as in Example 1:

write.xlsx2(get(data_names[1]), paste0(my_path, "data_all.xlsx"), row.names = FALSE, sheetName = data_names[1])                   # Write xlsx file

Now, we can use a for-loop to append each of the other data frames to new worksheets as shown below:

for(i in 2:length(data_names)) {
  write.xlsx2(get(data_names[i]), paste0(my_path, "data_all.xlsx"), row.names = FALSE, sheetName = data_names[i], append = TRUE)  # Append other data frames
}

Have a look at the folder were your data frames are exported to. It should contain an Excel file consisting of all our data frames.

Example 3: Exporting Multiple Data Frames to Different Sheets of Excel File Using openxlsx Package

Another way to write multiple data frames to different worksheets is provided by the openxlsx package.

First, we have to store all data frames we want to export in a list object:

data_list <- list("data1" = data1,
                  "data2" = data2,
                  "data3" = data3)
data_list
# $data1
#   x y
# 1 1 1
# 2 2 2
# 3 3 3
# 4 4 4
# 5 5 5
# 
# $data2
#   x1 x2 x3
# 1  5  9  7
# 2  4  8  7
# 3  3  7  7
# 4  2  6  7
# 5  1  5  7
# 
# $data3
#   x y
# 1 a a
# 2 b b
# 3 c c
# 4 d d
# 5 e e

Next, we can apply the write.xlsx function of the openxlsx package to export each of these data frames to a different worksheet in a Microsoft Excel file:

openxlsx::write.xlsx(data_list, "data_all2.xlsx")

After executing the previous R code, you should find another XLSX file in your working directory, which contains the same data and sheets as the XLSX file that we have created in Example 2.

Thanks to Vishal Katti for mentioning this alternative code in the comments section below!

Video, Further Resources & Summary

Would you like to know more about exporting data frames from R? Then you may have a look at the following video of my YouTube channel. I illustrate the R syntax of this article in the video:

In addition, you might want to read the other articles on my website. A selection of interesting tutorials about importing and exporting data can be found below.

• Merge Multiple Data Frames in List in R
• Merge Data Frames by Two ID Columns
• Write xlsx & xls in R
• R Programming Language

At this point of the article you should know how to export several data frames to new Excel worksheets in the R programming language. If you have additional questions, don’t hesitate to tell me about it in the comments.