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.

Practice

1. What is the primary purpose of the readxl package in R?

easy

A. To convert Excel files to CSV format

B. To write Excel files from R data frames

C. To read Excel files (.xls, .xlsx) into R data frames

D. To create Excel charts from R data

Solution

1. Step 1: Understand the purpose of readxl

   The readxl package is designed to import Excel files into R as data frames.

2. Step 2: Differentiate from other packages

   It does not write files or create charts; it only reads Excel files.

3. Final Answer:

   To read Excel files (.xls, .xlsx) into R data frames -> Option C

4. Quick Check:

   readxl = read Excel files [OK]

Hint: Remember: readxl reads Excel files into R [OK]

Common Mistakes:

• Confusing readxl with packages that write Excel files
• Thinking readxl creates charts
• Assuming readxl converts files to CSV

2. Which of the following is the correct syntax to read the first sheet of an Excel file named data.xlsx using readxl?

easy

A. read_excel(1, "data.xlsx")

B. read_excel("data.xlsx", sheet = 1)

C. read_excel("data.xlsx", sheet = "first")

D. read_excel("data.xlsx", sheet = TRUE)

Solution

1. Step 1: Check the function syntax

   The function read_excel() takes the file path as the first argument, then optional parameters like sheet.

2. Step 2: Validate sheet argument

   Sheet can be specified by number (1) or name (string). read_excel("data.xlsx", sheet = 1) uses sheet = 1 correctly.

3. Final Answer:

   read_excel("data.xlsx", sheet = 1) -> Option B

4. Quick Check:

   File path first, then sheet number [OK]

Hint: File path first, sheet number second in read_excel() [OK]

Common Mistakes:

• Swapping argument order
• Using invalid sheet names like "first"
• Passing TRUE instead of sheet number or name

3. What will be the output of this R code snippet using readxl?

library(readxl)
data <- read_excel("file.xlsx", range = "B2:D4")
print(dim(data))

medium

A. [1] 3 3

B. [1] 4 3

C. [1] 3 4

D. [1] 4 4

Solution

1. Step 1: Understand the range argument

   The range "B2:D4" selects columns B to D (3 columns) and rows 2 to 4 (3 rows).

2. Step 2: Determine dimensions of the data frame

   So the data frame will have 3 rows and 3 columns.

3. Final Answer:

   [1] 3 3 -> Option A

4. Quick Check:

   Range B2:D4 = 3 rows x 3 columns [OK]

Hint: Count rows and columns in range like Excel cells [OK]

Common Mistakes:

• Counting rows or columns incorrectly
• Confusing row and column order
• Assuming range includes headers automatically

4. Identify the error in this code snippet that tries to read the sheet named "Sales" from report.xlsx:

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

medium

A. Sheet name must be a string in quotes

B. File path should not be in quotes

C. read_excel does not support sheet argument

D. Missing library call for readxl

Solution

1. Step 1: Check sheet argument type

   The sheet argument expects a string for sheet name, so it must be in quotes like "Sales".

2. Step 2: Verify other parts

   File path is correctly quoted, readxl supports sheet argument, and library is loaded.

3. Final Answer:

   Sheet name must be a string in quotes -> Option A

4. Quick Check:

   Sheet names need quotes [OK]

Hint: Put sheet names inside quotes in read_excel() [OK]

Common Mistakes:

• Forgetting quotes around sheet names
• Removing quotes from file path
• Not loading readxl library

5. You want to read only the cells from columns C to E and rows 5 to 10 in the sheet named "Data" from records.xlsx. Which code correctly does this using readxl?

hard

A. read_excel("records.xlsx", sheet = "Data", range = "5:10")

B. read_excel("records.xlsx", sheet = Data, range = "C5:E10")

C. read_excel("records.xlsx", range = "C5:E10")

D. read_excel("records.xlsx", sheet = "Data", range = "C5:E10")

Solution

1. Step 1: Specify sheet name correctly

   The sheet name "Data" must be a string in quotes.

2. Step 2: Use correct range format

   The range "C5:E10" selects columns C to E and rows 5 to 10, exactly what is needed.

3. Step 3: Confirm all arguments present

   read_excel("records.xlsx", sheet = "Data", range = "C5:E10") includes file path, sheet name, and range correctly.

4. Final Answer:

   read_excel("records.xlsx", sheet = "Data", range = "C5:E10") -> Option D

5. Quick Check:

   Sheet name + range in quotes = correct [OK]

Hint: Use sheet and range as strings in read_excel() [OK]

Common Mistakes:

• Omitting quotes around sheet name
• Using incomplete range like "5:10"
• Not specifying sheet when multiple sheets exist