# 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 |

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 |

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) |

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) |

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 |

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 |

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:

- The openxlsx package is also fairly popular among R users.
- 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 |

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 |

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 |

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") |

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") |

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) |

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 cause 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" |

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 |

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) |

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) |

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.

## Further Resources for the Handling of Excel Files

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!

### Subscribe to my free statistics newsletter:

### R Tutorials

abs Function in R

all & any R Functions

Set Aspect Ratio of Plot

attach & detach R Functions

attr, attributes & structure in R

cbind R Command

Change ggplot2 Legend Title

Character to Numeric in R

Check if Object is Defined

col & row sums, means & medians

Complete Cases in R

Concatenate Vector of Strings

Convert Date to Weekday

cumsum R Function

Data Frame Column to Numeric

diff Command in R

difftime R Function

dim Function in R

dir R Function

Disable Scientific Notation

Draw Segments in R

droplevels R Example

Evaluate an Expression

Extract Characters from String

Factor to Numeric in R

Format Decimal Places

get, get0 & mget in R

is.na R Function

is.null Function in R

jitter R Function

Join Data with dplyr Package

length Function in R

lowess R Smoothing Function

max and min Functions in R

NA Omit in R

nchar R Function

ncol Function in R

nrow Function in R

outer Function in R

pairs & ggpairs Plot

parse, deparse & R expression

paste & paste0 Functions in R

pmax and pmin R Functions

polygon Plots in R

pretty R Function

R Find Missing Values

R Functions List (+ Examples)

R NA – Values

R Replace NA with 0

rbind & rbind.fill in R

Read Excel Files in R

readLines, n.readLines & readline

Remove Element from List

Remove Legend in ggplot2

Rename Column Name in R

Replace Last Comma of String

rev R Command

Round Numeric Data in R

Save & Load RData Workspace

scan R Function

setdiff R Function

setNames vs. setnames in R

sink Command in R

Sort, Order & Rank Data in R

sprintf Function in R

Square Root in R

str_c Function of stringr Package

str_sub Function of stringr Package

strptime & strftime Functions

substr & substring R Commands

sweep R Function

Transform Data Frames

union Function in R

unlist in R

weekdays, months, quarters & julian in R

with & within R Functions

Write Excel File in R

## 2 Comments. Leave new

Hi Joachim,

Thanks for the above description to read excel files.

How can I merge multiple excel files and make them into a single excel file?

Thanks,

Nara

Hi Nara,

Thanks for your comment!

If you want to merge multiple Excel files, you would have to do 2 steps:

1) Read all Excel files into R with one of the functions shown in this tutorial.

2) Write the imported Excel files to a new Excel file with multiple sheets. You can find an instruction on creating multiple sheets in an Excel file in Example 3 of this tutorial: https://statisticsglobe.com/write-xlsx-xls-export-data-from-r-to-excel-file

Let me know if it worked or if you need further assistance.

Regards,

Joachim