1  Getting Started with Excel

This chapter covers what Excel is, how to organize a workbook, and the mechanics of cells, formulas, and references — the foundation everything else in the module builds on.

Learning Objectives

By the end of this chapter and the next, you will be able to:

Excel skills

  • Navigate and organize Excel workbooks
  • Use formulas, cell references, and functions like SUM
  • Compute descriptive statistics with Excel’s built-in functions
  • Use conditional and lookup functions (IF, COUNTIF, VLOOKUP, XLOOKUP)
  • Distinguish wide from long data, and reshape it
  • Build PivotTables and PivotCharts, and create clear, effective charts
  • Write reproducible workbooks others can follow

Most of you have used Excel before — the grid of cells where you type numbers and add things up. That is not wrong, but it is a thin way to think about it.

Excel is a spreadsheet program. The core idea is that every cell holds either a value (a number or text) or a formula that computes a value from other cells — and when an input changes, every formula that depends on it updates automatically.

  • Excel for Dummies — Microsoft 365 Excel for Dummies, the opening chapters on the Excel interface and workbook basics.
  • Microsoft Support — Excel for Windows training, Microsoft’s own free tutorial hub.
  • Video (beginner overview) — How to use Excel for beginners — a short tour of entering data, formatting, and the interface.

1.1 File Formats and File Management

In this class, we will read in data in two formats:

  • .xlsx — The default modern Excel format. Holds multiple worksheets, formulas, formatting, charts, and PivotTables. Save your working files as this. Note that .xls is a pre-2007 format.
  • .csv — Comma-separated values: plain text, one table, one row per line, values separated by commas. This is generally preferred for raw data because it can be read by most statistical software (notably R). Whereas R has difficulty reading Excel files.

Excel will readily open a .csv file. However, it sometimes tries to be too helpful and will convert data to a format that it thinks is appropriate (e.g., turning SEPT1 into the date 1-Sep, or stripping leading zeros off a farm ID). When this might happen, use Data → Get Data → From Text/CSV, which lets you set column types explicitly, instead of double-clicking the file.

1.2 Workbook Structure: Thinking in Sheets

A workbook is one Excel file; it holds one or more worksheets (“sheets” or “tabs”). A habit worth building early is deciding deliberately how to split work across sheets — and that starts with one question you should ask every time: who is the audience for this workbook? Three answers, each shaping how you build it:

  • Just you. A scratchpad — quick numbers for today’s decision, or exploratory work. You can get away with fewer sheets, terser labels, less formatting. But be honest: “just for me” files have a habit of becoming “I need to send this to my boss tomorrow” files, so if there is any chance it will be shared, build it as if it will be.
  • Internal colleagues. A file for a co-worker, your boss, or the TA. Now you want a README, clear labels, consistent structure, and enough formatting to navigate without asking you questions. Aim for “clean and self-explanatory,” not “pretty.”
  • External audiences. A file for clients, regulators, or the public. Here aesthetics genuinely matter — consistent formatting, polished tables, charts that stand on their own, often with the working sheets hidden. Every element reflects on you or your organization.

There is no single “right” level of polish — it depends on who opens the file. The mistake is not picking the wrong level; it is not thinking about it at all. Match the polish to the audience.

A complex workbook should open with a README or contents sheet. This explains what the workbook is, who made it and when, where the inputs came from, and what each of the other sheets contains. It can also hold links to those sheets, so a reader can jump straight to what they need.

If you are doing a lot of data cleaning, it is also good practice to store raw data in a separate sheet from the cleaned data. This way you can always come back to the raw data and see what is the result of your work versus the raw data.

Finally, it is best to do analysis and charts in separate sheets, rather than having one sheet with 32 charts and 16 summary statistics. Of course, you don’t need to have a separate worksheet where you calculate the mean, and another where you calculate the median, and another where you calculate the standard deviation. But generally, you don’t want too much analysis on the same sheet. So for example, you might have one sheet for summary statistics and another for hypothesis tests.

The principle is separation of concerns: raw data, cleaning, analysis, and presentation each have their own place.

1.3 Aesthetics

In addition to calculations, Excel allows you to shade cells, put borders around tables, add text that describes the data, and insert images. These can all create a much nicer aesthetic than a wall of numbers. However, they can also make subsequent analysis of the data frustrating. So how many of these flourishes you add depends on your audience.

One audience might simply want to view the data – in which case you should format it so that they can see the dataset as easily as possible – use borders around tables, use bold text for totals, etc. An example of this is the data showing the capacity of grain elevators in Manitoba. The aesthetic is pretty minimal, but the bolding and borders allow the user to quickly understand the dataset.

Download this workbook · Open online (File → Save a Copy to view formulas and edit)

Source: Manitoba Agriculture, Foresight and Analysis — Markets and Statistics.

Another audience might want to do specific calculations with the data. An example of this is the Saskatchewan Crop Planning Guide, which allows users to input their own costs, expected yields, and crop prices to calculate the profitability of different crops. It is a fairly complex workbook, with a sheet for every major crop in the province. However, it is pretty user-friendly and easy to navigate: it opens with a disclaimer, lists its assumptions on their own sheet, and uses an identical layout on every crop sheet, so once you can read one you can read them all.

Download this workbook · Open online (File → Save a Copy to view formulas and edit)

Source: Saskatchewan Ministry of Agriculture — Crop Planning Guide and Crop Planner (2026 edition).

Yet another audience might want to do more detailed analysis of the data, and would prefer it in a more raw form. An example is the yield data from the Saskatchewan government (which we will work with more throughout this book), which gives yields of major crops in each of Saskatchewan’s rural municipalities. This data has no adornments — one header row, one row per observation, starting in cell A1 — which is exactly what an audience that wants to run its own analysis needs.

Download this workbook · Open online (File → Save a Copy to view formulas and edit)

Source: Government of Saskatchewan — Saskatchewan’s Dashboard: RM Yields. Extract filtered to 2024–2025.

1.4 Cells and Formulas

Each cell in a worksheet has an address formed by its column letter and row number: A1, B2, AA35, and so on. A cell can contain:

  • A number: 42, 3.14, -1000
  • Text: Canola, Field 7, North field
  • A date (internally stored as a number): 2026-09-15
  • A formula: anything starting with =

A formula is a small calculation. It can be as simple as =2+2 or as complex as =IF(AND(B2>0,C2<100),VLOOKUP(D2,$F$1:$G$50,2,FALSE),"N/A"). Perhaps the most powerful aspect of Excel is that a formula does not just combine numbers you type in directly (like =2+2) — it can operate on the numbers held in other cells. For example, =A2+B2 adds together the contents of cells A2 and B2. And because the formula references those cells rather than fixed numbers, it re-computes automatically whenever they change: edit A2, and every formula that mentions A2 updates on its own. This is what makes a spreadsheet a living model rather than a static page of numbers.

Excel uses the standard mathematical operators — +, -, * (multiply), / (divide), and ^ (exponent) — and it evaluates them in the standard PEMDAS order: Parentheses, Exponents, Multiplication/Division, then Addition/Subtraction, working left to right within each level.

The worksheet below provides examples of simple operations.

Download this workbook · Open online (File → Save a Copy to view formulas and edit)

1.5 Relative and Absolute References

When you write a formula like =A1+B1 in cell C1 and then copy that formula down to C2, Excel automatically changes it to =A2+B2. This is called a relative reference — the cell addresses are interpreted relative to where the formula is. Usually this is what you want. If you have a column of sales in column A and a column of costs in column B, and you want profit in column C, you want each row to compute its own sales minus its own costs.

But sometimes you don’t want the reference to change. Suppose you have a fixed exchange rate in cell E1 (say, 1.35 CAD per USD) and you want to convert a column of USD prices in A2:A100 to CAD. So if you want the CAD prices in column B you would want every row in column A to be multiplied by E1.

The fix is to use an absolute reference by putting a dollar sign in front of the column letter, row number, or both

  • A1 — relative column, relative row (changes when copied in either direction)
  • $A1 — absolute column, relative row (column stays as A, row changes)
  • A$1 — relative column, absolute row (column changes, row stays as 1)
  • $A$1 — absolute column and row (neither changes; “locked”)

So in the exchange-rate example, you should write =A2*$E$1. Now when you copy the formula down, A2 becomes A3, A4, … but $E$1 stays as $E$1. Exactly what you want.

The workbook below has three worksheets (tabs), that demonstrate relative and absolute referencing.

Download this workbook · blank version to fill in yourself · Open online (File → Save a Copy to view formulas and edit)

1.6 Cell Formatting

In the formulas above we were working only with numbers. However, cells can also store text, dates, times, or currencies. Each cell has a number format, and it is important that the number format of the cell match the data it is storing. Examples of number formats are General, Number, Date, and Currency. Excel will usually default to formatting a cell as General, but may autodetect when you enter information as, for example, a date and immediately change the format to date. You can alter the format of a cell by right-clicking on it and choosing Format Cells… (Figure 1.1 (a)), which opens a dialog where you pick a category and set its options.

(a) Right-click a cell and choose Format Cells…
(b) The dialog: cell A1 holds 100, but the Number category with two decimal places displays it as 100.00
Figure 1.1: Changing a cell’s number format. The value Excel stores is unchanged — only its appearance is.

Two formatting issues that are of immediate relevance are rounding and dates. It is best practice to round to the nearest whole number or the nearest meaningful digit – most audiences won’t care if you report average yields as 40.52 bushels per acre or 40.519834760. It is particularly important to ensure that dates are formatted properly, because you can perform operations on dates. For example, if you specify a day as September 7, 2027 and then you add seven to that date, you will get September 14, 2027. If that date is stored as text rather than as a date, the same calculation returns an error instead.

Importantly, formatting changes only how a value looks, not the value Excel stores and computes. A cell holding 0.45 can display 45%; a cell holding 1000 can display $1,000.00; but a formula referencing either still uses the plain underlying number.

Download this workbook · blank version to fill in yourself · Open online (File → Save a Copy to view formulas and edit)

1.7 Arrays and the SUM Function

Every formula so far has pointed at cells one at a time – =A2*B2, =B7*$B$4. That works when you are combining two or three numbers, but it falls apart quickly. If you have 500 fields and you want the total acres, you are not going to write =A2+A3+A4+ and so on for 500 rows.

What we want is a way to refer to a whole block of cells at once. In Excel that block is called an array, and you write it as two corner cells separated by a colon:

  • B5:B9 is the five cells running down column B from row 5 to row 9.
  • B5:D5 is the three cells running across row 5 from column B to column D.
  • B5:D9 is the rectangle with B5 in the top left and D9 in the bottom right – fifteen cells in all.

An array is just a set of cells, and most of the functions we use in this course take one as their input. SUM is the first of them. Hand it an array and it adds up everything inside:

=SUM(B5:B9)

The worksheet below has five fields of wheat. Column B gives the size of each field in acres and column C gives its yield in bushels per acre, so multiplying the two gives the bushels each field produced. The total acres is =SUM(B5:B9) and the total production is =SUM(D5:D9). Column E shows the formula behind each cell in column D so you can see what is going on.

Download this workbook · blank version to fill in yourself · Open online (File → Save a Copy to view formulas and edit)

Two things are worth noticing. You can hand SUM more than one array by separating them with commas, and you can mix in plain numbers – =SUM(B5:B9,40) adds forty acres of summerfallow that never made it into the table. And an array can run across a row as easily as down a column, though =SUM(B5:D5) here adds acres to a yield to a bushel count, which is arithmetic that means nothing. Excel will not stop you.

The mistake to watch for is a range that does not cover what you think it does. If you add three new fields at the bottom of the table, the formula =SUM(B5:B9) will keeps totalling only the first five. When you click on a cell containing a SUM, Excel outlines the cells it is actually adding – look at that outline and check it matches the data.