R: How to Export Data Frames to Multiple Excel Sheets


You can use the following basic syntax to export multiple data frames in R to multiple worksheets in Excel:

library(openxlsx)

dataset_names <- list('Sheet1' = df1, 'Sheet2' = df2, 'Sheet3' = df3)
write.xlsx(dataset_names, file = 'mydata.xlsx')

The following example shows how to use this syntax in practice.

Example: Export Multiple Data Frames to Multiple Excel Sheets

Suppose we have the following three data frames in R:

#define data frames
df1 = data.frame(playerID=c(1, 2, 3, 4),
                 team=c('A', 'B', 'B', 'C'))

df2 = data.frame(playerID=c(1, 2, 3, 4),
                 rebounds=c(7, 8, 8, 14))

df3 = data.frame(playerID=c(1, 2, 3, 4),
                 points=c(19, 22, 25, 29))

We can use the following syntax to export all three of these data frames to separate sheets within the same Excel file:

library(openxlsx)

#define sheet names for each data frame
dataset_names <- list('Sheet1' = df1, 'Sheet2' = df2, 'Sheet3' = df3)

#export each data frame to separate sheets in same Excel file
openxlsx::write.xlsx(dataset_names, file = 'mydata.xlsx') 

Once I navigate to the location on my computer where the Excel sheet was exported, I can view each of the data frames in their own sheets within the same Excel file called mydata.xlsx:

Sheet1:

Sheet2:

Sheet3:

Note that we exported three data frames to three separate sheets in Excel in this example, but we can use the same syntax to export any number of data frames we’d like.

Additional Resources

The following tutorials explain how to export other file types in R:

How to Export a Data Frame to an Excel File in R
How to Export a Data Frame to a CSV File in R
How to Export Multiple Plots to PDF File in R

Leave a Reply

Your email address will not be published.