R Read Excel Files

Excel files (.xlsx and .xls) are one of the most common data formats in business. R cannot read Excel files with base functions — you need a package. The readxl package is the most popular and reliable choice for reading Excel files into R data frames.

Installing and Loading readxl

install.packages("readxl")   # install once
library(readxl)              # load every session

Reading a Basic Excel File

data <- read_excel("sales_report.xlsx")
print(data)
str(data)

Key Arguments of read_excel()

Argument    Default     Description
────────────────────────────────────────────────────────────────
path        (required)  Path to the Excel file
sheet       1           Sheet name or number to read
range       NULL        Cell range like "A1:D20" or "B2:F50"
col_names   TRUE        First row as column names?
col_types   NULL        Force column types ("text","numeric","date",etc.)
na          ""          Strings to treat as NA
skip        0           Rows to skip before reading
n_max       Inf         Maximum rows to read

Reading a Specific Sheet

# By sheet number
df <- read_excel("report.xlsx", sheet=2)

# By sheet name
df <- read_excel("report.xlsx", sheet="Sales Data")

# List all sheet names first
excel_sheets("report.xlsx")
# "Summary" "Sales Data" "Returns" "Pivot"

Reading a Specific Cell Range

# Read only cells A1 to E20
df <- read_excel("report.xlsx", range="A1:E20")

# Skip the first 3 rows (metadata) and read from row 4
df <- read_excel("report.xlsx", skip=3)

Reading All Sheets at Once

all_sheets <- lapply(
  excel_sheets("report.xlsx"),
  function(s) read_excel("report.xlsx", sheet=s)
)

names(all_sheets) <- excel_sheets("report.xlsx")

# Access individual sheets
all_sheets[["Sales Data"]]

Reading Old .xls Files

# readxl handles both .xlsx and .xls automatically
old_data <- read_excel("legacy_report.xls")

Other Excel Packages

Package     Strength
─────────────────────────────────────────────────────────────────
readxl      Read only; fast; no Java required (recommended)
openxlsx    Read + write xlsx; rich formatting; no Java
writexl     Write only; very fast
xlsx        Read + write; requires Java
XLConnect   Full Excel control; requires Java

Practical: Load and Clean an Excel Report

library(readxl)

# Load the data
sales <- read_excel("monthly_sales.xlsx",
                     sheet  = "Jan2024",
                     skip   = 2,
                     na     = c("", "N/A", "-"))

# Inspect
cat("Rows:", nrow(sales), "| Cols:", ncol(sales), "\n")
head(sales)
colSums(is.na(sales))

# Clean
sales <- na.omit(sales)
sales$Revenue <- as.numeric(sales$Revenue)

# Analyze
total <- sum(sales$Revenue)
cat("Total Revenue: ₹", total, "\n")

Writing Excel Files with openxlsx

library(openxlsx)

write.xlsx(sales, "clean_sales.xlsx", sheetName="Cleaned Data")

# Multiple sheets
wb <- createWorkbook()
addWorksheet(wb, "Sales")
addWorksheet(wb, "Summary")
writeData(wb, "Sales", sales)
writeData(wb, "Summary", data.frame(Total=sum(sales$Revenue)))
saveWorkbook(wb, "report.xlsx", overwrite=TRUE)

The readxl package makes Excel data accessible in R with minimal setup. Most businesses store data in Excel, so this skill bridges the gap between the data source and your R analysis workflow.