Free Excel & Google Sheets guides
Excel formula guides, minus the headache
Clear, example-led guides to the formulas people search for most, with the exact syntax, common errors, and fixes. Or skip the reading and let ExcelPerfect write the formula for you.
Lookup & reference
How to Use VLOOKUP in Excel (Step-by-Step, with Examples)
VLOOKUP is the function people reach for when they need to find a value in a table and pull back a matching value from another column: looking up a price by product code, an email by name, a salary by employee ID.
Read guideHow to Use XLOOKUP in Excel (the Modern VLOOKUP)
XLOOKUP is the modern lookup function in Excel. It replaces VLOOKUP, HLOOKUP, and most INDEX/MATCH formulas with one cleaner function.
Read guideHow to Use INDEX MATCH in Excel (Look Left and Right)
INDEX/MATCH is the lookup combo power users prefer over VLOOKUP. MATCH finds the position of a value, and INDEX returns whatever sits at that position in another column.
Read guideVLOOKUP vs XLOOKUP in Excel (Which Should You Use?)
VLOOKUP and XLOOKUP both find a value in a table and return a matching value, but XLOOKUP is the modern rewrite: simpler, more flexible, and harder to break.
Read guideHow to Use HLOOKUP in Excel (Search Across a Row)
HLOOKUP is the horizontal cousin of VLOOKUP. It searches across the top row of a table for a value, then returns a result from a row beneath it, which is perfect for data laid out left to right such as months or quarters across columns.
Read guideHow to Use XMATCH in Excel (the Modern MATCH)
XMATCH is the modern replacement for MATCH. It returns the POSITION of a value in a list, which you then feed to another function such as INDEX to pull back the matching result.
Read guideHow to Use OFFSET in Excel (Dynamic Ranges)
OFFSET returns a reference that is shifted a set number of rows and columns from a starting cell, and can be a single cell or a whole block. It is the classic tool for building ranges that resize themselves as data is added.
Read guideHow to Use INDIRECT in Excel (Text to References)
INDIRECT turns a text string into a real cell reference. Instead of pointing Excel at A5 directly, you hand it the text "A5" and INDIRECT resolves it into the live cell, so the reference can be assembled on the fly.
Read guideHow to Use CHOOSE in Excel (Pick from a List)
CHOOSE returns one value from a list based on a position number. Give it 2 and a list of options, and it hands back the second one, which makes it a clean way to turn a number into a label, a month, or a quarter.
Read guideLogical
How to Use the IF Function in Excel (with Multiple Conditions)
The IF function returns one value when a condition is true and another when it is false. It is the foundation of decision-making in spreadsheets: flagging, scoring, categorising, and conditional math.
Read guideHow to Use IFERROR in Excel (Hide #N/A and #DIV/0!)
IFERROR catches any error a formula produces and returns a value of your choice instead (a friendly "Not found", a 0, or a blank cell), so dashboards stay clean.
Read guideHow to Use IFS in Excel (Cleaner Multi-Condition Logic)
The IFS function checks several conditions in order and returns the value for the first one that is true. It is the modern, readable replacement for stacking many IF functions inside each other.
Read guideHow to Use SWITCH in Excel (Match One Value to Many)
SWITCH compares one expression against a list of values and returns the result that matches the first one it finds. It is the clean way to translate codes, abbreviations, or numbers into readable labels.
Read guideHow to Use AND and OR in Excel (Combine Conditions)
AND and OR let you test more than one condition at the same time. AND is TRUE only when every condition is true; OR is TRUE when at least one condition is true. Both return TRUE or FALSE.
Read guideNested IF Statements in Excel (Step-by-Step Guide)
A nested IF places one IF inside another so a single formula can choose between three or more outcomes. Each IF handles one test, and its "false" slot holds the next IF in the chain.
Read guideHow to Use IFNA in Excel (Catch Only #N/A Errors)
IFNA catches the #N/A error specifically and returns a value of your choice instead, while letting every other error type still surface. It is the precise tool for cleaning up lookups that miss.
Read guideMath & aggregation
How to Use SUMIF in Excel (Sum with a Condition)
SUMIF adds up the numbers in a range that meet a single condition: total sales for one region, the sum of all "Paid" invoices, every value over 100.
Read guideHow to Use COUNTIF in Excel (Count with a Condition)
COUNTIF counts how many cells in a range meet a single condition: how many invoices say "Paid", how many scores are over 100, how many names start with S.
Read guideHow to Use SUMIFS in Excel (Sum with Multiple Criteria)
SUMIFS adds up numbers that meet two or more conditions at the same time: sales for the West region in January, paid invoices over 100, amounts within a date range.
Read guideHow to Use COUNTIFS in Excel (Count on Multiple Criteria)
COUNTIFS counts how many rows meet two or more conditions at the same time: orders that are both West and Paid, values between 50 and 100, open tickets raised this year.
Read guideHow to Use SUMPRODUCT in Excel (Weighted Sums and Counts)
SUMPRODUCT multiplies ranges together cell by cell and then adds up the results. That one idea powers two everyday tasks: weighted totals, like quantity times price, and conditional counts or sums that would otherwise need an array formula.
Read guideHow to Use AVERAGEIF in Excel (Average with a Condition)
AVERAGEIF returns the average of the numbers in a range that meet a single condition: the average sale in the West region, the average score above 50, the average of all non-zero entries.
Read guideHow to Use MAXIFS and MINIFS in Excel (with Examples)
MAXIFS and MINIFS return the largest or smallest number in a range that meets one or more conditions: the biggest order in the West region, the lowest score for a "Paid" customer, the highest sale within a date range.
Read guideHow to Round Numbers in Excel (ROUND, ROUNDUP, MROUND)
Rounding in Excel changes the actual value, not just how it looks. ROUND rounds to a set number of decimal places, ROUNDUP and ROUNDDOWN always push in one direction, and MROUND snaps to the nearest multiple, such as the nearest 5 or 0.05.
Read guideHow to Use RANK in Excel (RANK and RANK.EQ Explained)
RANK tells you where a number sits in a list: 1st, 2nd, 3rd, and so on. It is how you turn a column of scores, sales, or times into a leaderboard, ranking from highest to lowest or lowest to highest.
Read guideHow to Calculate Percentage in Excel (Formulas and Tips)
Percentages in Excel come down to a few simple formulas plus the Percent number format. The format does the heavy lifting: it multiplies the cell by 100 and adds the % sign, so a result of 0.25 displays as 25%.
Read guideText
How to Concatenate in Excel (Join Text from Cells)
Concatenating means joining text from several cells into one: combining first and last names, building an address line, or merging codes into a single key.
Read guideHow to Use the TEXT Function in Excel (Format Codes)
The TEXT function turns a number, date, or time into text formatted exactly the way you want: $1,250.00, 25%, 2026-06-30, or an ID with leading zeros. You hand it a value and a format code, and it returns a text string that looks the way you specified.
Read guideHow to Use LEFT, RIGHT, and MID in Excel (Examples)
LEFT, RIGHT, and MID are the three functions for pulling pieces out of text. LEFT takes characters from the start, RIGHT takes them from the end, and MID grabs a chunk from the middle starting at a position you choose.
Read guideHow to Use TEXTJOIN in Excel (Join Text with a Delimiter)
TEXTJOIN combines text from many cells into one string, placing a delimiter you choose between each item: a comma between names, a slash between dates, or a line break between address lines. It is the modern, flexible way to merge a list.
Read guideHow to Use SUBSTITUTE in Excel (Find and Replace Text)
SUBSTITUTE swaps one piece of text for another inside a cell: changing "Mr." to "Ms.", turning dashes into spaces, or stripping out unwanted characters. It matches by content, so it finds your text wherever it appears.
Read guideHow to Use FIND and SEARCH in Excel (Find Text Position)
FIND and SEARCH both return the position of one piece of text inside another, counting from the left. You use that number to know where a separator sits, then feed it to LEFT, RIGHT, or MID to split the text.
Read guideHow to Use TRIM in Excel (Remove Extra Spaces Fast)
TRIM removes extra spaces from text, leaving only single spaces between words and none at the start or end. It is the first thing to try when data imported from another system has invisible spaces that break lookups and comparisons.
Read guideDate & time
How to Use DATEDIF in Excel (Age and Tenure, with Examples)
DATEDIF calculates the difference between two dates and returns it as a whole number of years, months, or days. It is the function you reach for to work out someone's age, how long an employee has worked, or how many months are left on a contract.
Read guideHow to Use EOMONTH in Excel (End of Month and Due Dates)
EOMONTH returns the last day of a month, a set number of months before or after a date you give it. It is the go-to function for month-end reporting, billing cycles, and payment terms like "due at the end of the following month".
Read guideHow to Use NETWORKDAYS in Excel (Business Days, with Examples)
NETWORKDAYS counts the number of working days between two dates, automatically skipping Saturdays and Sundays. It is the function for measuring turnaround time, project duration, or SLA windows in business days rather than calendar days.
Read guideDynamic arrays
How to Use FILTER in Excel (Spill Results, with Examples)
FILTER returns only the rows from a range that meet the conditions you set, spilling the results into the cells below and to the right automatically. It replaces the old routine of copying, sorting, and deleting rows by hand, and it updates live as your data changes.
Read guideHow to Use SORT in Excel (Live Sorting, with Examples)
The SORT function arranges a range in order and spills the sorted result into new cells, leaving your original data untouched. Unlike the Sort button on the ribbon, it recalculates automatically, so the output stays sorted as the source data changes.
Read guideHow to Use UNIQUE in Excel (Distinct Lists, with Examples)
UNIQUE returns the distinct values from a range, removing duplicates and spilling the clean list into new cells. It is the live alternative to the Remove Duplicates button: the result updates automatically as your data grows, and it never touches the original.
Read guideData cleanup
How to Remove Duplicates in Excel (3 Simple Ways)
Duplicate rows creep into every spreadsheet. Excel gives you three ways to deal with them: a live UNIQUE formula, the one-click Remove Duplicates button, and COUNTIF to flag dupes without deleting anything.
Read guideAbsolute vs Relative References in Excel (with F4 Tips)
Whether a formula keeps working when you copy it across a sheet comes down to one thing: cell references. A relative reference like A1 shifts as you fill the formula, while an absolute reference like $A$1 stays locked on the same cell. Getting this right is the difference between a formula that fills perfectly and one that returns garbage.
Read guideVBA & automation
How to Write a VBA Macro in Excel (Step-by-Step Guide)
A VBA macro is a small program you write inside Excel to automate work you would otherwise do by hand: formatting reports, looping through rows, cleaning data, or building a file from scratch. VBA (Visual Basic for Applications) is the language built into Excel, and every macro lives inside a Sub procedure.
Read guideHow to Record a Macro in Excel (Step-by-Step Guide)
The Macro Recorder is the easiest way to automate Excel without writing a line of code. You press record, do the task once by hand, and Excel watches every click and keystroke, then turns it into a reusable VBA macro you can replay any time.
Read guideLaunching soon
Get this the moment we launch
ExcelPerfect turns plain English into the exact formula, and it’s almost here. Join the waitlist for early access and an extended free trial.
=SUMIF(A:A,"Paid",B:B)No spam, ever. Just one email the day we launch. By joining you agree to our Privacy Policy.
You’re on the list
We’ll email the moment ExcelPerfect goes live. Early members get an extended free trial.