New Design Basics for Everyone is open: the first cohort starts on the 1st. Learn more

Free LessonsData

Five Spreadsheet Formulas That Save Hours Every Week

A spreadsheet window with a highlighted column of cells

Most of the time we lose in spreadsheets is not spent on hard maths. It is spent copying, counting, tidying and looking things up by hand. Five formulas cover nearly all of it, and you can learn them before your coffee goes cold.

The five

FormulaWhat it doesUse it when
SUMIFAdds up values that match a ruleTotalling spend by category
COUNTIFCounts rows that match a ruleHow many orders are still open?
XLOOKUPFinds a value in another tableMatching names to email addresses
TRIMRemoves stray spacesCleaning data pasted from a website
TEXTJOINJoins cells with a separatorBuilding a list of tags or names

A worked example

Imagine a sheet of expenses with a category in column B and an amount in column C. To total everything in the Travel category, type this into any empty cell:

=SUMIF(B2:B200, "Travel", C2:C200)

Change the word in quotes to total another category, or point it at a cell that holds the category name so you can build a whole summary table in a minute.

Learn the formula once, then let the spreadsheet do the boring part forever.

Lesson 3, Data Basics

Try each formula on a copy of a real sheet from your own work. That is the fastest way to make it stick, and it is exactly how the Data Basics course is built.