| Concept | Description |
|---|---|
| Orientation | |
| Why Excel Before R | Data arrives as spreadsheets and is inspected there before any modelling begins |
| The Sample Workbook | One agricultural workbook, eight sheets, used for every example in this topic |
| The Excel Environment | |
| The Excel Window | Ribbon, Name Box, formula bar, headers, active cell, sheet tabs, and the status bar summary |
| Workbooks, Worksheets, and Cells | Cell, range, whole-column, cross-sheet, and cross-workbook addressing forms |
| The Four Data Types | Numbers, text, dates stored as serial numbers, logical values, and errors |
| Relative, Absolute, and Mixed References | The dollar sign pins a column or row when a formula is copied; F4 cycles the four forms |
| Excel Tables (Ctrl+T) | Structured references by column name that survive sorting, insertion, and new rows |
| Keyboard Shortcuts | Ctrl+arrow, F4, Ctrl+Enter, Ctrl+T, Alt+=, and the other shortcuts worth memorising |
| Data Quality and Limits | |
| Spreadsheet Data Organization | One variable per column, no merged cells, one header row, unambiguous dates, raw file untouched |
| Known Spreadsheet Error Rates | Audits find errors in most operational spreadsheets; autocorrect silently alters recognised patterns |
| Where Excel Stops | Row ceilings, no record of manual edits, repeat cost, and limited statistical depth |
12 Foundations of Data Analysis Using Excel
Everything from the next topic onward runs in R. That is the right tool for modelling, for anything that has to be repeated next season, and for work that another person needs to reproduce exactly. Before any of it, though, there is a file, and that file is almost always a spreadsheet.
A cooperative’s member register, a mandi price sheet, the raw observation log from a fertilizer trial, a soil testing lab’s return: these arrive as .xlsx, and they arrive with merged cells, trailing spaces, district names spelled four different ways, and numbers stored as text. Analysts spend more time on that stage than on the modelling that follows. Knowing Excel well is not a detour on the way to R. It is the part of the work where data quality is actually decided.
This topic covers Excel as a data analysis tool: the interface and its shortcuts, the formulas worth memorising, cleaning techniques, lookups, PivotTables, charts, the Analysis ToolPak for t-tests and ANOVA and regression, and Power Query for work that has to be repeated. The closing section reads the same workbook into R and confirms the two give identical answers.
12.1 The Sample Workbook
Every example in this topic runs on one file. Download it, keep it open in a second window, and work along.
Download agri-analytics-sample.xlsx
| Sheet | What it holds | Used in |
|---|---|---|
README |
Sheet-by-sheet description and column definitions | Reference |
FarmData |
60 farms: district, crop, area, irrigation, inputs, NDVI, yield | Most sections |
RawData_Messy |
The same 60 farms before cleaning, with real defects left in | Data Cleaning |
CropLookup |
Crop code, crop name, season, minimum support price | Lookup Functions |
DistrictLookup |
District, agro-climatic zone, extension officer | Lookup Functions |
TrialResults |
Fertilizer trial: three treatments, plus paired before and after plots | Analysis ToolPak |
MonthlyPrices |
48 months of wholesale prices for three crops | Charts |
SalesWide |
Quarterly volumes in wide layout, one column per quarter | Power Query |
The data is synthetic but built to behave like real farm records, including the awkward parts. RawData_Messy is not a toy: it contains the specific defects that show up in field data, and the cleaning section works through each one.
12.2 Basics of Excel
12.2.1 The Excel Window
Six regions matter for analysis work. The rest can be ignored until it is needed.
The Name Box does more than display the active cell address. Type a range into it and Excel selects that range, which beats scrolling when a dataset runs to 40,000 rows. Type a defined name and it jumps there.
The status bar is the fastest summary tool in Excel and the most ignored. Select any range of numbers and the bar shows count, average, and sum without a single formula. Right-click it to add minimum, maximum, and numerical count. For a quick sanity check on a column, this is faster than writing =AVERAGE() anywhere.
12.2.2 Workbooks, Worksheets, and Cells
A workbook is the file. A worksheet is one tab inside it, holding a grid of 1,048,576 rows by 16,384 columns. A cell is one intersection, addressed by column letter and row number, so D5 means column D, row 5.
Three addressing forms come up constantly:
| Form | Example | Meaning |
|---|---|---|
| Single cell | D5 |
One cell |
| Range | D2:D61 |
A rectangular block, top-left to bottom-right |
| Whole column | D:D |
Every row in column D |
| Cross-sheet | CropLookup!A2:D9 |
A range on a different sheet, sheet name then !
|
| Cross-workbook | [prices.xlsx]Sheet1!B2 |
A range in a different file |
Sheet names containing spaces need single quotes: 'Trial Results'!A1. This trips people up constantly, and it is also why sheet names in a well-built workbook avoid spaces entirely.
12.2.3 The Data Types Excel Recognizes
Excel stores four kinds of value, and confusing them causes most beginner errors.
Numbers right-align by default. Text left-aligns by default. That alignment difference is a free diagnostic: a column of yields that left-aligns is text, not numbers, and AVERAGE will quietly skip every one of those cells rather than warn about it.
Dates and times are numbers wearing a costume. Excel stores 1 January 1900 as the number 1 and counts days forward, so 16 September 2026 is stored as 46281. This is why subtracting one date from another gives a count of days, and why a date can accidentally display as a five-digit number if the cell formatting is cleared.
Logical values are TRUE and FALSE, which arithmetic treats as 1 and 0. =SUM((A2:A61="Kharif")*1) works for exactly that reason.
Errors are values too. #DIV/0!, #N/A, #VALUE!, #REF!, and #NAME? each point at a specific fault, covered where each one tends to appear.
12.2.4 Cell References: Relative, Absolute, and Mixed
This single concept explains more broken spreadsheets than any other. A reference written D2 moves when the formula is copied. A reference written $D$2 does not.
The $ locks whatever follows it. $D2 pins the column and lets the row move; D$2 pins the row and lets the column move.
=B2*C2 Relative. Both shift when copied.
=B2*$H$1 Absolute. $H$1 stays put no matter where this is copied.
=$B2*C$1 Mixed. Column B pinned, row 1 pinned.Work through it on FarmData. Put a price per quintal in N1, then in M2 enter the revenue formula and fill it down to row 61:
=J2*$N$1Written as =J2*N1 instead, row 3 would reference N2, row 4 would reference N3, and every result below the first would be zero. No error message appears. The column just fills with zeros, which is exactly the kind of fault that survives into a report.
12.2.5 Keyboard Shortcuts That Matter
Learn these fifteen and Excel stops being slow. The rest can wait.
Navigation and selection
| Action | Windows | Mac |
|---|---|---|
| Jump to the edge of a data block |
Ctrl + arrow |
Cmd + arrow |
| Select to the edge of a data block |
Ctrl + Shift + arrow |
Cmd + Shift + arrow |
| Select the whole current region |
Ctrl + A
|
Cmd + A
|
| Go to cell A1 |
Ctrl + Home
|
Fn + Ctrl + ←
|
| Move between sheets |
Ctrl + PgUp / PgDn
|
Fn + Ctrl + ↑ / ↓
|
Editing and entry
| Action | Windows | Mac |
|---|---|---|
| Edit the active cell in place | F2 |
Ctrl + U
|
| Cycle reference type while editing | F4 |
Fn + F4 or Cmd + T
|
| Enter the same value in all selected cells |
Ctrl + Enter
|
Ctrl + Return
|
| Fill down from the cell above |
Ctrl + D
|
Cmd + D
|
| Insert today’s date |
Ctrl + ;
|
Ctrl + ;
|
| Paste special |
Ctrl + Alt + V
|
Cmd + Ctrl + V
|
Analysis
| Action | Windows | Mac |
|---|---|---|
| Convert range to a Table |
Ctrl + T
|
Cmd + T
|
| AutoSum the column above |
Alt + =
|
Cmd + Shift + T
|
| Toggle filters on the header row |
Ctrl + Shift + L
|
Cmd + Shift + F
|
| Open the Format Cells dialog |
Ctrl + 1
|
Cmd + 1
|
Ctrl + arrow deserves special attention. Pressing Ctrl + ↓ in a clean column lands on the last filled row. If it stops early, there is a blank cell in the middle of the data, which is worth knowing before running any analysis on that column.
12.2.6 Excel Tables
Select any cell inside a dataset and press Ctrl + T. Excel converts the range into a structured Table, and three useful things follow.
Formulas start referring to column names rather than letters. Instead of =SUM(J2:J61), the formula reads:
=SUM(FarmData[YieldQtPerHa])That formula still works after rows are inserted, deleted, or sorted. A range reference like J2:J61 does not: add a 61st farm and the total silently excludes it.
New rows added at the bottom join the Table automatically, so PivotTables, charts, and formulas built on it pick up the new data on refresh. Filter buttons appear on the header row without needing to be switched on.
Name the Table as soon as it is created, through Table Design then Table Name. Table1 tells a reader nothing; FarmData tells them everything. Every worked example from here on assumes the sample workbook’s ranges have been converted to named Tables, though each formula is also given in plain range form for anyone who prefers to work without them.
12.3 How a Spreadsheet Should Be Organized
Most spreadsheet pain is self-inflicted at data entry. Broman and Woo (Karl W. Broman & Kara H. Woo, 2018) set out rules for organizing data in spreadsheets that are worth following from the first row typed.
One variable per column, one observation per row. This is the same tidy structure Wickham (Hadley Wickham, 2014) argues for in R, and the reason a tidy sheet imports into R without a fight.
No merged cells anywhere in a data range. Merging is a formatting choice that destroys the grid structure. Sorting breaks, PivotTables refuse to build, and read_excel() returns a column of NA where the merge was.
One header row, in row 1, with short names free of spaces. YieldQtPerHa survives the trip into R as-is. Yield (qt/ha) 2026 becomes something unrecognisable.
No blank rows or columns inside the data. A blank row splits the data into two regions as far as Ctrl + A, AutoFilter, and PivotTables are concerned.
Dates in one unambiguous format, and preferably as real dates rather than text. 03/04/2026 is 3 April in India and 4 March in the United States. 2026-04-03 is the same day everywhere.
Never put units, footnotes, or commentary in a data cell. A cell holding 42 qt/ha is text. A cell holding 42 with the unit in the column header is a number.
Keep the raw file untouched. Do the cleaning in a copy, or better, in Power Query where every step is recorded. The original is the only record of what the field actually reported.
Two findings are worth keeping in mind before trusting any spreadsheet, including your own.
Panko’s review of spreadsheet audits (Raymond R. Panko, 1998) found errors in the large majority of operational spreadsheets examined, with cell error rates in line with error rates in other human activities of similar complexity. The reassuring belief that a spreadsheet is correct because it has been used for a long time does not hold up.
Ziemann and colleagues (Mark Ziemann et al., 2016) found that roughly one fifth of published genomics papers with supplementary Excel files contained gene names silently converted into dates by autocorrect, SEPT2 becoming 2 September and so on. The lesson generalises: Excel changes data it thinks it recognises, and it does so without asking. Import any identifier column as text, and check it after.
12.4 What Excel Does Well, and Where It Stops
Excel is unmatched for looking at data. A dataset can be sorted, filtered, summarised, and charted in under a minute with no code, and the result is visible immediately. For exploring a new file, checking whether the numbers look plausible, or building a summary a colleague can open without installing anything, nothing else is as quick.
The limits are real, and they arrive sooner than people expect.
| Limit | What happens |
|---|---|
| Row ceiling | 1,048,576 rows per sheet. A season of half-hourly sensor readings from 40 fields passes this. |
| No record of what was done | A cleaned sheet does not remember which cells were edited by hand |
| Repeat cost | Next month’s file means repeating every manual step |
| Statistical depth | The ToolPak covers t-tests, ANOVA, correlation, and linear regression; it stops there |
| Review difficulty | Logic is hidden inside cells, so errors are hard to spot in review |
Every one of those is a reason the rest of this book runs in R, where the analysis is a script, the script is the record, and rerunning it on next season’s file costs one command. The closing section of this topic, From Excel to R, makes the crossing explicit by running the same analysis in both and comparing the output.
The practical answer is not one tool or the other. Data arrives in Excel, gets inspected in Excel, and moves to R for anything that has to be defended, repeated, or scaled.