Excel files with readxl in R Programming - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

What is the output of reading a specific sheet?

Consider an Excel file named data.xlsx with two sheets: Sheet1 and Sheet2.
Sheet1 contains a column Value with values 1, 2, 3.
Sheet2 contains a column Value with values 4, 5, 6.

What will be the output of this code snippet?

R Programming

library(readxl)
data <- read_excel("data.xlsx", sheet = "Sheet2")
print(data$Value)

A Error: Sheet 'Sheet2' not found

B [1] 1 2 3

C [1] 4 5 6

D [1] NA NA NA

💡 Hint

Remember the sheet argument specifies which sheet to read.

The code reads the sheet named 'Sheet2' which contains values 4, 5, 6 in the 'Value' column. So printing data$Value outputs [1] 4 5 6.

❓ Predict Output

intermediate

What happens when reading a non-existent sheet?

Given the same Excel file data.xlsx with sheets Sheet1 and Sheet2, what will happen if you run this code?

R Programming

library(readxl)
data <- read_excel("data.xlsx", sheet = "Sheet3")

A Reads the first sheet by default

B Error: Can't find sheet named 'Sheet3'

C Returns an empty data frame

D Reads the last sheet in the file

💡 Hint

Check what happens if the sheet name does not exist.

The read_excel function throws an error if the specified sheet does not exist in the Excel file.

🔧 Debug

advanced

Why does this code fail to read the Excel file?

You want to read the first 5 rows of an Excel file data.xlsx using readxl. You write this code:

R Programming

library(readxl)
data <- read_excel("data.xlsx", n_max = 5)
print(nrow(data))

A Code runs correctly and prints 5

B Error: unused argument 'n_max'

C Error: file not found

D Code runs but prints all rows, ignoring n_max

💡 Hint

Check the readxl documentation for the n_max argument.

The argument n_max is valid in read_excel and limits the number of rows read. So the code prints 5.

🧠 Conceptual

advanced

How to read only specific columns from an Excel sheet?

You want to read only columns named Age and Name from an Excel sheet using readxl. Which approach is correct?

A Use read_excel with col_types = c("skip", "text", "numeric") to skip unwanted columns

B Use read_excel with sheet argument set to the column names

C Use read_excel and then subset columns after reading all data

D Use read_excel with range argument specifying only the columns needed

💡 Hint

The range argument can specify a cell range like "B:C" to read specific columns.

Using the range argument allows reading only specific columns by specifying the cell range. col_types can skip columns but requires knowing positions. Subsetting after reading reads all data. Sheet argument is for sheet names, not columns.

❓ Predict Output

expert

What is the output of this code reading multiple sheets?

Given an Excel file data.xlsx with sheets Jan and Feb, each with a column Sales containing numbers:
Jan: 10, 20
Feb: 30, 40

What is the output of this code?

R Programming

library(readxl)
library(purrr)
sheets <- excel_sheets("data.xlsx")
data_list <- map(sheets, ~ read_excel("data.xlsx", sheet = .x))
total_sales <- sum(unlist(map(data_list, "Sales")))
print(total_sales)

A 100

B Error: object 'Sales' not found

C 70

D Error: could not find function 'map'

💡 Hint

Sum all sales from both sheets.

Jan sales sum to 30 (10+20), Feb sales sum to 70 (30+40). Total is 100. The code uses purrr::map correctly and sums all sales.