Read xlsx & xls Excel File in R (6 Examples)

This tutorial explains how to read Excel files in R. More precisely, I will show you how to:

• Read an xlsx File with the read.xlsx Function of the xlsx Package (Example 1)
• Read an xlsx File with the read_excel Function of the readxl Package (Example 2)
• Read an xlsx File with the read.xlsx Function of the openxlsx Package (Example 3)
• Read a Specific Sheet of an Excel File (Example 4)
• Read Multiple Excel Files (Example 5)
• Read an xls File (Example 6)

So without further ado, let’s move on to the examples!

Example 1: Read xlsx File with read.xlsx Function (xlsx Package)

Before we can start with the examples, we need to create some example data and store it as xlsx Excel file on our computer.

The following R code loads the iris data set to RStudio:

data("iris")                                                     # Load iris data to RStudio
head(iris)                                                       # Print first 6 rows of iris

In order to write this data as xlsx file to our computer, we need to install and load the xlsx R package:

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

The xlsx package includes the write.xlsx R function, which allows us to write Excel files to a working directory on our computer as follows:

xlsx::write.xlsx(iris,                                           # Write example Excel xlsx file
           "C:/ ... Your Path ... /iris.xlsx",
           row.names = FALSE)

After running the previous R syntax, you should find an xlsx file at the path that you have specified within the write.xlsx function. When you open the xlsx table it should look as follows:

Figure 1: Iris Data Set Exported as xlsx Excel File.

Finally, we are set up to read an xlsx Excel file to R!

The xlsx package, which we have just used to write an xlsx file to our PC, also provides the read.xlsx R function. We can use the function to load our Excel file to R as follows:

data1 <- xlsx::read.xlsx("C:/ ... Your Path ... /iris.xlsx",     # Read xlsx file with read.xlsx
                   sheetIndex = 1)

Call the data object data1 in your RStudio. You will see that it contains the iris data set that we have exported above.

In my personal opinion, the xlsx package is the package to go if you have to deal with xlsx files in R. However, there are several alternatives available and depending on your specific situation, you might prefer one of the other solutions.

In Examples 2 and 3 I’m going to explain two alternative packages for importing xlsx files (Tidyverse included). Keep on reading!

Example 2: Read xlsx File with read_excel Function (readxl Package)

The most popular alternative to the xlsx package (shown in Example 1) is the readxl package. The readxl package is part of the Tidyverse and therefore highly compatible with Tidyverse’s family of R packages (e.g. ggplot2 or dplyr).

First, we need to install and load the readxl package to R:

install.packages("readxl")                                       # Install readxl R package
library("readxl")                                                # Load readxl R package to RStudio

Now, we can use the read_excel function to load our example xlsx table into R:

data2 <- readxl::read_excel("C:/ ... Your Path ... /iris.xlsx")  # Read xlsx file with read_excel

If you print the data object data2 to your RStudio console, you will see the following output:

Figure 2: Screenshot of Tibble in R.

As you can see, the read_excel command returns a tibble instead of a data frame (as read.xlsx did in Example 1).

Tibbles are the typical data representation of the Tidyverse and there have been hundreds of discussions whether tibbles are preferable to data frames.

However, in case you are working a lot with the dplyr or other packages of the Tidyverse environment, you might prefer read_excel instead of read.xlsx. In the end, it’s a matter of taste!

Example 3: Read xlsx File with read.xlsx Function (openxlsx Package)

I’m showing you the third example due to two reasons:

1. The openxlsx package is also fairly popular among R users.
2. The openxlsx and the xlsx package often lead to confusion, since both packages contain a function that is called read.xlsx.

Let’s have a look at the R syntax!

First, we need to install and load the openxlsx package in R…

install.packages("openxlsx")                                     # Install openxlsx R package
library("openxlsx")                                              # Load openxlsx R package to R

…and then we can apply the read.xlsx function as follows:

data3 <- openxlsx::read.xlsx("C:/ ... Your Path ... /iris.xlsx") # Read with read.xlsx of openxlsx

Note: I had to specify explicitly that I want to use the openxlsx package (by typing openxlsx::) instead of the xlsx package, since both packages contain a read.xlsx function.

The basic R output of both functions is the same. However, openxlsx and xlsx provide different additional specifications for the read.xlsx functions.

There are even more packages and functions that enable the import of Excel files to R (e.g. gdata, XLConnect, RODBC, xlsReadWrite [not on CRAN anymore], or the read.table function which is available in base R).

However, I think we covered the most important ones and for that reason I’m now going to move on to some more complex situations.

Let’s dive in!

Example 4: Read Specific Sheet of Excel File in R

Excel files often contain several worksheets in one file. This can lead to problems, since each data matrix is usually stored in a single data object in R.

Fortunately, we are able to load only one specific worksheet from an Excel file – And that’s what I’m going to show you in this example.

Let’s first extent our previously created example file (i.e. the iris data set) with another sheet. The following R code loads the mtcars data set to R:

data("mtcars")                                                   # Load mtcars data to RStudio
head(mtcars)                                                     # Print first 6 rows of mtcars

Now we can append this data set to our already existing Excel file as follows:

xlsx::write.xlsx(mtcars,                                         # Write second sheet to Excel file
                 "C:/ ... Your Path ... /iris.xlsx",
                 row.names = FALSE,
                 append = TRUE,                                  # Specify that data gets appended
                 sheetName = "mtcars")

If you have a look at our Excel file, you will see that the file now contains a second sheet:

Figure 3: Excel File with Two Sheets.

Now let’s assume that we only want to import the second sheet (i.e. the mtcars data frame) into R. Then we can simply specify that with the sheetName option of the read.xlsx function of the xlsx package:

data_sh2 <- xlsx::read.xlsx("C:/ ... Your Path ... /iris.xlsx",  # Read second sheet of Excel file
                            sheetName = "mtcars")

If you now have a look at data_sh2, you will se that it contains the mtcars data set.

Example 5: Import Multiple Excel Files to R

Reading every Excel file on your computer one by one might be time consuming, depending on the amount of Excel files you want to import. But no worries, R allows to load multiple Excel files at the same time.

First, let’s create a second Excel file in our currently used working directory:

xlsx::write.xlsx(mtcars,                                         # Write mtcars as separate Excel file
                 "C:/ ... Your Path ... /mtcars.xlsx",
                 row.names = FALSE)

If we want to read both our example files (i.e. iris.xlsx and mtcars.xlsx), we first need to extract the names of all files. Of course we could do that manually, but using the list.files function as in the following R code is much quicker:

all_file_names <- list.files(pattern = "*.xlsx")                 # Grab list of all xlsx files
all_file_names                                                   # Print list of xlsx files
# "iris.xlsx"   "mtcars.xlsx"

The vector all_file_names includes all xlsx file names as character string.

Now we can combine lapply and the read_excel function of the readxl package to store the content of all Excel files in a list:

data_list <- lapply(all_file_names, read_excel)                  # Read multiple excel files
data_list                                                        # Print list of data.frames

Note that all data were stored as tibbles, since we are using the readxl package:

Figure 4: Importing Multiple Excel Files to R.

Example 6: Read xls File in R

Old but gold?! To be honest, I’m not really using the xls file format anymore.

However, if you have some xls files that you need to read into R, you might be asking yourself how you could do this. For that reason, I’m going to show you in this example how to read an xls file to R.

First; let’s create an example xls file on our PC. Similarly as in the previous examples, we can use the write.xlsx function of the xlsx package. We simply need to change the file extension from xlsx to xls:

xlsx::write.xlsx(iris,                                           # Write xls Excel file
                 "C:/ ... Your Path ... /iris.xls",
                 row.names = FALSE)

Now we can load this xls file to R as follows:

data_xls <- xlsx::read.xlsx("C:/ ... Your Path ... /iris.xls",   # Read xls Excel file
                            sheetIndex = 1)

Exactly the same as in Example 1, just with xls instead of xlsx.

Tutorial Video & Further Resources for the Handling of Excel Files

Still have problems on this topic? Check out the video below where I explain the steps of this article more detailed:

At this point, I have shown you basically all I know about reading Excel tables into R. However, it wouldn’t be the R programming language if there would not be more interesting stuff to learn.

If you want to deepen your knowledge about the handling of Excel files from within R, I can recommend the following YouTube video of the MarinStatsLectures – R Programming & Statistics channel. The video explains how to deal with Excel files in R in a more general manner, also including csv and txt files.

Furthermore, you might also want to have a look at the other R tutorials of this website. I am publishing new articles on a regular basis:

• How to Export Data from R to Excel
• Save & Load RData Workspace Files
• List of Useful R Functions
• The R Programming Language

I hope I was able to teach you how to read Excel files into R. However, in case you have any further questions, don’t hesitate to let me know in the comments!