Importing Multiple Excel Worksheets into R

readxl is an R package which makes it easy to get tabular data out of excel. It supports both .xls and .xlsx formats.

Using excel_sheets() to check the list of worksheets your Excel file has.

#install and load readxl package
install.packages('readxl')
library(readxl)
#check the no. of worksheets your Excel Workbook has
excel_sheets("customerdata.xlsx")

[1] "transactions" "masterlookup"

customerdata is an Excel file, saved in working directory. If your data is not saved in working directory then specify the path for it.
customerdata has two worksheets.

Let us see how to import specific worksheet using read_excel().
read_excel() is used to import Excel file into R.
From the list generated by excel_sheets() above you can choose which worksheet you want to import.

#import a specific worksheet either by referencing the sheet's name or its index (number) 
master<-read_excel("customerdata.xlsx", sheet=2)

master<-read_excel("customerdata.xlsx", sheet="masterlookup")
trans<-read_excel("customerdata.xlsx", sheet="transactions")

If sheet= argument is not specified, by default read_excel() will import the first sheet of the Excel file.

#master lookup - sheet2
head(master)

# A tibble: 6 × 3
  CustomerID Region   State
       <dbl>  <chr>   <chr>
1      10004   East Manipur
2      10008   East   Assam
3      10011   East Tripura
4      10012   East Manipur
5      10017   East   Assam
6      10021   East Tripura

#transaction data - sheet1
head(trans)

## # A tibble: 6 × 6
##   CustomerID       Date Month  Year Brand Sales
##        <dbl>      <chr> <dbl> <dbl> <chr> <dbl>
## 1      33871  1/16/2014     1  2014    B7   622
## 2      88219 10/17/2013    10  2013    B3   512
## 3      83619  8/27/2014     8  2014    B2   638
## 4      17907      41645     6  2014    B2  2995
## 5      22028      41528    11  2013    B7   659
## 6      81165      41798     8  2014    B2  1048