Read Excel Files in R (readxl)

R Needs a Package to Read Excel Files

Base R reads plain-text formats out of the box, but .xlsx isn't text - it's a zipped bundle of XML, and .xls before it was a binary format. There is no built-in read.xlsx() waiting for you. To open Excel files you install a package, and the standard choice is readxl: it reads both .xlsx and .xls, has no external dependencies (no Java, no Excel installation needed), and does one job well.

install.packages("readxl")   # once per machine
library(readxl)              # once per session

The snippets on this page need readxl installed locally, so they're shown as static code rather than runnable blocks - run them in your own R session. If packages are new to you, the short version: install.packages() downloads it once, library() loads it each time you start R.

read_excel(): the Basics

One call reads the first sheet of a workbook into R:

library(readxl)

sales <- read_excel("sales.xlsx")
head(sales)
str(sales)

Same habits as any import: str() to check every column's type, head() to eyeball the first rows. If the file isn't found, the cause is almost always the working directory - file.exists("sales.xlsx") tells you in one second, and the fix is the same as for CSV files: use a full path or set the working directory to the file's folder.

What comes back is a tibble - the tidyverse's take on the data frame. For everything you'll do as a beginner it behaves exactly like a data frame (it is one, with extras): $ extracts columns, nrow() counts rows, it flows into dplyr verbs. The visible differences are cosmetic and pleasant - it prints only the first ten rows with column types under the names, instead of dumping everything. If some older function insists on a plain data frame, as.data.frame(sales) converts it.

Picking Sheets, Ranges, and Skipping Junk

Real workbooks are rarely one clean table starting at cell A1. readxl's arguments handle the usual mess.

Which sheets exist? Ask before you read:

excel_sheets("report.xlsx")
# [1] "Summary"  "Q1"  "Q2"  "Q3"  "Raw data"

sheet = picks one, by name or by position:

q3 <- read_excel("report.xlsx", sheet = "Q3")
raw <- read_excel("report.xlsx", sheet = 5)

Prefer the name - sheet = 5 silently reads the wrong sheet the day someone reorders the tabs.

range = reads an exact rectangle, in Excel's own notation. This is the cleanest way to skip logo rows, title rows, and stray notes around the actual table:

budget <- read_excel("budget.xlsx", range = "B4:E20")
budget <- read_excel("budget.xlsx", sheet = "Plan", range = "B4:E20")

skip = and col_names = are the looser alternative when you know how many junk lines sit above the data but not where it ends:

# Data starts after 3 title rows, first real row is the header:
df <- read_excel("export.xlsx", skip = 3)

# No header row at all - supply names yourself:
df <- read_excel("export.xlsx", col_names = c("id", "region", "amount"))

col_names = TRUE (the default) uses the first row as names; FALSE generates ...1, ...2 and treats row one as data.

Writing Excel Needs a Different Package

readxl is read-only by design. When a colleague wants your results as a spreadsheet, the quickest route is writexl - a one-liner with no dependencies:

install.packages("writexl")
writexl::write_xlsx(results, "results.xlsx")

If you need more than raw values - bold headers, frozen panes, cell colors, several formatted sheets in one workbook - that's openxlsx territory:

library(openxlsx)
write.xlsx(list(Summary = summary_df, Detail = detail_df), "report.xlsx")

A named list becomes one sheet per element. openxlsx can also read Excel files, so you'll see it used for both directions in the wild; for plain reading, readxl stays the simpler tool.

The Pragmatic Alternative: Export a CSV

Sometimes the least clever solution wins. If the workbook is a one-off - someone mailed you a spreadsheet and you need the numbers now - open it in Excel, use "Save As" to export the sheet as CSV, and read it with the tool you already know:

df <- read.csv("sales.csv")

No package, no sheet names, no merged cells. The full workflow is in read CSV. The trade-offs: you lose the other sheets, you flatten formatting-based information (cell colors that "mean" something - a data-design smell anyway), and the export is a manual step someone will forget when the source file updates. For a repeating pipeline, read the .xlsx directly; for a one-time import, CSV is honestly fine.

The Classic Excel Import Traps

Excel files carry problems CSVs don't, because spreadsheets let humans do things tables shouldn't.

Dates arrive as numbers. Excel stores dates as a serial day count, and if a column mixes types or was formatted oddly, you can end up with 44688 where you expected a date. readxl usually converts real date cells correctly, but when you do get the raw number, convert with Excel's epoch - which is 1899-12-30, not 1970:

as.Date(44688, origin = "1899-12-30")
# [1] "2022-05-07"

Merged cells unmerge into blanks. A header merged across three columns comes back as one value and two empty cells; a category label merged down ten rows becomes one value and nine missing ones. readxl can't recover intent that only existed visually - expect to fill those gaps yourself after import.

One stray cell turns a column into text. Column types are guessed from the data, so a single "n/a", a note typed into the numbers, or a space in an "empty" cell makes the whole column come back as character. str() right after reading catches this; the col_types argument (e.g. col_types = c("text", "numeric", "date")) forces the matter when guessing keeps getting it wrong.

The theme: a spreadsheet is a canvas people draw on, not a table. Read it with your skeptic hat on, run str(), and verify a few values against the original before trusting the import.

What You Take Away

• Base R can't read .xlsx - install readxl, then read_excel("file.xlsx").
• excel_sheets() lists what's in a workbook; sheet = (prefer names) picks one; range = "B4:E20" cuts out exactly the table you want.
• You get a tibble back - a data frame with nicer printing.
• Writing goes through a different package: writexl::write_xlsx() for plain output, openxlsx for formatted workbooks.
• For one-offs, exporting a CSV from Excel and using read.csv() is a perfectly respectable shortcut.
• Watch for the three classics: dates as serial numbers (origin 1899-12-30), merged cells becoming blanks, and one bad cell dragging a column to text.

Next up: the other direction - writing your data frames out to CSV and RDS files.