How-To Guides

Build INDEX MATCH formulas in Excel for lookups VLOOKUP can’t handle, including left-lookups and two-way row-and-column lookups in a single formula.

Build an Excel PivotTable from raw data — what the Rows, Columns, Values and Filters boxes do, and how to fix a pivot showing Count instead of Sum.

Use SUMIF in Excel to total matching cells fast, get the three arguments in the right order, and fix the top cause of a SUMIF formula returning 0 wrongly.

Count cells in Excel the right way — COUNT, COUNTA, COUNTBLANK and COUNTIF explained, plus why COUNT returns 0 when your numbers are stored as text.

Join text in Excel with the ampersand or TEXTJOIN, skip blank cells, and stop dates and currency turning into plain numbers when you combine them.

Freeze rows and columns in Excel with Freeze Panes, pin both at once, and fix the option when it is greyed out or ends up freezing the wrong rows entirely.

Remove blank rows in Excel safely, avoid the Go To Special trap that deletes partial records, and fix rows that look empty but refuse to delete.

Split cell contents in Excel with Text to Columns, Flash Fill or TEXTSPLIT — without overwriting the column beside it or losing your leading zeros.

Make a graph in Excel, choose the chart type that fits your question, avoid misleading axes and pie charts, and build charts that update themselves.

Sort data in Excel safely, sort by multiple columns and custom orders, and fix sorting that is greyed out or puts numbers in the wrong order.