Excel Formula Guides
Formula-focused tutorials covering lookup functions, calculations, debugging, and practical worksheet examples.
100 articles
Moving Average in Excel: 3-Period and 7-Day Examples
Calculate 3-period and 7-day moving averages in Excel. Follow a daily sales example, handle blanks and zeros, and plot the calculated results on a chart....
Excel Formula Errors: Find the Cause Before Hiding It
Diagnose six common Excel formula errors, check inputs and references, and fix the cause before using IFERROR to show a fallback....
INDEX MATCH MATCH in Excel: Two-Way Lookup
Find a value by row and column with INDEX MATCH MATCH in Excel. Follow a product-by-month example and fix mismatched ranges and missing keys....
Standard Deviation in Excel: STDEV.S vs STDEV.P
Calculate standard deviation in Excel with STDEV.S or STDEV.P and choose the right formula for your data....
CAGR Formula in Excel: Count Periods Correctly
Calculate CAGR in Excel with RRI or a simple exponent formula. Count five intervals across six annual values and check the result in an example workbook....
How to Split First and Last Names in Excel
Split names into columns with TEXTSPLIT, TEXTBEFORE, TEXTAFTER, or Text to Columns. Handle extra spaces, single names, and middle names....
How to Delete Blank Rows in Excel Safely
Delete completely blank rows in Excel with a helper column and filter. Keep partial records, formulas returning empty text, and cells containing spaces....
Excel IF Cell Contains Text: Formulas and Examples
Use IF with SEARCH, FIND, or COUNTIF to test whether an Excel cell contains text, with case-sensitive, multi-word, and blank-input examples....
Weighted Average in Excel: Formula and Examples
Calculate a weighted average in Excel with SUMPRODUCT and SUM. Follow price, score, and percentage examples, and handle zero weights and missing values....
How to Count Unique Values in Excel
Count unique values in Excel, ignore blanks, count values that appear once, and add criteria. Includes modern formulas and an older Excel alternative....
Compound Interest Formula in Excel (With Annual and Monthly Examples)
Calculate compound interest in Excel with annual and monthly formulas, the FV function, a frequency comparison, and a downloadable example workbook....
Summarize Sales with GROUPBY and PIVOTBY in Excel
Use GROUPBY for product totals and PIVOTBY for sales by product and region, with an Excel Table that expands when you add transactions....
How to Change Case in Excel: Uppercase, Lowercase, and Proper Case
Learn how to change case in Excel with UPPER, LOWER, PROPER, Flash Fill, and Paste Special so you can safely replace text values....
Compare Two Columns in Excel
Learn how to compare two columns in Excel for matches, differences, duplicates, missing values, and case-sensitive text....
How to Find and Fix Circular References in Excel
Learn how to find a circular reference in Excel, trace the formula loop, fix common circular reference errors, and handle intentional iterative formulas....
Translate a Column in Excel and Check the Detected Language
Translate a column with Excel's TRANSLATE function, use DETECTLANGUAGE for review, and check short labels, language codes, and service errors....
How to Calculate Percentage Change in Excel
Learn the percentage change formula in Excel, including examples for increases, decreases, percentage formatting, and avoiding divide-by-zero errors....
How to Use =COPILOT in Excel
Learn how to use the =COPILOT function in Excel, what kinds of prompts work best in cells, and where this feature still needs careful review....
Can AI Fix Broken Excel Formulas? Real Tests With VLOOKUP, XLOOKUP, and SUMIFS
I tested whether AI can fix broken Excel formulas in real scenarios involving VLOOKUP, XLOOKUP-style logic, and SUMIFS errors caused by dirty data....
Check Product Codes Against a Pattern in Excel
Use REGEXTEST to check the whole product code in Excel, flag extra spaces and wrong characters, and filter a list to the rows that need review....
How to Use AI to Explain Excel Formulas Step by Step
Learn how to use AI to explain Excel formulas step by step, including how to verify the explanation and catch what the model still gets wrong....
Best AI for Excel Formulas: Copilot vs ChatGPT vs Claude
Compare Copilot, ChatGPT, and Claude for Excel formulas using the same prompt, the same workbook task, and the same verification standard....
Build a Report from a PivotTable with GETPIVOTDATA
Use GETPIVOTDATA to pull figures from an Excel PivotTable into a fixed report. Select a region and troubleshoot missing or filtered PivotTable values....
How to Fix Common XLOOKUP Errors in Excel
Fix common XLOOKUP errors in Excel, including #N/A, spill problems, mismatched ranges, and text cleanup issues that break lookups....
Extract Numbers and Codes from Text in Excel
Extract the first number, all matches, or a specific order code with REGEXEXTRACT in Excel. Preserve leading zeros and convert quantities for arithmetic....
How to Use XLOOKUP with Wildcards for Partial Matches
Use XLOOKUP wildcards for starts-with, contains, and ends-with SKU matches. Return the first or last match, or use FILTER to show every matching row....
How to Convert Text to Time in Excel (and Fix TIMEVALUE Errors)
Convert imported text times with TIMEVALUE, check numeric timestamps, remove ordinary spaces, and handle valid HHMM entries such as 930 and 0030....
Turn Multiple Excel Columns into One List with TOCOL
Flatten an Excel grid into one column with TOCOL. Read across rows or down columns and choose how to handle blank cells and errors....
How to Return Multiple Columns and Rows with XLOOKUP
Learn how to use XLOOKUP to return multiple columns or rows in Excel and how spill behavior changes the way lookup formulas work....
Select and Reorder Columns in Excel by Header Name
Use CHOOSECOLS and XMATCH to build an Excel report in the column order you want, even when the source columns move....
How to Use XLOOKUP with Multiple Criteria in Excel
Learn how to use XLOOKUP with multiple criteria in Excel by combining Boolean logic or concatenation for cleaner, more flexible lookups....
XLOOKUP vs VLOOKUP in Excel: Which Should You Use?
Compare XLOOKUP vs VLOOKUP in Excel, including syntax, left lookups, exact matches, and when the older function still makes sense....
Convert Text to Numbers in Excel: Fix Imported Decimals and Thousands Separators
Convert imported text to numbers in Excel, choose the right decimal separators, preserve leading-zero IDs, and check blanks before totaling results....
XLOOKUP Function in Excel: Step-by-Step Guide for Beginners
Learn how to use XLOOKUP in Excel with beginner-friendly steps, practical examples, and a clear comparison with VLOOKUP, HLOOKUP, and INDEX MATCH....
How to Add or Subtract Months from a Date in Excel
Use EDATE to add or subtract months from a date, handle January 31 and leap years, and keep renewal dates tied to the original start date....
Transpose Data in Excel: A One-Time Copy or a Linked Formula
Turn rows into columns in Excel with Paste Transpose for a fixed copy or the TRANSPOSE formula for a result that follows your source cells....
Currency Conversion Using Live Exchange Rates in Excel and Google Sheets
Convert currencies in Excel or Google Sheets with current exchange rates, numeric formulas, and safe currency formatting....
VLOOKUP Not Working with Text? Check These Causes
Fix VLOOKUP text mismatches by checking exact match, numbers stored as text, hidden spaces, and wildcard characters. Keep leading-zero IDs intact....
How to Calculate Hours Between Two Dates and Times in Excel
Calculate elapsed hours between Excel dates and times, display totals over 24 hours, convert to decimal hours, and handle overnight shifts....
The Data Adds Up: Using the Addition Formula in Excel
In this article you'll learn the basic addition formula for Excel with examples and a how-to guide for entering formulas and using the sum feature....
HLOOKUP In Excel: Everything You Need to Know
If you're looking on how to use hlookup in excel, you came in to the right place! In this article, we discussed on how to use it and it's common problem....
How to Create a Database in Excel
Excel database files are useful for creating simple tables or spreadsheets based on numbers and formulas. Learn here on how to create a database in excel....
The NOW Function in Excel: What It is and How to Use It
Are you wondering how to use the now function in Excel? Look no further. Our easy step-by-step guide has all of the information that you need to know....
How to Subtract in Excel
Learn how to subtract in Excel with this valuable how-to guide. This article will walk you through each step of the process from start to finish....
Excel SUM Formula: What Is It And When Do I Use It?
Perhaps one of Excel's best-known functions is the SUM formula. Learn more about a subset of data by totaling rows, columns and more with excel sum formula....
How to Use the AVERAGE Function in Excel
To figure out the average of a group of numbers, makes it easier for you to analyze important data. Learn how to use the average function in Excel right here....
SUMIF and SUMIFS in Excel: Syntax, Examples, and Differences
Learn when to use SUMIF or SUMIFS in Excel, with working text, wildcard, and date examples—and see how their argument order differs....
Excel Fractional Part: Extracting Integers and Fractions in Excel
Sometimes you need to extract the integer or decimal part of a number. Sometimes the fractional part. Check this guide on Excel fractional part....
Extract Time from datetime with the MOD Function in Excel
Use the MOD function in Excel to extract a Time value from a Date-Time number. Change the cell formatting to get the time format of your choosing....
Break Even Calculation with an Unlocked iPhone and International Rates
When on Holiday in the UK pick up a Sim card for your unlocked iPhone. Make the smart move with simplicity plans by O2: talk, text, data....
A Dynamic Dependent Drop Down List with a Horizontal Table Reference
I received a comment asking if a dynamic dependent drop-down list in Excel could have a list where the "table headers were actually rows and not columns?" Since...
The VLOOKUP Function - Inside Out
Learn the VLOOKUP function in Excel by looking at the four arguments in reverse order....
How to Update a List or Range without OFFSET
I avoid the use of Volatile Functions , especially OFFSET, which is commonly used to update a list or range. They can slow down the operation of your workbook....
Fill Down a Formula with VBA
Here is some VBA code I use to copy a formula down to the end of a data range. It assumes a heading in row 1 and the formula in row 2....
International Short Date Formatting for the TEXT Function
A quick method to automatically give the TEXT function a short date format that matches the computers region settings by using some VBA code in Excel....
A Dynamic Dependent Drop Down List in Excel
Create dependent Excel dropdowns from an expanding Table, with separate lists for each input row and a legacy method for older versions....
How to Convert GPS Coordinates in Excel
Learn how to easily convert GPS coordinates in Excel. Discover tips and tricks to input, display, and map latitude and longitude data in Excel....
A Woopra Chat About Excel
Convert minutes and seconds to a value of minutes followed by the text MIN and formatted correctly with no decimal places by using the TEXT function....
INDEX and MATCH Functions Together Again in Excel
The INDEX and MATCH functions perform a lookups in Excel. INDEX returns a cell from an array, MATCH contributes the row and/or column position....
The INDEX Function in Excel
The INDEX function returns a cell value from a range, given a row and/or column position number. Three examples are given in this article....
The MATCH Function in Excel
The MATCH function in Excel returns the position number of a matched value from within a range, not the value itself....
Goal Seek in Excel
If you know the result that you want from a formula, but are not sure what input value the formula needs to get that result, use the Goal Seek feature...
What Day of the Year is It?
The embedded spreadsheet will take a date value and return the Day of Year with a formula that uses the DATE Function....
Use the DATEDIF Function to Quickly Calculate Date Difference in Excel
Use the DATEDIF Function in Excel to find the number of Years, Months and Days between two dates. Only documented in Excel 2000 but in later versions....
Remove Extra Spaces in Excel: TRIM, Hidden Characters, and VBA Differences
Remove extra spaces with Excel TRIM, identify hidden characters when it fails, and see why VBA Trim keeps spaces between words....
VLOOKUP Function in Excel: The Essential Guide
The VLOOKUP function in Excel explained in relatively non-technical terms for the laymen. Two examples, FALSE and TRUE, from the fourth argument....
Logical Operations in Excel
Logical operations in Excel on four TRUE/FALSE outcomes of A an B. Then ADD, MULTIPLY, and use functions AND, OR, NOT, NAND, NOR, & equals comparison....
ISNUMBER vs IsNumeric in Excel: Worksheet and VBA Differences
Learn the difference between Excel ISNUMBER and VBA IsNumeric, including how they handle text numbers, blanks, dates, and data-entry cleanup....
Calculate the Xth Weekday of Any Month in Excel
A simple formula to calculate the Xth Day of any month using four inputs: Year, Month, Week, and Day. Data validation with custom formatting required....
Converting Pace in Minutes per Mile to MPH and Back Again in Excel
I'm converting Pace to MPH and then reversing the process, converting MPH to Pace, to create a conversion chart....
Convert Seconds with the TIME Function in Excel
An iTunes playlist download shows time in seconds. Convert these seconds with the TIME Function in Excel and some custom formatting....
How to Calculate Letter Grades in Excel (IF and VLOOKUP)
Turn numeric scores into letter grades in Excel with nested IF, a VLOOKUP grading table, or a named array. Handle blanks and decimal grade boundaries....
Nested IF Functions in Excel
Nested IF Functions means having at least one IF Function contained within another IF Function, which can get complicated. Use a binary outcome chart....
The IF Function in Excel
The IF Function has three arguments. The first is required, the last two optional. Comparison calculation operators and example IF Functions shown....
Single-Cell Array Formula in Excel
The Evaluate Formula dialog box shows interim calculations for single-cell array formulas to reveal array values Excel holds in internal memory....
Multi-Cell Array Formula in Excel
A Multi-Cell Array formula is entered by selecting a range, typing a formula, then holding the Control and Shift keys down while pressing Enter....
Use the DATE Function to Find the Last Day of a Month
The DATE Function will return the last day of the month prior to the Month argument, by using zero (0) for the Day argument....
A Sparkline Example in Excel 2010
Create a dynamic range sparkline graphic to show the last seven data points. Sparklines are new in Excel 2010 and don't work with earlier versions....
Put an OFFSET Formula Inside a Named Range
Create a Named Range using a formula with the OFFSET Function to capture the last 7 days of a range....
Average the Last N Values in Excel with TAKE or OFFSET
Average the last seven values in an ordered Excel log with TAKE, or use OFFSET in older versions. Follow the range calculation and check its assumptions....
Become the OFFSET Function and Tell a Short Story
I become the OFFSET function to tell a simple story of how I give you a value for the last cell in a range by using an example....
Enter a Constant Value in a Name
Create a Name in Excel that holds a constant value....
Figure Those Pesky Fractions in Excel
Enter fractions in Excel by adding a space after the integer and before the fraction. Easily create a list of fractions.....
Stock Price Formula in a Spreadsheet
The GoogleFinance Function returns a stock price by default, given stock symbol for a company or mutual fund....
The TEXT Function in Excel
The TEXT Function in Excel converts a value to text and lets you specify the display formatting by using special format strings....
The WEEKDAY Function in Excel
Excel 2010 has new function arguments for the WEEKDAY Function. It returns an integer value based upon the day of the week....
Convert Seconds to a Time Format in Excel
Convert a large number of seconds to a hours:minute:seconds format using the TIME, INT and MOD functions....
Using the SUM Function for a Running Total in Excel
Using the SUM Function in Excel to provide a running total time for song duration....
Putting the TIME, INT and MOD Functions to Work in Excel
Convert a Time in seconds to a minutes:seconds format by using the INT and MOD functions inside the TIME function....
The TIME Function in Excel
Using the TIME Function to display minutes and seconds in a readable time format....
The MOD Function in Excel
Understanding how the MOD Function works to extract the number of seconds left over after dividing a number of seconds by 60....
QUOTIENT, ROUNDDOWN and INT Functions in Excel
Three functions to get the integer portion of a division calculation: QUOTIENT, ROUNDDOWN, INT Functions in Excel....
iTunes Playlist Export Task – Convert Seconds to a Time Format
Converting seconds to a minutes:seconds time format requires several formulas....
How to Convert Text to a Date in Excel
Convert text to dates in Excel with DATEVALUE or a fixed-layout DATE formula, then format the numeric result....
The VALUE Function in Excel – Converting Text Dates
Converting text string Dates to numeric serial number values using the Excel VALUE Function and with alternate methods: addition, multiplication, division, and double negation....
The TYPE Function in Excel
The TYPE Function in Excel will indicate the type of data in a cell. In this case between Text and Numeric....
Combine Cells in Excel with & or TEXTJOIN: Spaces, Blanks, and Number Formats
Combine cell values with & or TEXTJOIN, control spaces and blank cells, and use TEXT to keep dates and numbers readable in the result....
The RIGHT Function in Excel
The RIGHT Function returns a specified number of characters from the end of a text string in Excel....
The MID Function in Excel
Use the MID Function to extract characters from the middle of a text string in Excel....
Extract a Date from Text in Excel
Extract a Date from Text values by using a formula in Excel....
About Excel Semi Pro
Since 2010, we have been helping people improve their Excel skills. Here, you will find beginner and intermediate tips on navigating spreadsheets to achieve results.
Read more about us