A few years ago, I spent an entire Friday afternoon fighting with a "consolidated" spreadsheet from a client. They had a master workbook with twelve different tabs—one for each month of the year—and each tab had three rows of useless metadata (like "Report Date" and "Author") before the actual data table even started. I tried exporting them to CSVs first, but the formatting broke, and I ended up with a mess of shifted columns. I realized then that trying to force Excel data into a text format before reading it into R is a waste of time. You need a tool that speaks Excel's native language.

That's where the readxl package comes in. It's part of the tidyverse, but it's a bit unique because it doesn't have any external dependencies. It just does one thing very well: it pulls .xls and .xlsx files directly into R as tibbles. Since you've already worked with data frames and tibbles in previous lessons, the output will feel familiar, but the input process requires a bit of precision to get right.

Dealing with Metadata and Offset Rows

Most "real world" Excel files aren't clean. They often have titles or notes at the top that will confuse R if you just call read_excel(). If R sees a title in the first row, it'll assume that's your column header, and your actual data will be shifted. To fix this, use the skip argument.

library(readxl)

# Skip the first 3 rows of "junk" and start reading at row 4
budget_data <- read_excel("company_budget.xlsx", skip = 3)

I also recommend being explicit about your column types. By default, readxl guesses the data type based on the first thousand rows. But if a column is mostly empty at the top and suddenly has numbers at row 1001, R might guess it's a character column and throw a warning. You can pass a vector to col_types—using "text", "numeric", "date", or "skip"—to force R to treat the columns exactly how you intend.

Targeting Specific Sheets and Ranges

The biggest advantage of readxl is how it handles workbooks with multiple tabs. You don't have to save every sheet as a separate file. You can refer to a sheet by its name (a string) or its position (an integer).

# Read the second tab in the workbook
q2_data <- read_excel("sales_report.xlsx", sheet = 2)

# Or better yet, use the actual name so your code doesn't break if someone moves the tabs
q2_data <- read_excel("sales_report.xlsx", sheet = "Quarter 2")

Sometimes, you don't even want the whole sheet. Maybe there's a small summary table tucked away in cells A10 through C15. In those cases, you can use the range argument, which uses standard Excel notation. It's a lifesaver when you're dealing with those "dashboard" style spreadsheets where data is scattered across the page.

# Grab only the summary table from a specific area
summary_stats <- read_excel("sales_report.xlsx", range = "A10:C15")

One quick tip: if you're working with a massive workbook and you aren't sure what the sheets are named, use excel_sheets("your_file.xlsx"). It returns a character vector of all the tab names, which you can then plug into a loop or a map function to import everything at once.

📋 Practical Task

Parsing the Regional Sales Workbook

You have been handed an Excel file named regional_sales.xlsx. The file has a tab named "West_Coast". However, the actual data table doesn't start until row 5 (the first 4 rows are just the company logo and report headers). Additionally, the "West_Coast" sheet has a small "Regional Totals" table located in the range G2:H5.

Write the R code to perform the following:

• Load the readxl library.
• Import the main data table from the "West_Coast" sheet, skipping the first 4 rows, and assign it to a variable called west_sales.
• Import the summary totals from the range G2:H5 of that same sheet and assign it to a variable called west_totals.