15 essential Excel formulas and functions, with Copilot prompts

Accurate and reliable data starts with the right foundations. Reduce manual errors, catch mistakes early, and build logic that holds up every time data changes using Microsoft Excel formulas. From simple totals to multi-condition summaries and cross-table lookups, each is built into Excel and available in the browser without installation.

Explore 15 essential Excel formulas and functions, from cleaning up imported data to analyzing and summarizing datasets. Each section includes real-world examples and Copilot in Microsoft Excel actions for getting started without memorizing syntax.

Data cleanup for accurate formulas

Getting data into a consistent, usable state is the first step before running any analysis. Use these functions to remove duplicates, correct spacing, and standardize text, so that formulas, lookups, and PivotTable summaries produce reliable results.

• TRIM: removes extra spaces from the start and end of a text string, and reduces multiple spaces between words to a single space. Paste names or IDs from another system and TRIM cleans up the spacing inconsistencies that break VLOOKUP formulas and COUNTIF counts.

• CONCAT and TEXTJOIN: join text from separate columns into one field. TEXTJOIN adds a chosen separator between values, making it faster to build full addresses, product labels, or display names from split source data.

• LEFT and RIGHT: pull a fixed number of characters from either end of a text string. Use LEFT to extract a region code from the start of a product ID, or RIGHT to isolate a file extension or date component from the end of a field.

• SORT: returns a reordered version of a range without touching the original data. Sort a project planning list by due date, or reorder an inventory tracker by stock level before reviewing what needs replenishing.

• FILTER: returns only the rows that meet a set condition. Pull active accounts from a full client list, show only overdue tasks from a project tracker, or isolate one month of entries from a full-year dataset.

• Remove Duplicates: eliminates repeated rows from a selected range in one step. Run it on a contact list before a campaign send, on a merged customer export to get unique records, or on a keyword list pulled from multiple SEO tools.

Try these Copilot example prompts

Suggest a useful way to remove duplicates and standardize the text formatting in this table.Click to copy this Copilot prompt

Totals, averages, and counts in spreadsheet data

Totals, averages, counts, and the highest and lowest values in a range are commonly used in data spreadsheets. The following seven functions can be applied to numeric ranges in a business spreadsheet.

• SUM: adds every value in a selected range, from a personal budget planner tracking monthly outgoings to a balance sheet reconciling assets and liabilities.

• AVERAGE: calculates the mean across a range. Track average order values over a quarter, average response times across a support team, or the mean score from a survey response column.

• MIN and MAX: return the lowest and highest values in a range. Pair them to help spot outliers in an expense dataset, check that data entry stays within expected bounds, or surface the best and worst performers in a sales column without sorting the whole sheet.

• COUNT and COUNTA: COUNT tallies cells containing numbers; COUNTA counts any non-blank cell. Use COUNTA on a sign-up column to see how many responses came in, or COUNT on a revenue column to check how many transactions have been entered so far.

• COUNTIF: counts cells that meet a single condition. How many invoices have a status of "Paid"? How many survey responses selected option B? COUNTIF returns the number without needing to filter or sort first.

• SUMIF: totals values in one column based on a condition in another. Add up hours logged by one team member across a timesheet, total costs from a single supplier in a business budgeting spreadsheet, sum support tickets closed by one agent in a week, or total expenses by category from a travel claim.

• RANK.EQ: returns the position of a number within a list, highest to lowest or the reverse. Rank salespeople by revenue, order campaign results by conversion rate, or build a leaderboard from a results column without altering the underlying data.

Try these Copilot example prompts

Which formula should I use to count how many tasks in this sheet are marked complete?Click to copy this Copilot prompt

Conditional logic and error handling

Logic formulas turn static cells into conditional ones, and keep a spreadsheet readable when something goes wrong. Set a formula to return different results based on what the data contains, combine multiple conditions into a single test, or replace formula errors with a clean value instead of a broken cell.

• IF: returns one result when a condition is true, and another when it is false. Flag overdue invoices in a project management tracker, label survey scores as "Above target" or "Below target", or mark tasks in a to-do list as complete based on the value in a status column.

• IFERROR: catches any error a formula returns and replaces it with a specified value instead, such as a dash, a zero, or a plain-language note. Wrap it around a VLOOKUP formula that might not always find a match to help keep results readable.

• AND and OR: combine multiple conditions into a single logical test. AND returns true only when every condition is met; OR returns true when at least one is. Nest either inside an IF formula to build more precise rules, such as flagging a record only when both the region and the status match a target.

Try these Copilot example prompts

I want column C to show 'On track' when the value in column B is above 80%, and 'Needs review' when it is not. What formula should I use?Click to copy this Copilot prompt

Data lookup and matching

Lookup formulas find a value in one table and return related information from another. Use them to pull customer names from order IDs, match product codes to price tiers, map keywords to intent categories from a taxonomy sheet, or enrich an invoice generator with account details from a separate list.

• VLOOKUP: searches a table from left to right and returns a value from a specified column when a match is found. Still widely used in shared workbooks and legacy files.

• XLOOKUP: searches in any direction and can return a specified value when no match is found, making it more flexible than VLOOKUP. Available in Excel for the web.

• MATCH: finds the position of a value within a range. Pair it with INDEX to retrieve data from any column in a table, including columns to the left of the search column where VLOOKUP cannot reach.

• INDEX: retrieves a value from a specific position in a table. Paired with MATCH, it handles lookups that neither XLOOKUP nor VLOOKUP can resolve directly, such as returning a value based on two matching conditions across separate columns.

Try these Copilot example prompts

Write a VLOOKUP formula to match the product code in column A against the price list in columns D and E.Click to copy this Copilot prompt

Data analysis and summarization

SUMIFS and PivotTables handle analysis at scale in Excel. SUMIFS calculates totals across multiple conditions, and PivotTables group and summarize data into a view that is easy to read and share.

• PivotTable: summarizes and explores large datasets by grouping rows, calculating totals, and letting the view be rearranged without touching the source data. Use a PivotTable to compare monthly sales across regions in a business budgeting sheet, analyze traffic by source and device, or break down expenses by category in a Gantt chart-based project file. Chat with Copilot to help set up a PivotTable.

• SUMIFS: totals values that meet two or more conditions at once. Sum revenue for a specific product in a specific region, or total hours from a timesheet for one team member across a set week. Use it when a single condition is not enough to isolate the right rows.

Try these Copilot example prompts

Create a PivotTable to summarize sales by region and month from this data.Click to copy this Copilot prompt

Note: Copilot in Excel requires a Microsoft 365 subscription with Copilot included. Files must be saved to OneDrive or SharePoint with AutoSave turned on. Copilot works with .xlsx, .xlsb, and .xlsm files.

Start from a ready-made Excel spreadsheet template to apply these formulas to real data today. To see how Copilot in Excel supports analysis, charts, and data cleanup in a single workbook, explore five ways to create and analyze an Excel spreadsheet with AI.