Excel Formula Library
Browse ready-to-use formula templates for Excel, Google Sheets, SQL, and regex. Each formula includes a step-by-step breakdown, edge-case warnings, and real-world examples.
AI-generated and validated Excel formulas. Every formula is tested against edge cases before you paste it into your spreadsheet.
VLOOKUP With Multiple Criteria
Standard VLOOKUP only matches one column. Here's how to match on two or more criteria using INDEX-MATCH arrays.
INDEX MATCH Formula
INDEX-MATCH is the most powerful lookup formula in Excel. It's faster, more flexible, and more reliable than VLOOKUP.
XLOOKUP With Multiple Criteria
XLOOKUP simplifies multi-criteria lookups in Excel 365. No array formulas, no helper columns — just clean syntax.
VLOOKUP vs XLOOKUP
XLOOKUP is the modern replacement for VLOOKUP. Here's exactly when to use each — and why XLOOKUP wins in most cases.
SUMIFS With Multiple Conditions
SUMIFS lets you sum a range based on 2, 3, or more criteria. It's the workhorse formula for financial analysis and reporting.
COUNTIFS With Multiple Conditions
Count rows that match two or more criteria simultaneously. Essential for dashboards, KPI tracking, and data validation.
SUMPRODUCT for Conditional Calculations
SUMPRODUCT is the Swiss Army knife of Excel formulas. It handles weighted averages, complex conditions, and calculations SUMIFS can't.
Nested IF With Multiple Conditions
Nested IFs get messy fast. Here's how to write them cleanly — and when to use IFS or SWITCH instead.
IFERROR Formula
Stop ugly error messages from appearing in your spreadsheets. IFERROR catches any error and returns a clean fallback value.
Split Text With Formulas
Extract first names, last names, domains, or any delimited data using formulas instead of the Text to Columns wizard.
Dynamic Named Ranges With OFFSET
Stop updating range references manually. Dynamic named ranges grow and shrink automatically as your data changes.
Conditional Formatting With Formulas
Built-in conditional formatting rules are limited. Custom formulas let you highlight based on any logic you can write.
Calculate Date Differences
Find the exact number of days, months, or years between two dates. Including business days that skip weekends and holidays.
Percentage Change Formula
Calculate growth rates, month-over-month changes, and YoY comparisons. With proper handling for zero and negative baselines.
Remove Duplicates With Formulas
Extract a clean list of unique values without modifying your original data. UNIQUE for Excel 365, COUNTIF tricks for older versions.
Pivot Table Calculated Fields
Add custom calculations like profit margins, unit economics, and derived metrics directly inside your Pivot Table.
Weighted Average Formula
Regular AVERAGE treats all values equally. Weighted average accounts for importance — grades, portfolio weights, survey responses.
Data Validation Dropdown Lists
Dropdown lists prevent bad data entry and make spreadsheets more user-friendly. Here's how to create them — static and dynamic.
Moving Average Formula
Smooth out noisy data with moving averages. Essential for sales trends, stock analysis, and KPI dashboards.
INDIRECT Function
INDIRECT converts text strings into cell references. Use it to dynamically reference sheets, create flexible formulas, and build cascading dropdowns.
Concatenate Text in Excel
Combine first name + last name, build addresses, or merge any text. Three methods compared: &, CONCAT, and TEXTJOIN.
LET Function
Stop repeating the same calculation 3 times in one formula. LET defines variables you can reuse — making formulas shorter, faster, and readable.
LAMBDA Function
LAMBDA lets you create your own reusable functions in Excel — without writing a single line of VBA. Define it once, use it everywhere.
FILTER Function (Excel)
FILTER extracts rows that match your criteria and spills the results automatically. No helper columns, no manual filtering.
SORT Function (Excel)
SORT and SORTBY create live-sorted copies of your data. Change the source, the sorted output updates automatically.
UNIQUE Function (Excel)
UNIQUE returns a dynamic list of distinct values from a range. No more Remove Duplicates button or helper columns.
SEQUENCE Function
SEQUENCE creates dynamic arrays of numbers. Build date ranges, row numbers, time slots, and matrix scaffolding without typing.
SUBSTITUTE Function
SUBSTITUTE replaces text within a cell using a formula. Clean messy data, standardize formats, and fix text issues automatically.
OFFSET Function
OFFSET creates references that move and resize based on parameters. Essential for auto-expanding ranges, rolling calculations, and dynamic charts.
CHOOSE Function
CHOOSE picks a value from a list based on an index number. It's a cleaner alternative to nested IF statements for fixed options.
TRIM & CLEAN Functions
Data from imports, copies, and web scraping often has invisible junk characters and extra spaces. TRIM and CLEAN fix them.
TEXTJOIN Function
TEXTJOIN combines text from a range with any delimiter you choose. Replaces chained CONCATENATE or ampersand (&) formulas.
LEFT, RIGHT, MID Functions
Pull characters from the start, end, or middle of text. Essential for parsing product codes, IDs, and fixed-format data.
IF with AND/OR
Use AND and OR inside IF to test multiple conditions cleanly. Handle complex business rules without deeply nested formulas.
NPV and IRR Functions
NPV and IRR are the core functions for evaluating investments, comparing projects, and making capital budgeting decisions.
Sum Values with Criteria
The SUMIF function allows you to sum values in one column based on criteria in another column, simplifying data analysis.
Count Text Values in Excel
Counting cells that contain specific words or partial text can be crucial for data analysis. This formula makes it easy!
Count Dates with Conditions
Counting data based on date ranges and conditions can be complex, but this formula simplifies the process effectively.
Average If Conditional
Struggling to find averages based on conditions? This guide shows you how to average values in one column based on non-blank criteria in another.
Handle VLOOKUP Errors Gracefully
VLOOKUP can often lead to errors when no match is found. Using IFERROR allows you to display a user-friendly message instead.
IF with Multiple Conditions
Managing multiple conditions in Excel can be challenging, but with the IF function, you can easily return different values based on your criteria.
Nested IF Statements for Grading
Assigning grades based on scores can be complex, but nested IF statements simplify this process in Excel.
XLOOKUP with Fallback Message
Discover how to effectively use XLOOKUP to retrieve values while providing a custom message for missing data, enhancing your Excel skills.
Reverse Lookup with XLOOKUP
Need to find values to the left of a lookup column? Use XLOOKUP for efficient reverse lookups in your Excel sheets.
Calculate Date Difference
Understanding the time span between two dates can be crucial for various tasks. The DATEDIF function simplifies this process.
Count Business Days Between Dates
Counting business days between two dates can be tricky, especially with holidays. Use the NETWORKDAYS function to simplify this task.
Add Months to Date
Need to adjust a date by three months while keeping the same day? This formula will help you do just that.
Last Day of Month Formula
Use the EOMONTH function to easily determine the last day of the current month or a future month by specifying the number of months to add.
Extract Characters from Text
Need to pull specific characters from a text string? This guide shows you how to extract the first three characters and characters five to eight using Excel formulas.
Format Numbers and Dates
Formatting numbers and dates in Excel can enhance data presentation. This guide shows you how to display numbers as currency and format dates effectively.
Clean Text and Remove Spaces
Extra spaces and non-printable characters can clutter your data. This formula helps you clean it up effectively.
Join First and Last Name
Combining first and last names into a single cell can streamline your data management. Use this formula to achieve that effortlessly.
Extract Unique Values
Struggling with duplicate entries in your data? Use the UNIQUE function to easily extract a list of unique values.
Filter Active Rows
Filtering data by specific criteria can streamline your analysis. Use dynamic arrays in Excel 365 to easily return only active rows.
Sort Lists Alphabetically
Sorting data in Excel can help you organize information effectively. Discover how to sort lists alphabetically or based on another column's order.
Generate Sequential Numbers
Creating a sequence of numbers or dates in Excel can simplify data entry and organization. This guide shows you how to do it easily.
Sum Product with Criteria
Discover a powerful method to calculate sums of products based on specific conditions without the complexity of array formulas.
Dynamic Range with OFFSET
Struggling with static ranges? Discover how to use OFFSET and COUNTA to create a dynamic range that adjusts as you add data.
Dynamic Cell Reference
Changing a cell value can dynamically alter your references to different sheets or ranges, streamlining your data management.
Choose Function for Lookup
The CHOOSE function allows you to return different values based on a specified index number, making it perfect for lookup tables.
Calculate Percentile Rank
Understanding where a score stands in a list can be crucial. This guide shows you how to calculate the rank and percentile of a value in Excel.
Calculate Loan Payments
Understanding your loan payments is crucial for financial planning. This formula helps you calculate the monthly payment based on loan amount, interest rate, and term.
NPV and IRR Calculation
Understanding the profitability of investments is crucial; this guide shows you how to calculate NPV and IRR using Excel formulas.
Count Non-Empty and Blank Cells
Counting cells accurately is crucial for data analysis. This guide shows you how to count non-empty, numeric, and blank cells in Excel.
Find 2nd and 3rd Largest Values
Struggling to extract specific values from your data? This guide shows you how to find the second and third largest values and the smallest non-zero value in Excel.
Check Cell Type with IF
Understanding cell content is crucial for data analysis. This guide shows you how to use IF with ISNUMBER, ISTEXT, and ISBLANK.
Find Value Position in List
Discover how to locate a value's position in a list and use it to retrieve data from a different column effortlessly.
Week Number and Day Name
Understanding the week number and day name for a date can help in scheduling and planning. This formula provides both in one go.
Add Business Days to Date
Need to find a future date while skipping weekends and holidays? This formula will help you calculate business days easily.
Find and Extract Text
Struggling to locate specific text within a cell? This guide will help you find and extract it seamlessly.
Replace Text in Excel
Easily replace all instances of a word and modify text at specific positions in your Excel sheets.
Text Case Conversion in Excel
Struggling with text formatting? Discover how to easily convert text to title case, uppercase, or lowercase using Excel formulas.
Round, Ceiling, and Floor
Master the techniques to round numbers effectively with Excel's powerful functions for precise calculations.
Calculate Remainder in Excel
The MOD function allows you to easily calculate the remainder of a division, helping you identify even and odd numbers or group records effectively.
Transpose Data Easily
Struggling to switch rows and columns in Excel? This guide shows you how to transpose data effortlessly without the hassle of copy-pasting.
Crosstab Summary with SUMIFS
Transform your sales data into a crosstab summary by leveraging the power of SUMIFS, eliminating the need for PivotTables.
Year Fraction Calculation
Understanding the fraction of a year between two dates is crucial in finance, and this formula simplifies that process using a 360-day calendar.
Calculate Percentage in Excel
Understanding how to calculate percentages can simplify your data analysis. This guide covers total percentages, changes, and increases.
Calculate Percentage Change
Understanding percentage change is crucial for analyzing data trends. This guide shows you how to calculate it easily in Excel.
Cumulative Running Total
A running total helps you keep track of cumulative sums in your data. This guide will show you how to set it up easily in Excel.
Year Over Year Growth
Understanding revenue growth is crucial for businesses, and this formula helps you calculate it accurately, even in tricky situations.
Check Cell for Text
Struggling to check if a cell contains certain text? Use the IF function combined with SEARCH to easily return desired values.
Extract Numbers from Text
Struggling to isolate numbers from a text string? This formula will help you extract only the numeric digits efficiently.
Highlight Duplicate Rows
Identifying duplicate entries in your data can streamline your analysis. Use this formula to easily flag duplicates in your list.
Check Date Within Range
Need to verify if a date lies within a specific range? This guide shows you how to use the IF function effectively.
VLOOKUP for Multiple Columns
Struggling to extract data from multiple columns? Discover how to efficiently use VLOOKUP or XLOOKUP for this task.
Index Match with Multiple Criteria
Combining multiple criteria in a lookup can be challenging, but using INDEX MATCH simplifies the process significantly.
Weighted Average Calculation
Weighted averages allow you to account for varying levels of importance in your data. Use the SUMPRODUCT function to easily compute this in Excel.
Reusable Custom Functions in Excel
Unlock the power of custom functions in Excel with LAMBDA, allowing for reusable and efficient calculations tailored to your needs.
Using the LET Function
The LET function allows you to name intermediate calculations, making your formulas cleaner and more efficient.
Using the AGGREGATE Function
The AGGREGATE function allows you to perform calculations while ignoring hidden rows and error values, making data analysis more efficient.
Break-Even Point Calculation
Understanding your break-even point is crucial for business success. This formula helps you determine when your revenues will cover your costs.
Calculate CAGR in Excel
Understanding CAGR is essential for evaluating investment growth over time. This guide will help you calculate it easily using Excel.
Standard Deviation in Excel
Understanding standard deviation is crucial for data analysis, and knowing when to use STDEV.S versus STDEV.P can enhance your insights.
Z-Score Normalization
Z-score normalization transforms your data to a standard scale, making it easier to compare values across different datasets.
Calculate Moving Averages
Moving averages help smooth out data fluctuations. This guide shows you how to calculate a rolling average using Excel formulas.
Days Until Deadline
Need to track deadlines? This formula helps you calculate the days remaining until a deadline, displaying 0 if the date has passed.
Concatenate with Separator
Easily combine values from different cells into one, using a specified separator while ignoring any empty cells.
Max and Min with Conditions
Discover how to efficiently find maximum or minimum values that meet specific criteria in your data sets using Excel functions.
Rank Values Without Ties
Tired of tied ranks in your data? Discover how to rank values uniquely from highest to lowest using a simple formula.
Dynamic Chart Data Range
Automatically update your chart as new data is added by using the OFFSET function or named ranges for flexibility and efficiency.
Convert Text to Date
Dates stored as text can cause issues in Excel. This guide shows you how to convert them into proper date values effortlessly.
Calculate Sales Commission
Understanding how to calculate tiered commissions can help sales teams maximize their earnings based on performance thresholds.
VLOOKUP for Tiered Pricing
Finding the right rate in tiered pricing can be challenging; VLOOKUP with approximate match simplifies this process.
Sum Values by Month
Struggling to get monthly totals from scattered dates? This formula will help you sum values by month effortlessly.
Dynamic Sheet Reference
Using the INDIRECT function, you can easily reference different worksheets by changing a cell value, making your formulas more flexible.
Fuzzy Match Lookup
Struggling with mismatched data? Use fuzzy matching techniques in Excel to accurately compare and match lists with slight variations.
Calculate Age from Birthdate
Finding a person's age from their birthdate can be easily done using the TODAY() function in Excel. This guide will show you how.
First and Last Day of Month
Easily determine the first and last days of any month from a given date using simple Excel formulas.
Count Words in Cell
Counting words in a cell can be tricky, but with a straightforward formula, you can easily determine the word count.
Extract Email Domain
Struggling to get the domain from email addresses? This formula will help you extract everything after the '@' symbol effortlessly.
Two-Way Lookup in Excel
Struggling to find values at the intersection of rows and columns? This guide will show you how to use Excel's powerful lookup functions.
Common Excel Formula Errors
Excel users often encounter formula errors that can disrupt their work. This guide helps you identify and resolve these issues effectively.
Dynamic Dropdown List
Struggling with static dropdown lists? Discover how to make your dropdowns dynamic so they update automatically with your data.
Compare Two Lists in Excel
Comparing two lists can be challenging, but with the right formula, you can easily identify unique and common items.
Salary Grade Assignment Formula
Assigning pay grades helps in standardizing employee compensation based on their salary. This formula simplifies the process of categorizing salaries into defined ranges.
Inventory Reorder Alert Formula
Ensure you never run out of stock by using a visual alert formula to flag items below their reorder points.
Profit Margin Calculation
Understanding profit margins is crucial for evaluating business performance. This guide will help you calculate gross profit margin, net profit margin, and markup percentage using simple Excel formulas.
Loan Amortization Schedule
Managing loan payments can be complex, but with an amortization schedule, you can easily track principal, interest, and remaining balances.
Last Occurrence Lookup
Struggling to find the last match of a value in a column? This formula will help you pinpoint the most recent occurrence efficiently.
Conditional Formatting in Excel
Conditional formatting allows you to visually highlight important data by changing cell colors based on specific criteria, enhancing data analysis.
Linear Forecasting in Excel
Need to forecast future data points? The FORECAST.LINEAR function can help you extend a linear trend from your historical data.
Count Unique Values by Category
Counting unique values within categories can enhance your data analysis. Use COUNTIFS or SUMPRODUCT to achieve this efficiently.
Check for Numbers in Text
Finding out if a text string includes numeric digits can be crucial for data validation. This formula helps you determine that easily.
XLOOKUP Exact Match
Struggling to find exact matches in your data? XLOOKUP simplifies this process and allows for custom messages when values aren't found.
XLOOKUP with Wildcards
Discover how to perform partial text matching using XLOOKUP with wildcard characters, streamlining your data searches.
XLOOKUP vs VLOOKUP Comparison
XLOOKUP offers enhanced functionality over VLOOKUP, making it easier to retrieve data. Here, we show you how to convert VLOOKUP formulas to XLOOKUP.
Find Last Match with XLOOKUP
Discover how to efficiently retrieve the most recent entry of a value in a dataset using XLOOKUP's reverse search feature.
XLOOKUP Across Rows
Struggling to find values across rows? XLOOKUP can help you search horizontally and return the desired results seamlessly.
Count Unique Values in Excel
Counting unique values in a column can be tricky, but this formula will help you exclude duplicates and blanks effortlessly.
Last Value in Column
If you're dealing with a column that keeps getting new data, this formula helps you quickly find the last non-empty entry.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate it, explain it, and validate it — instantly.