Excel FormulasWhere to start
New to formulas? Read these two first, in order.
- How to write a formula in Excel: the equals sign, operators, the order of calculation, functions and copying down.
- Relative and absolute cell references: what the dollar sign does, and when you need it. More wrong answers come from a missing dollar sign than from any other cause.
Something has gone wrong? Excel formula errors and how to fix them goes through #N/A, #VALUE!, #REF!, #DIV/0!, #NAME? and the rest, with the formula that produces each and its cure.
Lookups
Fetching a value from another table is the task people most often need help with.
- INDEX and MATCH together, step by step builds the pair slowly, then shows lookups to the left, two-way lookups and lookups on two conditions.
- VLOOKUP or XLOOKUP puts the two side by side on the same data and helps you decide which to use in your office.
Everyday tasks
- Percentage formulas: percent of a total, percentage change, adding and removing a percentage, and the mistake of taking tax off the wrong figure.
- Date formulas: ages, days between dates, due dates, month ends, working days and the financial year.
- Cleaning and splitting text: spaces, capitals, splitting names and codes, and turning text into real numbers.
Excel and other programs
- Excel and Google Sheets: where formulas differ, for anyone who moves files between the two.
How to get the most from a guide
Open Excel beside the page. Type the small sample sheet into a blank workbook, or use data of your own with the same shape. Then type each formula yourself. Copying and pasting is quicker, but typing a formula once, and seeing it fail when a bracket is missing, teaches more than reading it ten times.
When a formula works, change it. Point it at a different cell. Remove a dollar sign and copy it down to see what breaks. Formulas are learnt by experiment.
This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.