0Pricing
R Academy · Lesson

Importing Excel Files with readxl

Read .xls and .xlsx sheets with sheet selection and cell range options.

Importing Excel Files with readxl is a free R Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the R Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why readxl for Excel Files?

Excel files (.xlsx and .xls) are ubiquitous in business settings. The readxl package reads them without needing Excel installed, with no Java dependencies. It returns a tibble and handles multiple sheets, named ranges, and cell type detection.

library(readxl)

# readxl can read:
# .xlsx  - modern Excel format (XML-based)
# .xls   - older Excel format (binary)
# .xlsm  - Excel with macros (reads data, ignores macros)

# Core functions:
# read_excel()   - auto-detects .xls vs .xlsx
# read_xlsx()    - always reads as .xlsx
# read_xls()     - always reads as .xls
# excel_sheets() - lists all sheet names

cat('readxl requires no Java, no Excel installation!')

excel_sheets() — List All Sheets

excel_sheets('file.xlsx') returns a character vector of all sheet names. Use this before reading to inspect the workbook structure, then pass the sheet name to read_excel().

library(readxl)

# Using readxl's built-in example file
path <- readxl_example('datasets.xlsx')

# List all sheets in the workbook
sheets <- excel_sheets(path)
print(sheets)

cat('\nNumber of sheets:', length(sheets))

read_excel() — Basic Usage

read_excel(path) reads the first sheet by default. It auto-detects whether the file is .xls or .xlsx. The result is a tibble with column types guessed from the data.

library(readxl)

path <- readxl_example('datasets.xlsx')

# Read the first sheet (default)
df <- read_excel(path)
print(head(df, 4))
cat('\nDimensions:', nrow(df), 'rows x', ncol(df), 'cols')

sheet Argument — Select by Name or Index

Use sheet='Sheet1' to select by name or sheet=2 to select by position (1-indexed). Both are valid. Using names is more robust if sheet order changes; using indices is convenient for iteration.

library(readxl)

path <- readxl_example('datasets.xlsx')
sheets <- excel_sheets(path)
print(sheets)

# Read by name
df_name <- read_excel(path, sheet = 'iris')
cat('iris sheet rows:', nrow(df_name), '\n')

# Read by index
df_idx <- read_excel(path, sheet = 2)
cat('Sheet 2 rows:', nrow(df_idx))

range — Reading a Cell Range

range='A1:D10' reads only the specified cell range in Excel notation. This is useful when a worksheet contains multiple tables, has formatting borders, or has metadata outside the data area you need.

library(readxl)

path <- readxl_example('datasets.xlsx')

# Read only the first 5 data rows of columns A-D
df <- read_excel(path, sheet='iris', range='A1:D6')
print(df)
cat('\nShape:', nrow(df), 'rows,', ncol(df), 'cols')

col_names — Custom Column Names

Set col_names=FALSE to skip the header row and auto-name columns. Pass a character vector to col_names to use custom names (and skip the header row). Combine with skip if the header is on a different row.

library(readxl)

path <- readxl_example('datasets.xlsx')

# Read with custom column names (skip original header)
df <- read_excel(
  path,
  sheet = 'iris',
  col_names = c('sepal_l','sepal_w','petal_l','petal_w','species'),
  skip = 1  # Skip the original header row
)

print(head(df, 3))
print(names(df))

skip Argument — Skip Metadata Rows

skip=n skips the first n rows of the sheet before reading. Useful when worksheets have report titles, generation dates, or other metadata above the actual data table.

library(readxl)

# Simulating a sheet with metadata rows (using a range instead)
path <- readxl_example('datasets.xlsx')

# Skip first row (pretend it has a title)
# and read only first 5 data rows
df <- read_excel(
  path,
  sheet = 'chickwts',
  skip = 1,       # Skip first data row
  col_names = FALSE  # Row 2 has no header now
)

print(head(df, 4))

col_types — Specifying Column Types

Use col_types to override type guessing. Valid types: 'text', 'numeric', 'date', 'logical', 'skip' (drops the column), and 'list' (for mixed-type columns).

library(readxl)

path <- readxl_example('datasets.xlsx')

# Read with explicit column types
# iris sheet: 4 numeric + 1 text
df <- read_excel(
  path,
  sheet = 'iris',
  col_types = c('numeric','numeric','numeric','numeric','text')
)

print(sapply(df, class))

Reading All Sheets with map()

Combine excel_sheets() with purrr::map() to read all sheets at once into a named list. Each list element is a tibble for that sheet. Use map_df(.id='sheet') to row-bind all sheets.

library(readxl)
library(purrr)

path <- readxl_example('datasets.xlsx')
sheets <- excel_sheets(path)

# Read all sheets into a named list
all_data <- map(sheets, ~read_excel(path, sheet=.x))
names(all_data) <- sheets

# Show dimensions of each sheet
map_df(all_data, function(df) {
  data.frame(rows=nrow(df), cols=ncol(df))
}, .id='sheet')

n_max — Limiting Rows Read

n_max=n reads at most n rows of data (excluding the header). Use this to preview large worksheets, load a sample for testing, or read data in chunks for memory-constrained environments.

library(readxl)

path <- readxl_example('datasets.xlsx')

# Preview just the first 5 rows
preview <- read_excel(path, sheet='iris', n_max=5)
print(preview)
cat('\nPreviewed', nrow(preview), 'of', nrow(read_excel(path, sheet='iris')), 'rows')

na Argument — Missing Value Strings

Like readr, readxl accepts a na argument specifying which text strings should be read as NA. Excel often stores missing values as empty cells (handled automatically) or sentinel strings like 'N/A' or '#N/A'.

library(readxl)

path <- readxl_example('datasets.xlsx')

# Handle Excel error strings and custom NA markers
# In real files these might be '#N/A', '#VALUE!', 'N/A', '-'
df <- read_excel(
  path,
  sheet = 'iris',
  na = c('', 'NA', 'N/A', '#N/A', '-')
)

cat('Missing values per column:\n')
print(colSums(is.na(df)))

Quick Check

What does excel_sheets('file.xlsx') return?

Recap: Importing Excel Files

Key takeaways for Excel import with readxl:

  • excel_sheets(path) — list all sheet names
  • read_excel(path) — read first sheet; auto-detects .xls/.xlsx
  • sheet='Name' or sheet=2 — select sheet by name or index
  • range='A1:D10' — read specific cell range
  • col_names, skip — handle metadata rows and custom headers
  • col_types = c('text','numeric','date','skip') — override type guessing
  • n_max=5 — preview large files; na=c('N/A','#N/A') — custom NA strings
  • Combine with map(excel_sheets(path), ~read_excel(path, sheet=.x)) for all sheets
library(readxl)
library(purrr)

path <- readxl_example('datasets.xlsx')

# Full workflow: discover, select, read
cat('Sheets available:', paste(excel_sheets(path), collapse=', '), '\n\n')

# Read a specific sheet with explicit types
df <- read_excel(
  path,
  sheet = 'iris',
  col_types = c('numeric','numeric','numeric','numeric','text'),
  n_max = 5
)

print(df)

Frequently asked questions

Is the “Importing Excel Files with readxl” lesson free?

Yes — the full text of “Importing Excel Files with readxl” is free to read here on the web, and the R Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the R Academy course, upgrade to CoddyKit PRO.

What will I learn in “Importing Excel Files with readxl”?

Read .xls and .xlsx sheets with sheet selection and cell range options. You practise R Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start R Academy?

No prior experience is required. R Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Importing Excel Files with readxl” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this R Academy lesson?

Yes. Every R Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Reading CSV Files with read_csv()
  2. Parsing TSV and Fixed-Width Files
  3. Importing Excel Files with readxl
  4. Writing Data to Multiple Formats
← Back to R Academy