Read All Worksheets of Excel File into List in R (Example)

In this tutorial, I’ll illustrate how to import all sheets of an Excel file to a list in R.

The tutorial will consist of this:

2) Example: Importing All Sheets of Excel File Using readxl Package

Here’s how to do it…

Example Data & Packages

As the first step, we have to construct an example Excel file with multiple worksheets. For this, we have to install and load the xlsx package:

install.packages("xlsx")                               # Install xlsx package
library("xlsx")                                        # Load xlsx package

Next, we can create an Excel file with three sheets using the following R syntax:

write.xlsx2(iris, "data_all.xlsx", row.names = FALSE)  # Create Excel file with multiple sheets
# [1] "Sheet1" "Sheet2" "Sheet3"
write.xlsx2(mtcars, "data_all.xlsx", row.names = FALSE, append = TRUE, sheetName = "Sheet2")
write.xlsx2(beaver1, "data_all.xlsx", row.names = FALSE, append = TRUE, sheetName = "Sheet3")

excel file multiple sheets

Have a look at the previous screenshot. It shows that the example Excel file has three different worksheets.

Example: Importing All Sheets of Excel File Using readxl Package

The following R syntax explains how to import all sheets of our Excel file into a list in R.

First, we have to install and load the readxl package:

install.packages("readxl")                             # Install & load readxl package
library("readxl")

Next, we can use the excel_sheets function to create a vector containing all sheet names as character strings:

sheet_names <- excel_sheets("data_all.xlsx")           # Get sheet names
sheet_names                                            # Print sheet names
# [1] "Sheet1" "Sheet2" "Sheet3"

As you can see based on the previous output of the RStudio console, our example file has three worksheets named Sheet1, Sheet2, and Sheet3.

In the next step, we can use the lapply, as.data.frame, and read_excel functions to import our data to R:

list_all <- lapply(sheet_names, function(x) {          # Read all sheets to list
  as.data.frame(read_excel("data_all.xlsx", sheet = x)) } )

The previous R code has created a new list object called list_all, which contains all worksheets of our excel file converted as data frame objects.

It makes sense to rename the list elements of our list by the sheet names of our Excel file:

names(list_all) <- sheet_names                         # Rename list elements

That’s it! Now, we can have a look at each of the elements of our list.

The first six rows of our Sheet1 data look as follows…

head(list_all$Sheet1)                                  # Print head of first sheet

table 1 data frame read all worksheets excel file list r

…the head of the second sheet looks as follows…

head(list_all$Sheet2)                                  # Print head of second sheet

table 2 data frame read all worksheets excel file list r

…and the third sheet has the following data structure:

head(list_all$Sheet3)                                  # Print head of third sheet

table 3 data frame read all worksheets excel file list r

Looks good!

Video, Further Resources & Summary

Some time ago I have published a video on my YouTube channel, which explains the R code of this tutorial. You can find the video below:

The YouTube video will be added soon.

In addition, you may want to read the other tutorials of this website. Some tutorials are listed below.

In this article you have learned how to create a list of data frames based on an Excel file in R programming. Please tell me about it in the comments section, if you have further questions.