Excel how-to guides
Short, exact answers to the jobs people search for. Every formula on these pages has been checked in real Excel, and each guide links to a lesson where you can practice it on fresh data.
Calculations
- How to calculate percentage changeWork out percentage increase or decrease in Excel with one short formula, plus the two mistakes that give the wrong answer.
- How to calculate the percentage of a totalShow each row as a share of the total, and lock the total with $ so the formula fills down correctly.
- How to sum cells that meet a conditionAdd up only the rows you care about with SUMIFS, such as sales for one region, with one or several conditions.
- How to calculate a weighted averageCalculate a weighted average with SUMPRODUCT and SUM, for grades, prices or anything where some items count more.
- How to make a running totalBuild a running (cumulative) total with one formula and a range that grows as you fill it down.
- How to rank numbersRank scores or sales from highest to lowest with RANK.EQ, and see how ties are handled.
- How to round to the nearest 5 cents, 10 or 100Round prices and quantities to a step, not just decimal places, with MROUND, CEILING and FLOOR.
Lookups
- How to look up a value from another tableFind a price, name or department by its code with XLOOKUP, with the VLOOKUP and INDEX/MATCH versions too.
- How to do a lookup with two criteriaLook up a value that depends on two columns, such as a product and a size, with XLOOKUP or SUMIFS.
- How to find the most recent entry for an itemFind the latest price, status or reading for an item in a log, by making XLOOKUP search from the bottom.
- How to replace #N/A errorsShow a friendly message instead of #N/A when a lookup finds nothing, with XLOOKUP, IFNA or IFERROR.
Cleaning data
- How to find duplicatesFlag duplicate rows with COUNTIF, so you can review them before removing anything.
- How to count unique valuesCount how many different values a list contains, with COUNTA and UNIQUE, or SUMPRODUCT in older Excel.
- How to remove extra spacesStrip leading, trailing and double spaces from imported text with TRIM, so lookups and duplicates work.
- How to convert numbers stored as textFix numbers that won’t add up because Excel sees them as text, with VALUE or a quick multiplication.
Text
- How to combine first and last namesJoin text from several cells into one, such as first and last names, with &, CONCAT or TEXTJOIN.
- How to split text in Excel with a formulaSplit names, codes or emails at a space, dash or @ with TEXTBEFORE, TEXTAFTER and TEXTSPLIT.
- How to count cells that contain specific textCount cells that contain a word anywhere in them, using COUNTIF with wildcards.
Dates and times
- How to calculate hours workedTurn start and end times into hours you can pay, including shifts that end after midnight.
- How to calculate age or years of serviceCount complete years between two dates with DATEDIF, for ages, tenure and anniversaries.
- How to add working days to a dateSet due dates that skip weekends with WORKDAY, and count working days between dates with NETWORKDAYS.