Prepare Excel Wide-Format Data for R's pivot_longer and tidyr

Wide years to a tidy tibble for ggplot2

Three year columns gathered into a year column, then plotted. The output is exactly what pivot_longer(cols = c(yr_2021, yr_2022, yr_2023), names_to = 'year', values_to = 'value') would give.

Input (wide):
country,yr_2021,yr_2022,yr_2023
USA,100,120,140
UK,80,90,95

Fields:
  ID columns:    country
  Value columns: yr_2021, yr_2022, yr_2023
  Variable col:  year
  Value col:     value

Then in R:
library(readxl); library(ggplot2)
df <- read_xlsx('unpivoted.xlsx')
ggplot(df, aes(year, value, colour = country, group = country)) +
  geom_line()

Group-by summarise on the long data

Once long, dplyr aggregation is trivial. The value column is numeric-looking text from the melt; read_xlsx infers numeric, or coerce with as.numeric if a stray format slipped through.

Fields:
  ID columns:    region, product
  Value columns: q1, q2, q3, q4
  Variable col:  quarter
  Value col:     units

In R:
df <- read_xlsx('unpivoted.xlsx')
df |>
  group_by(quarter) |>
  summarise(total_units = sum(units, na.rm = TRUE))

Drop NA rows that the melt preserves

Unlike values_drop_na = TRUE in pivot_longer, this tool keeps empty cells as rows. Replicate the drop in R after reading, so your tidy frame has no blank observations.

Input (wide, UK missing yr_2023):
country,yr_2022,yr_2023
USA,120,140
UK,90,

After unpivot you get a row: UK, yr_2023, (empty)

In R, drop it to mimic values_drop_na = TRUE:
df <- read_xlsx('unpivoted.xlsx')
df <- df |> filter(!is.na(value) & value != '')

Keep an extra ID variable R needs

Columns you do not list are dropped. If R needs a grouping variable (e.g. cohort), add it to the ID columns so it survives the reshape and is available for facet_wrap.

Input (wide):
country,cohort,yr_2022,yr_2023
USA,A,120,140
UK,B,90,95

Fields:
  ID columns:    country, cohort
  Value columns: yr_2022, yr_2023
  Variable col:  year
  Value col:     value

In R:
ggplot(df, aes(year, value)) +
  geom_col() +
  facet_wrap(~ cohort)

Why pivot before R, not inside it

For a wide file that is slow to read or that you only want long, reshaping first means read_xlsx loads a smaller-schema, already-tidy file — no pivot_longer call to maintain and no chance of a tidyselect typo silently gathering the wrong columns.

Instead of (inside R, risk of wrong cols):
df <- read_xlsx('wide.xlsx') |>
  pivot_longer(cols = starts_with('yr_'),
               names_to = 'year', values_to = 'value')

Do (reshape first, then a plain read):
# Unpivot tool with explicit value columns yr_2021..yr_2023
df <- read_xlsx('unpivoted.xlsx')   # already tidy

tidyr::pivot_longer(values_drop_na = TRUE) removes rows where the gathered value is NA. This tool always emits a row per value column, including empty cells. To match the tidy default, filter(!is.na(value) & value != '') after read_xlsx.

Only ID and value columns survive. If R later joins on an identifier you forgot to list (e.g. id, cohort), that column is gone. Add every variable R needs to the ID columns list before reshaping.

Values are read with formatting applied, so the value column may come into R as character if cells carried text formatting. read_xlsx usually infers numeric, but a stray format can force character. Coerce with mutate(value = as.numeric(value)) if sum() complains.

Matching is exact (after trimming). A typo like yr2022 vs yr_2022 yields empty values for that gathered name across all rows — an all-NA block in R. Copy header text exactly; tidyselect helpers like starts_with() are not available here.

pivot_longer's .value sentinel can split rev_2021/cost_2021 into measure + year. This tool melts one group into one (variable, value) pair. Melt once, then split the variable column on the delimiter in R (or with the regex extractor before reading), or run pivot_longer for the multi-measure case.

Only the first sheet is processed. Put the wide table on the first tab before uploading, or read_xlsx on the result will see nothing useful.

If your wide headers are actual dates, the gathered variable column holds the displayed date text. Parse it in R with lubridate::ymd() (or similar) to recover a Date type for time-series work.

The input-row cap applies before reshaping (Pro 100,000). A many-year panel can exceed it. Split the panel, upgrade (Developer is unlimited), or pivot_longer inside R for the very largest files.

Un-Pivot is Pro-gated and throws 'Un-Pivot requires Pro tier' for free accounts. If you are already in R, pivot_longer is free; the browser tool's value is skipping the in-R reshape and the install.