Find and fix what's wrong in your spreadsheets

The core Excel terms in plain language: workbook, worksheet, cell, cell reference, range, formula, function, and more, each linked to a fuller guide.

What an Excel formula is, its four parts, the order of operations, and the formula bar, plus where to go when a formula breaks or returns an error.

Range in Excel means two things: a cell range is a block like A1:B10 that a formula reads, and the statistical range is =MAX(range)-MIN(range).

The dollar sign is the whole difference: A1 adjusts when copied, $A$1 stays locked, $A1 and A$1 lock one half. How each behaves, plus F4 and R1C1.

A glossary of the symbols in an Excel formula: the arithmetic, comparison, text, and reference operators, what each one does, and the order Excel runs them in.

A named range gives a cell or range a plain name, so =SUM(Sales) can replace =SUM(B2:B5). How to create one, the naming rules, scope, and how names break.

Add, subtract, multiply, and divide in Excel with a formula: type = then the cells with + - * / between them. Excel has no SUBTRACT function.

Reference another sheet with =Sheet2!B2, another workbook with =[Budget.xlsx]Annual!C10, and many sheets with a 3-D reference. Syntax, quoting, how links break.

How to write IF formulas in Excel, nest multiple IF statements safely, and when IFS is cleaner. And the boundary mistakes that return wrong numbers, not errors.

AND is TRUE only when every condition holds, OR when any does, NOT reverses a result. Put them in an IF's logical test to weigh several conditions at once.

How COUNTIF and SUMIF work, how COUNTIFS and SUMIFS handle multiple criteria, and the quiet reasons a count or total comes back as 0 or wrong.

COUNT counts numbers only, COUNTA any non-empty cell, COUNTBLANK the empty ones. A formula returning empty text is non-blank to COUNTA, blank to COUNTBLANK.

How SUMPRODUCT multiplies ranges and adds up the products, conditional totals with multiple criteria, SUMPRODUCT vs SUMIFS, and why it returns 0 or #VALUE!.

How ROUND, ROUNDUP, ROUNDDOWN, and MROUND change stored values, what negative num_digits does, rounding to the nearest 5 or 100, and ROUND vs formatting.

How to use =TEXT(value, format_text): the format codes worth knowing, converting dates to text for labels, and why TEXT results no longer calculate.

Four ways to join text in Excel: =A2&" "&B2, CONCAT for ranges, TEXTJOIN for delimiters, legacy CONCATENATE, plus names, columns, dates, and merged cells.

How to pull part of a text string in Excel with LEFT, RIGHT, MID, LEN, and TRIM, plus FIND and SEARCH to extract a substring before or after a delimiter.

Array formulas explained: what they are, how dynamic arrays spill results into neighboring cells, and when you still need Ctrl+Shift+Enter and the braces.

AutoSum, the one-click sigma button on Excel's Home tab, writes a SUM for the numbers above a cell. Press Alt+=, then check the range it guessed before Enter.

How to calculate a percentage in Excel: percent of a total with =part/total, percent change with =(new-old)/old, and formatting a value so 0.25 displays as 25%.

MATCH finds a value's position, INDEX returns the value at that position. Together they make a lookup that works in any version of Excel, in any direction.

MATCH returns the position of a value within a row or column, not the value itself. How match_type works, the approximate-match trap, wildcards, and XMATCH.

XLOOKUP looks up a value and returns from any column, with an exact match and a built-in not-found result. How to use it, and why it beats VLOOKUP.

HLOOKUP searches the top row of a table and returns a value from the row you number below it. The syntax, exact vs approximate match, and the errors it throws.

VLOOKUP vs XLOOKUP: XLOOKUP looks any direction, matches exactly by default, and handles a no-match itself. When to use each, and the same lookup both ways.

What the Excel error values mean, from #REF! to #SPILL!, how to find every error cell in a workbook in one pass, and when it is safe to ignore one.

Excel formula not calculating or showing as text? Manual calculation mode, Text-formatted cells, Show Formulas, and a stray apostrophe, each with its fix.

Why your VLOOKUP isn't working and returns #N/A: a missing exact-match FALSE, text stored as numbers, stray spaces, or the wrong column. Each fix, step by step.

A circular reference makes Excel calculate a formula from its own result. Here's how to find every one, fix it, and decide when to allow it on purpose.

The reliable ways to combine multiple Excel files or sheets into one: Power Query for many files, Consolidate for numbers, Move or Copy for sheets — and how to check nothing was lost.

How to highlight duplicates, delete them with Data > Remove Duplicates, what the column checkboxes really remove, and how to check before deleting.

Remove blank rows in Excel safely: Go To Special then Delete Sheet Rows, filter a column for (Blanks), or sort the blanks to the bottom, without losing real data.

How to use Data > Text to Columns: the wizard step by step, delimiter choices, the Date and Text column formats, and the overwrite catch.

How to split a cell in Excel: Text to Columns for delimited data, formulas and Flash Fill for names, and unmerging the merged cells that fake a split.

What Flash Fill does, the Ctrl+E shortcut, the worked name example, the enable checkbox under File > Options, and why it fills values, not formulas.

Transpose rows and columns in Excel: Copy the range then use the Transpose paste option for a static copy, or enter =TRANSPOSE(range) for a live array that spills and updates.

How to alphabetize a column in Excel, sort by date or a custom list, run a multi-level sort, and keep rows together so values do not detach from them.

How to turn on AutoFilter in Excel, filter by value with the header dropdown, filter by text, number, date, or color, and clear or reapply filters.

Data validation in Excel restricts what a cell accepts via Data > Data Validation. Only a Stop error alert blocks a typed entry; paste can bypass it.

Create a drop-down list in Excel with Data > Data Validation and Allow set to List, from typed entries or a range. Plus how to edit, extend, and remove one.

To move a column in Excel, select it, then hold Shift and drag its edge to drop it between two columns, or Cut it and choose Insert Cut Cells where you want it.

Excel dates are serial numbers, and the format only changes the display. How to change date formats, custom codes like dd/mm/yyyy, and DATEVALUE for text dates.

The working set of Excel date formulas: TODAY, DATE, DAY, MONTH, YEAR, WEEKDAY, DATEDIF, EOMONTH and NETWORKDAYS, with worked examples and the text-date trap.

How to convert text to numbers in Excel: Convert to Number, VALUE, Paste Special. Plus when text is right: leading zeros and 16-digit codes.

Excel usually rounds only the display, not the stored value. Fix it by widening the column, clicking Increase Decimal, or adding decimals in Format Cells.

How to apply conditional formatting in Excel: highlight cells greater than a value, flag duplicates and dates, add data bars, and write formula rules.

Merge and unmerge cells in Excel: the Merge & Center menu, Merge Across vs Merge Cells, the upper-left rule for what survives, and how the web unmerges.

Wrap text in Excel with Home > Alignment > Wrap Text, add a manual line break with Alt+Enter, and fix wrapped lines that hide in a merged or fixed-height row.

Add strikethrough in Excel with Ctrl+5, or Format Cells > Font > Strikethrough. It only styles the text, so a struck-out number still feeds every total.

How to freeze the top row in Excel, freeze multiple rows or columns with View > Freeze Panes, unfreeze them, and why the Mac path looks different.

To hide columns or rows in Excel, select their headers, right-click, and choose Hide. Select the headers on both sides of the gap and choose Unhide to show them again.

Every cell is locked by default, but locking does nothing until you protect the sheet. Unlock the cells you want editable, then choose Review > Protect Sheet.

To insert a checkbox in Excel for Microsoft 365, select the cells and choose Insert > Checkbox. A ticked box stores TRUE in its cell, a cleared box FALSE.

How to create a pivot table in Excel: insert one, drag fields into Rows and Values, fix a pivot that counts instead of sums, refresh it, and add a slicer.

SUBTOTAL totals a range but skips rows a filter hid. function_num picks the operation, and codes 1-11 keep manually hidden rows while 101-111 drop them.

Add a sparkline in Excel: select a cell, choose Insert > Sparklines, and pick Line, Column, or Win/Loss over a data range. A tiny chart inside one cell.

Goal Seek runs a formula backward. On the Data tab pick What-If Analysis then Goal Seek, and set the formula cell, a target value, and the one input cell to change.