A step-by-step guide to writing spreadsheet formulas that work: cell references, lookups, logical functions, text and date functions, and handling errors. Every technique is written for both Microsoft Excel and Google Sheets, and every exercise comes with a practice spreadsheet.
What the guide covers
Formulas and cell references
How a formula is put together, the difference between relative, absolute and mixed references, and what happens to each kind when you copy a formula into other cells.
Lookups
Finding a value in one table and returning the matching value from another, exact and approximate matches, and lookups that can return a result from any column.
Logical functions
IF on its own and with AND and OR, handling more than two outcomes, and counting or adding up only the rows that meet a condition.
Text functions
Joining and splitting text, trimming stray spaces, changing case, and pulling one part of a value out of a longer piece of text.
Date functions
How spreadsheets store dates, working out the time between two dates, adding days and months, and formatting dates so they display the way you want.
Error handling
What the common error values mean, tracing a formula back to the cell that causes a problem, and returning a clean result when a formula cannot calculate.
Excel and Google Sheets side by side
Where a step or a function works differently in the two programs, the guide shows both versions so you can follow along in whichever one you use.
Inside the guide
Every chapter follows the same pattern: the skill is explained, broken into steps, practiced in an exercise, and checked against a worked solution. These illustrations show the three kinds of page you will find in the PDF.
Illustrations represent the layout of guide pages. They are not reproductions of any software's interface.
What's in the .zip
Your .zip download
- PDF guideEach skill explained, then broken into numbered steps.
- Step-by-step instructions
- Annotated illustrations
- Practice exercises
- Worked solutionsEvery step, not only the final answer.
- Practice spreadsheetsA starter spreadsheet for each exercise, set up with the data the exercise uses. They are made to open in either Microsoft Excel or Google Sheets.
The software is not included. See Software required below.
Software required
This guide is written for Microsoft Excel and Google Sheets. You can follow it with either one. Neither program is included with the guide; you need your own access to the one you choose, obtained separately from its maker.
About software updates. Software makers update their programs, so menus and button locations can change between versions. When an update changes the steps, the guide is revised, and updated files are free to anyone who bought this guide.
How the guide works
This is a self-study guide. You read each step, carry it out in your own copy of the software, then do the exercises and compare your work with the worked solutions. There is no video, no live instruction, no tutoring, and no feedback on your work. Everything you get is in the .zip.
Is this guide a good fit?
Who it's for
- You enter data in spreadsheets but rarely write formulas beyond simple totals.
- You use formulas other people wrote and want to understand how they work.
- You switch between Excel and Google Sheets and want one guide that covers both.
- You prefer to work through written steps and check your own answers.
Who it isn't for
- You want macros, scripting, VBA or Apps Script. Those are not covered.
- You mainly need pivot tables and charts. Those are in Data Analysis with Pivot Tables & Charts.
- You want video lessons, live help, or someone to review your work.