R Programming Quiz: Reading Excel Files
10 questions · exam conditions
0:00
Reading Excel FilesQuestion 1 of 10

A workbook has sheets named Current and Archive. An analyst runs read_excel("orders.xlsx", sheet = "Current", range = "Archive!B3:C6", skip = 10, n_max = 1, col_names = TRUE).

Which result should the analyst expect?

One data row from Current, because sheet and n_max jointly control which sheet and rows are imported
An error, because specifying a sheet name inside range conflicts with a separate sheet argument
One data row from Archive, because the range selects the sheet but n_max still limits the row count
Three data rows from Archive, because a sheet-qualified range overrides sheet, skip, and n_max
← Back to quizzes

R Programming Quiz

R Programming Quiz: Reading Excel Files

Practice Reading Excel Files in R Programming with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Reading Excel Files, giving you a quick way to practice the rules, question types, and explanations that matter most for R Programming.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

A workbook has sheets named Current and Archive. An analyst runs read_excel("orders.xlsx", sheet = "Current", range = "Archive!B3:C6", skip = 10, n_max = 1, col_names = TRUE).

Which result should the analyst expect?

  1. One data row from Current, because sheet and n_max jointly control which sheet and rows are imported
  2. An error, because specifying a sheet name inside range conflicts with a separate sheet argument
  3. One data row from Archive, because the range selects the sheet but n_max still limits the row count
  4. Three data rows from Archive, because a sheet-qualified range overrides sheet, skip, and n_max (correct answer)
Explanation: When working with read_excel(), one of the most important rules to internalize is that a sheet-qualified range overrides all other location-controlling arguments. This is the core concept being tested here. When you embed a sheet name directly inside the range argument using the syntax "SheetName!CellRange" — in this case "Archive!B3:C6" — the readxl package treats that qualified range as the authoritative source of truth. It ignores the separate sheet argument entirely, pulling data from Archive, not Current. More critically, the explicit cell range B3:C6 defines exactly which cells are read, so skip and n_max are also bypassed. The range B3:C6 spans 4 rows: row 3 is consumed as the header (since col_names = TRUE), leaving rows 4, 5, and 6 as three data rows. That makes D correct. A is wrong because sheet does not override a sheet-qualified range — the qualified range wins, and n_max does not apply when a cell range is explicit. B reflects a reasonable-sounding guess, but read_excel() does not throw an error on this conflict — it silently resolves it by prioritizing the qualified range, which may surprise you if you expect strict argument validation. C gets the sheet right but misunderstands n_max: once a fully-qualified cell range is specified, n_max no longer limits rows — the range boundaries do. As a study strategy, remember: in readxl, a sheet-qualified range is the "nuclear option" — it overrides sheet, skip, and n_max simultaneously. Always check whether range contains a ! before assuming other arguments are active.

Question 2

The header row of a worksheet contains id, a blank cell, and another id. The analyst runs read_excel("records.xlsx", .name_repair = "unique").

Which statement best describes the resulting columns?

  1. All three columns remain, but the two id columns retain exactly the same name
  2. Only the first id column remains, because duplicate headers are removed with their data
  3. All three columns remain, and missing or duplicate names are repaired to be unique (correct answer)
  4. The import fails, because name repair cannot handle a blank header and a duplicate together
Explanation: When importing Excel files with messy headers, the key concept to understand is how R's readxl package handles problematic column names through its .name_repair argument. Rather than failing or silently dropping data, read_excel is designed to be forgiving — it keeps all columns and fixes their names according to the repair strategy you specify. With .name_repair = "unique", R guarantees that every column gets a name and that no two columns share the same name. A blank cell becomes something like ...2, and the duplicate id becomes id...3 (using positional suffixes to distinguish it). All three columns survive the import with repaired, unique names — which is exactly what C describes. A is wrong because "unique" repair explicitly prevents two columns from keeping identical names. The whole point of that setting is to eliminate duplicate names, not preserve them. B reflects a serious misconception: read_excel never silently drops columns based on header issues. Your data rows are preserved regardless of name conflicts. D is also incorrect — .name_repair = "unique" is specifically designed to handle both missing and duplicate names simultaneously. The function won't throw an error here; that's the problem it's built to solve. A useful study tip: remember that .name_repair has several levels — "minimal" (keep as-is), "unique" (force uniqueness), "universal" (also make syntactically valid). On exam questions, if you see "unique", think "all columns survive, names get disambiguated" — data is never discarded just because headers are messy.

Question 3

A worksheet contains four columns in this order: customer, internal_note, balance, and renewal_date. The internal notes must not appear in the imported object.

What is the effect of read_excel("renewals.xlsx", col_types = c("text", "skip", "numeric", "date"))?

  1. It returns four columns, with internal_note filled entirely with missing values
  2. It skips the second worksheet row and applies the remaining types by position
  3. It returns three columns after omitting internal_note during the import (correct answer)
  4. It produces an error because skip is valid only as a separate argument
Explanation: When importing Excel files with readxl, the col_types argument lets you control not just how each column is parsed, but whether it appears in your output at all. The key insight here is that "skip" is a valid type value within the col_types vector — it tells read_excel to drop that column entirely rather than import it with any type. In this question, the vector c("text", "skip", "numeric", "date") maps positionally to the four columns: customer gets "text", internal_note gets "skip", balance gets "numeric", and renewal_date gets "date". Because internal_note is skipped, it never appears in the resulting data frame — you get back only three columns. That makes C correct. A is wrong because "skip" doesn't produce a column of NA values — it removes the column from the output entirely. If you wanted a column filled with NA, you'd use "skip" with a workaround or just import and then replace values manually. B confuses column skipping with row skipping; skip as a standalone argument to read_excel() controls how many rows to skip at the top of the sheet, but inside col_types, "skip" operates on a column. D is the most tempting distractor if you mix up these two uses of skip, but "skip" is fully valid inside col_types — no error is produced. A useful habit: remember that skip has two distinct roles in read_excel — as a standalone argument (rows) versus as a col_types value (columns). Keeping that distinction clear will help you avoid B and D on similar questions.

Question 4

A workbook named sales.xlsx contains sheets named Summary, North, and South. An analyst wants a named list containing one tibble for each sheet.

Which code correctly imports every sheet and names each list element with the corresponding worksheet name?

  1. s <- excel_sheets("sales.xlsx"); setNames(lapply(s, function(x) read_excel("sales.xlsx", sheet = x)), s) (correct answer)
  2. s <- excel_sheets("sales.xlsx"); setNames(read_excel("sales.xlsx", sheet = s), s)
  3. s <- excel_sheets("sales.xlsx"); setNames(lapply(s, function(x) read_excel(x, sheet = "sales.xlsx")), s)
  4. s <- excel_sheets("sales.xlsx"); setNames(lapply(seq_along(s), function(x) read_excel("sales.xlsx", sheet = x)), seq_along(s))
Explanation: When working with multi-sheet Excel files in R, the core challenge is combining three tools correctly: excel_sheets() to retrieve sheet names, lapply() to iterate and import each sheet, and setNames() to label the resulting list. Understanding how each function behaves individually helps you spot errors quickly. Option A is the correct approach. It first retrieves all sheet names into s, then uses lapply() to loop through each name and call read_excel("sales.xlsx", sheet = x) for each one — producing a list of tibbles. Finally, setNames() assigns those sheet names as list element names. This is the clean, idiomatic pattern for this task. Option B fails because read_excel() does not accept a vector of sheet names — it reads exactly one sheet per call. Passing s (a character vector of three names) directly won't import all three sheets; it will either error or return only one tibble, not a named list. Option C swaps the arguments to read_excel(), passing the sheet names as the file path and "sales.xlsx" as the sheet argument. This reverses the role of the two parameters entirely, so R would look for files named "Summary", "North", and "South" — none of which exist. Option D uses numeric indices in lapply() and then names the list with those same integers via seq_along(s). While numeric indexing can work for importing the sheets, the list elements end up named 1, 2, 3 rather than the actual sheet names — which defeats the purpose. A reliable study tip: whenever you need to apply a function across a vector and collect results into a named list, the pattern setNames(lapply(vector, function(x) f(x)), vector) is your go-to template in R.

Question 5

A workbook uses manual calculation. A formula cell displays a cached result of 30, although recently changed input cells would make the formula evaluate to 40 if Excel recalculated the workbook. The file is saved without recalculation and then read with read_excel().

What should the R user expect for that formula cell?

  1. The value 40, because read_excel() evaluates formulas using the current input cells
  2. The value 30, because read_excel() reads the stored result without recalculating (correct answer)
  3. The formula expression itself, because formulas are imported as ordinary character text
  4. A missing value, because formula cells cannot be read unless Excel is currently open
Explanation: When working with Excel files in R, it helps to understand the distinction between what Excel stores versus what Excel computes. Excel workbooks can operate in manual calculation mode, meaning formulas aren't recalculated until the user explicitly triggers it. When a file is saved in this state, Excel writes the last cached result to disk — not the formula logic itself, and not a freshly computed value. read_excel() from the readxl package is a pure file reader. It parses the Excel file's binary or XML structure and extracts whatever values are stored there. In this scenario, the cached result of 30 is what lives in the file, so 30 is exactly what read_excel() returns. This makes B the correct answer — the R user should expect 30. A is wrong because read_excel() has no formula engine. It cannot look at input cells, apply formula logic, and compute 40. That kind of recalculation requires Excel itself (or a library that embeds a calculation engine, which readxl does not). C is wrong because Excel does not store formula expressions as plain text in the cell value slot — it stores the evaluated result. The formula string exists separately in the file structure, but read_excel() returns the cached numeric result, not the formula text. D is wrong because readxl reads files directly from disk with no dependency on a running Excel instance. Formula cells are perfectly readable; they simply return their cached value. Study tip: Whenever you see a question about read_excel() and formulas, remember the mantra: reads the file, not the spreadsheet logic. The cached value rules.

Question 6

A worksheet has columns account_id, amount, and opened. The account IDs are stored as Excel text such as "00127"; amounts are numeric cells; and opening dates are Excel date cells. Type guessing has previously produced inconsistent results across files.

Which call most directly enforces the intended types while preserving the leading zeros in the stored account IDs?

  1. read_excel("accounts.xlsx", col_types = c("numeric", "text", "date"))
  2. read_excel("accounts.xlsx", col_types = c("guess", "text", "date"))
  3. read_excel("accounts.xlsx", col_types = c("text", "date", "numeric"))
  4. read_excel("accounts.xlsx", col_types = c("text", "numeric", "date")) (correct answer)
Explanation: When working with read_excel() from the readxl package, the col_types argument accepts a character vector where each element corresponds to a column in order. The valid type strings are "text", "numeric", "date", "logical", "list", and "guess". Getting this order wrong is the most common source of errors, so always match position-by-position against your actual columns. The worksheet has three columns in this order: account_id, amount, opened. You need "text" for account IDs (to preserve leading zeros like "00127" — reading them as numeric would silently drop the zeros), "numeric" for amounts, and "date" for opening dates. That maps directly to c("text", "numeric", "date"), making D the correct answer. Choice A fails immediately because specifying "numeric" for account_id will coerce "00127" to 127, destroying the leading zeros — exactly the problem the question warns against. Choice B uses "guess" for the first column, which is explicitly unreliable; the passage states type guessing has already produced inconsistent results, so this is the wrong tool here. Choice C applies the right types but in the wrong column order — "text" goes to account_id correctly, but then "date" is assigned to amount and "numeric" to opened, which completely misaligns the types. A reliable strategy: before writing your col_types vector, write out the column names as a comment and align each type beneath it. This one-to-one mapping habit eliminates the ordering mistakes that choices A, B, and C all represent.

Question 7

In inventory.xlsx, the range B2:D5 contains four spreadsheet rows and three columns. Row 2 contains column headings, while one cell in row 5 is blank.

What dimensions will the object returned by read_excel("inventory.xlsx", range = "B2:D5", col_names = TRUE) have?

  1. Three columns and two data rows, because the heading and blank cell are excluded
  2. Three columns and three data rows, because the first selected row supplies names (correct answer)
  3. Four columns and three data rows, because spreadsheet endpoints are added as data
  4. Three columns and four data rows, because the heading remains an observation
Explanation: When working with read_excel() from the readxl package, you need to think carefully about how the range and col_names arguments interact to determine your final data frame dimensions. Here, range = "B2:D5" selects a block spanning 4 rows and 3 columns. When col_names = TRUE, read_excel() treats the first row of that range as a header, not as data. Since row 2 contains your column headings, those three cells become the column names of the resulting data frame. The remaining three rows (rows 3, 4, and 5) become your data rows — including row 5, even though one of its cells is blank. A blank cell becomes NA, but the row itself is still read. This gives you a data frame with 3 columns and 3 data rows, confirming that B is correct. A is wrong because it claims both the heading row and the blank cell are excluded. read_excel() does not drop rows simply because one cell is missing — it fills the missing value with NA. Only the header row is consumed as names, not discarded entirely. C is wrong on two counts: there are no "spreadsheet endpoints" added as extra columns, and the column count stays at 3, not 4. D reflects a misunderstanding of how col_names = TRUE works. When set to TRUE, the first row is promoted to column names and removed from the data body — it does not remain as an observation. As a study tip, always trace range selection step by step: count total rows, subtract the header row when col_names = TRUE, then count remaining rows as your data observations.

Question 8

After its header, a worksheet column contains 1,000 numeric cells followed by a text code in the next data row. An analyst imports all rows using guess_max = 1000, and no explicit col_types value is supplied.

What is the most likely outcome for this column, and which change is most appropriate if every value must be retained as text?

  1. It is guessed as numeric; the text code may become missing, so set col_types = "text" for that column (correct answer)
  2. It is guessed as text; the numeric cells lose their values, so increase guess_max to inspect more rows
  3. It is guessed as a list column; all cell types are retained automatically without any further change
  4. It is guessed as numeric; the text code is automatically promoted to a factor level during import
Explanation: When working with readr's read_csv() (or similar functions), type guessing is a critical concept to understand. The package samples a limited number of rows — controlled by guess_max — to infer each column's data type before reading the full file. Here, guess_max = 1000 means readr inspects exactly 1,000 rows to guess the column type. Since those 1,000 rows are all numeric, the column gets labeled as numeric. When readr then encounters the text code in row 1,001, it cannot coerce that string into a number, so it silently replaces the value with NA. This makes A the correct answer: the column is guessed as numeric, the text code is lost, and the fix is to explicitly declare col_types = "text" (or col_types = cols(your_column = col_character())) for that column so every value is read as-is. B is backwards — text is not guessed when the sampled rows are all numeric. Increasing guess_max would expose the problem but doesn't fix it; you'd still need to set the type explicitly. C is incorrect because list columns aren't automatically created during type guessing; they require deliberate specification and are used for nested or mixed structures. D describes behavior that simply doesn't exist in readr — the package never promotes a coercion failure into a factor level. Factor handling is a separate, explicit step. A useful rule of thumb: whenever a column contains mixed types or you know a "surprising" value lurks beyond the guessed rows, always supply col_types explicitly rather than relying on sampling. Don't let silent NA conversion hide data quality issues.

Question 9

A worksheet is organized as follows: row 1 is a title, row 2 is a note, row 3 contains variable names, and rows 4 onward contain observations.

Which source rows become observations after running read_excel("survey.xlsx", skip = 2, n_max = 4, col_names = TRUE)?

  1. Rows 3 through 6, because n_max includes the row used for names
  2. Rows 4 through 7, because row 3 supplies names before four observations (correct answer)
  3. Rows 5 through 8, because the header is counted as an additional skipped row
  4. Rows 4 through 8, because n_max identifies the final source-row offset
Explanation: When working with read_excel(), you need to mentally simulate how R processes the file row by row. The arguments skip, col_names, and n_max each play distinct roles, and understanding their sequence is the key to this type of question. After skip = 2, R ignores source rows 1 and 2 entirely — the title and note disappear. R then encounters source row 3. Because col_names = TRUE, that row is consumed as the header, supplying variable names. At this point, no observations have been read yet. R then reads the next n_max = 4 rows as data observations, which are source rows 4, 5, 6, and 7. So answer B is correct: rows 4 through 7 become your observations, with row 3 serving as the column name supplier. Answer A is wrong because it misunderstands what n_max counts. n_max limits the number of data rows, not the total rows processed including the header. Row 3 is consumed before n_max even starts counting, so the window begins at row 4, not row 3. Answer C incorrectly treats the header row as an additional skipped row, pushing everything down by one. The header is read, not skipped — it just isn't stored as an observation. Answer D is wrong on two counts: it misidentifies both the starting and ending rows, suggesting n_max defines a row offset rather than a count of observations. A reliable strategy: trace the file top to bottom mentally — skip first, then header, then count n_max data rows. That sequence never changes regardless of how the arguments are combined.

Question 10

A text column in an Excel worksheet contains the values " west ", " N/A ", and "not available". The analyst imports it with read_excel("regions.xlsx", na = c("", "N/A"), trim_ws = TRUE).

Which description of the imported values is correct?

  1. "west", a missing value, and "not available" (correct answer)
  2. " west ", " N/A ", and "not available"
  3. "west", "N/A", and a missing value
  4. "west", a missing value, and a missing value
Explanation: When importing data with readxl::read_excel(), two parameters work independently but sequentially: trim_ws strips leading and trailing whitespace from cell values before R evaluates whether the result matches any na string. Keep this two-step process in mind whenever a question involves both whitespace and NA conversion. Here's what happens to each value. " west " gets its surrounding spaces trimmed, producing "west" — a clean character string. " N/A " is trimmed to "N/A", which exactly matches one of the strings in na = c("", "N/A"), so it becomes a true missing value (NA). "not available" is trimmed (no spaces to remove) and does not match either NA string, so it stays as "not available". That gives you "west", NA, and "not available" — which is answer A. B is wrong because it ignores trim_ws = TRUE entirely, leaving all values untouched with their original whitespace. C gets the trimming right for "west" but misapplies the NA logic — it treats "not available" as NA and leaves "N/A" as a string, which is backwards; "N/A" matches the na vector, but "not available" does not. D would require both " N/A " and "not available" to resolve to NA, but only "N/A" (after trimming) is in the na vector. The key study tip: always apply trim_ws mentally first, then check the trimmed result against your na vector. These two steps happen in order, and confusing the sequence is exactly what distractors B, C, and D are designed to exploit.