21  From Excel to R

Every result produced across the last nine sections can be produced in R, usually in fewer steps and always with a record of how it was done. This closing section runs the same analyses on the same numbers and compares the two, then hands over to Fundamentals of R and R Studio, where the language itself begins.

The point is not that Excel was a waste of time. Inspecting a new file, checking whether the values are plausible, and building a summary someone can open without installing anything remain faster in Excel than anywhere else. The point is what happens after that first look.

21.1 What Changes When the Analysis Becomes Code

Manual workflowraw fileedit, sort, retyperesultThe steps are gone.Nobody can say which cellschanged, or why.next month: do all of it again, by handScripted workflowraw fileanalysis.RresultThe script is the record.Readable, reviewable, andunder version control.next month: point it at the new file and run it onceThe raw file is untouched in both. What differs is whether anything survives that explains the result.

A spreadsheet stores the answer. A script stores the question, which is why the script can be checked, corrected, and rerun while the answer alone cannot. Power Query, described in the previous section, closes much of that gap within Excel itself, and is the right choice when the work stays in Excel.

21.2 Reading the Workbook

The readxl package reads .xlsx files directly, with no need to export to CSV first and no dependency on Java or on Excel being installed.

Run this in RStudio, with agri-analytics-sample.xlsx in the working directory:

install.packages("readxl")     # once only
library(readxl)

farms  <- read_excel("agri-analytics-sample.xlsx", sheet = "FarmData")
crops  <- read_excel("agri-analytics-sample.xlsx", sheet = "CropLookup")
trials <- read_excel("agri-analytics-sample.xlsx", sheet = "TrialResults")

str(farms)
head(farms)
summary(farms)

str() is the first thing to run on any imported file. It reports the type R inferred for each column, and it is where a yield column that arrived as chr rather than num announces itself immediately, which is the same numbers-stored-as-text defect from Data Cleaning and Preparation caught at the door rather than three steps later.

Useful arguments on read_excel():

read_excel(path, sheet = "FarmData", range = "A1:K61")        # an explicit range
read_excel(path, sheet = 1, skip = 3)                         # skip title rows above the header
read_excel(path, sheet = "FarmData", na = c("", "N/A", "NA")) # treat these as missing
read_excel(path, sheet = "FarmData", col_types = "text")      # read everything as text
excel_sheets(path)                                            # list the sheet names first

The na argument is the direct answer to defect 9 from the cleaning section. Listing "N/A" there turns that literal text into a genuine missing value at import, so the column arrives numeric instead of character.

The code blocks below run in the browser rather than in RStudio, and the browser has no access to a file on your machine, so they rebuild the same 60 farms from the workbook’s own values instead of reading the file. The numbers are identical either way.

21.3 The Same Analysis, Both Ways

Four of the summaries produced earlier in this topic, reproduced in R. The group summary corresponds to the PivotTable, the correlations to the Correlation tool, and the regression to the Regression tool in Statistical Analysis with the Analysis ToolPak.

21.4 The Regression, Side by Side

The Analysis ToolPak’s Regression tool needed its predictor columns to be adjacent, produced a static block of numbers, and stopped there. The same model in R is one line, and the object it returns can be queried further.

Every number the ToolPak reported appears here under a different heading. Multiple R Squared is Excel’s R Square at 0.5960. Adjusted R-squared is 0.5743. Residual standard error is Excel’s Standard Error at 4.057. The F-statistic line carries the F value of 27.54 on 3 and 56 degrees of freedom and the p-value that Excel calls Significance F. The coefficient table gives the same estimates, standard errors, t values, and p-values, with significance stars added.

What comes free, and had no equivalent in the ToolPak: confint() for the intervals, plot(model) for four diagnostic charts in one command, predict(model, newdata) for predictions on new farms, and the whole fitted object available to any other function that accepts a model.

21.5 The Trial Tests

The t-test and ANOVA from the ToolPak section, on the same TrialResults values.

The Welch t-test returns t of -3.7731 on 23.4 degrees of freedom with p of 0.000960, matching the ToolPak output exactly apart from the degrees of freedom, which Excel rounds to 23 for its critical values while R keeps the fractional value. The ANOVA returns F of 10.175 on 2 and 42 degrees of freedom with p of 0.000249, again matching.

The last block has no Excel equivalent at all. TukeyHSD() answers the question the ANOVA raised and could not settle: not whether some treatment differs, but which ones. It reports each pairwise difference with a confidence interval and an adjusted p-value that accounts for having made three comparisons. A significant ANOVA followed by eyeballing the group means, which is where the Excel version has to stop, is exactly the inference this function exists to replace.

21.6 Function Translation

Excel R
SUM(range) sum(x)
AVERAGE(range) mean(x)
MEDIAN, MIN, MAX median(x), min(x), max(x)
STDEV.S, VAR.S sd(x), var(x)
COUNT, COUNTA sum(!is.na(x)), length(x)
COUNTIF(r, "Guntur") sum(x == "Guntur")
SUMIFS(sum_r, r, crit) sum(y[x == crit])
AVERAGEIFS mean(y[x == crit])
IF(test, a, b) ifelse(test, a, b)
IFERROR(expr, alt) tryCatch(expr, error = function(e) alt)
VLOOKUP / XLOOKUP merge(x, y, by = "key")
TRIM, UPPER, LOWER trimws(x), toupper(x), tolower(x)
LEFT, RIGHT, MID substr(x, start, stop)
LEN nchar(x)
Remove Duplicates unique(df) or df[!duplicated(df), ]
Sort df[order(df$col), ]
AutoFilter subset(df, condition) or df[condition, ]
PivotTable tapply(), aggregate(), or table()
Text to Columns strsplit(x, "-")
RANK.EQ rank(-x)
PERCENTILE.INC quantile(x, p)
CORREL cor(x, y)
T.TEST t.test(x, y)
F.TEST var.test(x, y)
ANOVA tool aov(y ~ group)
Regression tool lm(y ~ x1 + x2)
Descriptive Statistics tool summary(df)
Power Query Unpivot reshape(), or tidyr::pivot_longer()
Power Query Merge merge(x, y, all.x = TRUE)
FORECAST.LINEAR predict(model, newdata)

One structural difference runs through the whole table. An Excel formula operates on one cell and is then copied down; an R function operates on the entire column at once. That is why a lookup across 60 farms is 60 formulas in Excel and one merge() in R, and why the R version reports a failed match as a visible NA in a known row rather than scattering #N/A down a column for someone to notice later.

21.7 What Carries Over

The habits built in this topic do not get discarded at the door.

The tidy structure from Basics of Excel, one variable per column and one observation per row, is exactly what R expects from a data frame. A sheet organised that way imports cleanly; one with merged cells and multi-row headers does not, and the difficulty of importing it is a reasonably good measure of how badly it was organised.

The cleaning checks transfer directly. COUNT against COUNTA becomes sum(!is.na(x)) against length(x). The UNIQUE audit of a category column becomes table(x), which additionally shows how many of each. The duplicate hunt becomes duplicated(df).

Reading regression output transfers with only the labels changing, as the side-by-side above showed.

And the judgement transfers completely. Which chart answers which question, why a correlation is not a cause, when an average needs its sample size shown beside it, whether a significant difference is a difference worth acting on. None of that is a property of the software.

What R adds is that the work can be rerun, reviewed, and trusted by someone who was not in the room when it was done. Fundamentals of R and R Studio starts there.

Summary

Concept Description
Why Move to R
Manual versus Scripted Workflow A spreadsheet stores the answer; a script stores the question, so it can be checked and rerun
Reading a Workbook with readxl read_excel() takes a sheet name or range directly, with no CSV export and no Java dependency
str() as the First Check Reports the type R inferred per column, catching numbers-stored-as-text at the door
Handling N/A at Import The na argument turns literal N/A text into genuine missing values during import
The Same Analyses
PivotTable as tapply and aggregate Group-then-aggregate in one call, producing the same cross-tabulation as a PivotTable
Correlation Matrix in One Call cor() across several columns replaces the Correlation tool and its matrix output
Regression with lm() One line fits the same model the Regression tool fits, with no adjacency requirement
Matching the ToolPak Output R Square, adjusted R Square, residual standard error, F and the coefficient table all correspond
What lm() Adds confint, plot for diagnostics, predict for new data, and a fitted object usable elsewhere
t.test and aov The same Welch, paired and one-way tests, returning identical statistics
TukeyHSD The post-hoc test Excel lacks, identifying which specific pairs differ after a significant ANOVA
Translation
Function Translation Table A direct mapping from each Excel function to its R equivalent
Column-Wise versus Cell-Wise Excel formulas act on one cell and are copied; R functions act on the whole column at once
What Carries Over Tidy structure, the cleaning checks, output interpretation, and analytical judgement all transfer