Reading Excel Files in R With readxl Without Surprises

R users meet Excel files constantly: a survey exported by a colleague, a spreadsheet from a statistics office, a lab sheet with merged cells and notes. The readxl package is the standard tool. It has no system dependencies, reads both xls and xlsx and returns tibbles. It is easy to use and easy to use badly, because its defaults guess types from the first rows. This post covers the read arguments that matter, the traps with types, dates and names, and the options for writing and for the information readxl does not read. It is a spoke of reading and writing Excel from JavaScript, Java and C Sharp.

Install and look

install.packages("readxl")
library(readxl)

path <- "survey.xlsx"
excel_sheets(path)
#> [1] "Responses" "Codebook"  "Notes"

excel_sheets lists the sheet names without reading the data. Use it to pick a sheet and to check that the file is what you think. A sheet can then be read by name or by number:

resp <- read_excel(path, sheet = "Responses")
resp
#> # A tibble: 3 x 4
#>   id    date                score comment
#>   <chr> <dttm>              <dbl> <chr>

The result is a tibble. The column types in the header line show what readxl decided: chr for text, dbl for numbers, dttm for dates and times, lgl for logical.

Control the read

The arguments that do most of the work:

A read that states its intent:

resp <- read_excel(
  path, sheet = "Responses", range = "A4:D500",
  col_types = c("text", "date", "numeric", "text"),
  na = c("", "n/a", "-")
)

range is the most dependable way to skip a title block, because it names the cells. skip = 3 also works but breaks if the producer adds a line. Because range includes the header row, A4:D500 takes row 4 as names.

Column types: do not let it guess

By default readxl looks at the first 1000 rows (guess_max = min(1000, n_max)) and picks a type per column. If the first 1000 rows of an amount column are numbers and row 1,200 holds "n/a", the column is read as numeric and the text becomes NA with a warning. If the first rows are numbers but the column is an identifier like 00123, you get 123 and lose the zeros. Set the types:

resp <- read_excel(path, col_types = "text")      # everything as text, then convert on purpose
resp$score <- suppressWarnings(as.numeric(resp$score))
sum(is.na(resp$score) & !is.na(read_excel(path, col_types = "text")$score))   # failures

Reading as text first, then converting, makes every conversion a visible step and lets you count the failures. The types readxl accepts are "skip", "guess", "logical", "numeric", "date", "text" and "list". A "list" column keeps the original types of mixed cells as list elements, useful for inspecting a messy column (detecting mixed types in an Excel column).

Raise guess_max (to Inf for small files) if you must guess. Warnings such as "Expecting numeric in D1200 / got a date" tell you the guess was wrong; read them.

Dates

Excel dates are numbers with a date format. readxl recognizes date-formatted cells and returns POSIXct datetimes in UTC, even if you only need the date. Convert:

resp$date <- as.Date(resp$date)

A column of date strings that were typed as text stays character; parse it:

resp$date2 <- as.Date(resp$date_text, format = "%d/%m/%Y")     # state the format

Do not rely on locale guesses for ambiguous formats like 03/04/2025. A date stored as a plain number because the cell lacked a date format arrives as a number: convert with as.Date(x, origin = "1899-12-30"). The 1899-12-30 origin matches Excel's date system on Windows and modern Mac files; very old Mac files use 1904 (Mac and Windows date systems in Excel). A mixed column, with some real dates and some text, comes out as text with the dates as serial-number strings; clean it in R.

Names, blanks and merged cells

Empty header cells become ...1, ...2 and similar names, and duplicates are made unique with a suffix. Repair names on purpose:

resp <- read_excel(path, .name_repair = janitor::make_clean_names)

(That uses the janitor package to produce lower-case names with underscores; .name_repair = "universal" is built in.) Merged cells read as a value in the first cell and NA below; fill down with tidyr::fill():

library(tidyr)
resp <- fill(resp, region)

Fill only columns where blank means "same as above" (unmerging cells and filling values down). Entirely empty rows and columns are read as NA rows; remove them with janitor::remove_empty() or a filter. Hidden rows are read like any others.

Many sheets and many files

Read all sheets into a named list:

sheets <- excel_sheets(path)
all <- lapply(sheets, function(s) read_excel(path, sheet = s, col_types = "text"))
names(all) <- sheets

Or bind sheets of the same shape with purrr::map_dfr, adding the sheet name as a column. For a folder of files, list.files(pattern = "\\.xlsx$", full.names = TRUE) and the same pattern, with the file name as a column and a check that all headers match before binding (merging hundreds of workbooks into one table describes the validation approach in Python; the logic carries over).

What readxl does not read

readxl reads values. Formulas are returned as their cached results, which are the values saved by Excel. A workbook written by a package that does not calculate has no cached results, and formula cells come back empty. It does not return formatting, comments, merged-cell ranges, hyperlinks or data validation. For those:

• **tidyxl** reads every cell with its value, formula, format and style in a tidy structure, and unpivotr helps to reshape messy layouts. It is the tool for spreadsheets that are really forms or reports.

• **openxlsx2** (successor of openxlsx) reads and writes with formatting, and can load a workbook, modify it and save.

Write a workbook

For a quick export, writexl has no dependencies and is fast:

library(writexl)
write_xlsx(list(Responses = resp, Codebook = codebook), "out.xlsx")

Each element of the list becomes a sheet. It writes values and basic types, with no styling. For formatted reports use openxlsx2:

library(openxlsx2)
wb <- wb_workbook()$
  add_worksheet("Report")$
  add_data(x = resp)$
  add_fill(dims = "A1:D1", color = wb_color("lightgray"))$
  add_font(dims = "A1:D1", bold = TRUE)$
  set_col_widths(cols = 1:4, widths = "auto")
wb_save(wb, "report.xlsx")

Write identifiers as character columns so zeros are kept, and dates as Date or POSIXct with a number format. Neutralize text that begins with =, +, - or @ if it comes from users.

Check the result

After the read, check dim(resp) against the sheet's row count and sum(resp$score, na.rm = TRUE) against Excel. Print problems(resp) or look at warnings() after the read. Check range(resp$date, na.rm = TRUE) for plausible dates. Count NA per column (colSums(is.na(resp))) and compare with what you expect. Compare a few rows by eye with the sheet.

When readxl cannot do what you need

You need formulas, formats or comments. Use tidyxl or openxlsx2.

The sheet is a report layout. Read it with tidyxl and unpivotr, or clean in another tool first (cleaning Excel data before analysis).

The file is huge. readxl reads the whole sheet into memory; convert to CSV or Parquet outside R, and read with arrow or data.table (converting a huge Excel file to CSV or Parquet).

You need to preserve the original file. Write a new one; do not edit in place.

See the readxl package documentation and the openxlsx2 documentation. To convert a small result to JSON, use the CSV to JSON converter.