We run a free email newsletter with weekly Excel tips, focused on interesting and useful formulas. Each email contains a summary of the topic and links to the complete article and workbook on Exceljet. The emails go out Friday morning. To give you an idea of what's in this newsletter, below is a list of recent emails. You can subscribe to our newsletter using the form below.
How to find all formula errors (tip)
Fri, Mar 29, 2024
In my last email, I shared a formula to count errors on all worksheets in a workbook. One of our newsletter readers then kindly pointed out that you can use Excel's Find and Replace feature to list all errors in a workbook. This is a great tip! You can see the result in the screen below. The asterisk (*) is a wildcard in Excel that will match one or more characters, so the idea here is to find values that start with a hash (#) and end with an exclamation point (!)... Read email | Read article
Count errors on all worksheets
Wed, Mar 20, 2024
One of our readers reached out last week with an interesting problem about how to count errors on all sheets in a workbook. The idea is to provide a simple way to audit the sheets in a workbook for errors. It is possible to use the IFERROR and INDIRECT functions to do this. You can see the result in the workbook below, where the formula in cell C5 is: =SUMPRODUCT(--(ISERROR(INDIRECT("'"&B5&"'!A1:F200"))))... Read email | Read article
How to combine data in multiple sheets (formula)
Fri, Mar 08, 2024
This week, I want to share a simple solution to a classic hard problem in Excel: how to combine data on multiple sheets with a formula. In the workbook below, the goal is to combine data on Sheet1, Sheet2, and Sheet3 on a summary sheet. When ranges are at a known location, a nice way to do this is to use the VSTACK function with a 3D reference like this: =VSTACK(Sheet1:Sheet3!B5:E16)... Read email | Read article
Extract common values from text strings
Fri, Mar 01, 2024
Hi - Dave here. Happy Friday! Last week, I shared a formula to extract common values from two ranges in Excel. This week, I want to show you how you can extract common values from two text strings. The new formula builds directly on top of the original. We have to parse some text strings first with TEXTSPLIT, but once we do that, we can follow exactly the same approach. You can see the results in the worksheet below. The data in row 5 contains the same values from last week. The data in row 6 sh... Read email | Read article
Extract common values in two lists (formula)
Fri, Feb 23, 2024
Hi - Dave here. Happy Friday! Today, I have another cool example of how you can use the FILTER function to do something useful. In the workbook below, the goal is to list common values in List 1 and List 2 as shown. The solution is based on the FILTER function and the XMATCH function. The formula in cell F5 looks like this: =FILTER(B5:B16,ISNUMBER(XMATCH(B5:B16,D5:D14))) [Download the workbook and read the full explanation] The article above contains a full explanation. Although the lists in t... Read email | Read article
Categorize text with keywords (formula)
Fri, Feb 16, 2024
Hi - Dave here. Happy Friday! A tricky problem in Excel is how to categorize text records based on specific keywords, as seen in the worksheet below. The challenge is that we aren't looking for one keyword, we're looking for many keywords. In addition, when we do find a keyword, we need a way to return the correct category for that keyword. One way to solve this problem is with the XLOOKUP function and a table that lists keywords and categories. This is the approach seen below where the formul... Read email | Read article
How to get numbers from TEXTSPLIT
Fri, Feb 09, 2024
Hi - Dave here. Happy Friday! I've got one more TEXTSPLIT video for you before we move on to other things. This time, the topic is how to get numbers from TEXTSPLIT. Using TEXTSPLIT to quickly split up text values is cool, but it can be frustrating when the text contains numbers because the numbers come back as text. If you try to sum these numbers, you'll get zero. The video below explains how to fix this problem with the IFERROR function. We use the VALUE function to convert certain text ... Read email | Read article
TEXTSPLIT with multiple delimiters - cool trick!
Fri, Feb 02, 2024
Hi - Dave here. Happy Friday! Did you know that TEXTSPLIT can handle multiple delimiters? This sounds super boring and technical, but it is actually quite useful! The video below explains with two examples. In the first example, the text contains inconsistent delimiters, and we configure TEXTSPLIT step-by-step to handle each variation. In the second example, the source text contains unwanted labels, and we use TEXTSPLIT to remove the labels and split the text simultaneously. You can use this t... Read email | Read article
Excel's TEXTSPLIT function - new video
Fri, Jan 26, 2024
Hi - Dave here. Happy Friday! TEXTBEFORE and TEXTAFTER are perfect when you want to get text before or after a specific delimiter. But what if you want to get text between every delimiter? In that case, Excel's TEXTSPLIT function is your friend. The video below shows how to use TEXTSPLIT in three different ways: (1) to split email addresses into two parts, (2) to split comma-separated text into five columns, and (3) to split dimensions into three separate columns. [Click to watch the video (3 ... Read email | Read article
TEXTAFTER with TEXTBEFORE - new video
Fri, Jan 19, 2024
Hi - Dave here. Happy Friday! This week, I have a new video that shows how you can use the TEXTAFTER function together with the TEXTBEFORE function. This might seem like a strange idea, but this approach is quite useful when you need to extract a value between other values. The video below explains how it works with two examples: Click to watch a brief demo - 4 minutes This video is part of our course on Dynamic Array Formulas, which covers new functions and behaviors in the Excel formula en... Read email | Read article
Excel's TEXTAFTER function - new video
Fri, Jan 12, 2024
Hi - Dave here. Happy Friday! It's been a while, but we are finally making some new videos again! First up is the TEXTAFTER function, a very nice way to split text in Excel. The video below provides an introduction to TEXTAFTER and includes three different examples: Click to watch the video (4 minutes) This video comes from our Dynamic Array Formulas course. Dynamic Arrays are the biggest change to Excel's formula engine ever. They drastically simplify complicated formulas, and provide much ... Read email | Read article
Count words in cell - new and improved!
Fri, Jan 05, 2024
Hi - Dave here. Happy Friday, and Happy New Year! I'm still working on a project to update many of my older formulas to use new functions in Excel. Below is another interesting example of an older more complicated formula being replaced by a simple formula that works just like you'd expect it to. The old version of the formula looks like this: =LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+1 The new version of the formula (below) looks like this: =COUNTA(TEXTSPLIT(TRIM(B5)," ")) [Download the work... Read email | Read article
How to capitalize the first letter with a formula
Fri, Dec 22, 2023
Hi - Dave here. Happy Friday! A quick email before we take a break for the holidays. One of the most important skills to learn with Excel formulas is the concept of nesting. Put simply, nesting just means putting one function inside another. Nesting is super useful, but it does take some practice. You have to learn to read a formula from the inside out. You can see a good example of nesting in the worksheet below, where the goal is to capitalize the first letter in a sentence while leaving all o... Read email | Read article
Formula Friday - Clean up phone numbers
Fri, Dec 15, 2023
Hi - Dave here. Happy Friday! Today I've got another good example of how new functions are completely changing the way hard problems are solved in Excel. In the worksheet below, the goal is to "clean and reformat" a list of telephone numbers that appear in various formats. The old formula involves removing extra characters one at a time with multiple nested SUBSTITUTE functions: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,"(",""),")",""),"-","")," ",""),".","")+0 The new formula ... Read email | Read article
LET there be names
Fri, Dec 08, 2023
Hi - Dave here. Happy Friday! I've got another fun formula for you :) In the worksheet below, the goal is to split different parts of a name (First, Middle, and Last) into the right columns with a single formula. The main challenge is that a middle name is not always available, so names can end up in the wrong columns. The solution in cell D5 is based on the LET function plus several other fancy new functions: =LET( parts,TEXTSPLIT(B5," "), count,COUNTA(parts), first,INDEX(parts,1), ... Read email | Read article
Get last name from name (cool formula)
Fri, Dec 01, 2023
Hi - Dave here. Happy Friday! I was updating some formulas last week on the Exceljet site and ran into another great example of how new functions like TEXTAFTER can really simplify complicated formulas. In the worksheet below, the "old" formula to get the last name from a name looked like this: =MID(B5,FIND("*",SUBSTITUTE(B5," ","*",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))))+1,100) The "new" formula looks like this: =TEXTAFTER(B5," ",-1) I don't know about you, but I prefer the second option :) [Down... Read email | Read article
XLOOKUP w/ multiple criteria + sale continues
Wed, Nov 22, 2023
Hi - Dave here. XLOOKUP is a big upgrade to the VLOOKUP function. It's flexible, powerful, and offers many cool features. But how do you use XLOOKUP with multiple criteria? This is a puzzling problem for many users because it's unclear how to provide conditions. The most flexible approach is to use Boolean logic, a technique of building formulas with simple logical expressions that return TRUE or FALSE. This sounds complicated, but it's honestly pretty easy! To see a quick demonstration, watch... Read email | Read article
10-year anniversary sale + more SORTBY
Fri, Nov 17, 2023
Hi - Dave here. Happy Friday! 10 years ago, Lisa and I started Exceljet! Our goal was to help ordinary people get things done in Excel by providing useful information in a way that is quick, clean, and to the point. Today, Exceljet serves more than a million users a month. What a crazy ride it's been. These days, we find ourselves competing in a world flooded with unhelpful (but well-written!) AI-generated content. I am happy to report that we are still 100% human :) Thank you sincerely for your... Read email | Read article
How to sort by a substring
Fri, Nov 10, 2023
Hi - Dave here. Happy Friday! In my last email, I shared a video demo of how you can use the SORTBY function to perform a custom sort. Before we leave the topic of SORTBY, I wanted to share a simpler example. In the worksheet below, we have a list of codes in Column B. Each code consists of a prefix (two letters), a color (variable), and a 4-digit number, all separated by hyphens (e.g., AX-Red-6387). The goal is to sort the codes by the color substring. In cell D5, the formula we use to solve th... Read email | Read article
The SORTBY function, part 2
Fri, Nov 03, 2023
Hi - Dave here. Happy Friday! In my last email, I shared a simple example of how you can use the SORTBY function. SORTBY works fine in the example, but the SORT function would also work and is easier to configure. So what is SORTBY good for? To answer this question, I have another short video. As you watch the video, the main point you should understand is that the column we are using to sort the data does not exist in the data. We create the column on the fly inside the formula: [Click to watc... Read email | Read article
Why is the SORTBY function useful, part 1
Fri, Oct 27, 2023
Hi - Dave here. Happy Friday! Have you used the SORTBY function yet? This is one of the new functions in Excel that at first might not make sense. After all, why use SORTBY when you can just use the SORT function? I'm going to answer this question in two parts. For part one, watch this short video to see a basic SORTBY example: [Click the link above to to watch a short video - 3 minutes] For part two, I'll share a more advanced problem that shows why you sometimes need the SORTBY function. Both ... Read email | Read article
Mortgage payment schedule - one formula!
Fri, Oct 20, 2023
Hi - Dave here. Happy Friday! In my last email, I shared a simple workbook to estimate a mortgage payment based on the loan amount, the term, and the annual interest rate. A smart reader of this newsletter, Matt Hanchett, suggested I take this example further and generate an entire mortgage payment schedule with a single formula like this: =LET( loanAmt,C9, intAnnual,C5, loanYears,C6, rate,intAnnual/12, nper,loanYears*12, pv,-loanAmt, pmt,PMT(rate,nper,pv), pers,SEQUENCE(nper), ipmts,IPMT(rate,... Read email | Read article
Estimate your mortgage payment
Fri, Oct 13, 2023
Hi - Dave here. Happy Friday! Mortgage rates are high, and maybe going even higher!? If you're in the market for a new home, you need to be able to quickly estimate your mortgage payment for different options. You can easily do this in Excel with the PMT function, as seen below: [Download the workbook and read the full explanation] To estimate a mortgage payment, you'll need to provide the rate, the number of periods, and the loan amount. The tricky part of using PMT for a mortgage is providing ... Read email | Read article
FILTER with boolean logic - useful!
Fri, Oct 06, 2023
Hi - Dave here. Happy Friday! Of all the new functions the Excel team has introduced in the last few years, I think the FILTER function is the most useful for the average user. This is because FILTER, unlike XLOOKUP, returns multiple results. This makes FILTER a very good way to isolate and inspect specific data without altering the original data. But one challenge people run into with FILTER is how to apply more than one condition since there is just one include argument. The trick is to use s... Read email | Read article
List "Second Tuesdays" of the month
Fri, Sep 29, 2023
Hi - Dave here. Happy Friday! How can you list the "Second Tuesdays" of each month with a formula? This is a tricky problem in Excel, but you can solve it with one formula like this: Don't be alarmed by this formula...it's not as bad as it looks! Most of the code is just set up, where we use the LET function to define variables that we'll need later. The real work is done in the last 4 lines. You can see the result in the worksheet below, which is configured to list "second Tuesdays" for the ne... Read email | Read article
Previous work day formula + last day for VBA School
Fri, Sep 22, 2023
Hi - Dave here. Happy Friday! Last week, I shared a formula to list semimonthly pay dates that land on the 1st and 15th of each month. Sharp readers pointed out that when these dates land on a weekend, they are typically adjusted to the previous business day. How can we get Excel to make this adjustment for us? The trick is to run the dates through the WORKDAY function with a special configuration like this: =WORKDAY(date+1,-1) You can see the result of this approach in the worksheet below. T... Read email | Read article
Semimonthly pay dates & VBA School
Fri, Sep 15, 2023
Hi - Dave here. Happy Friday! In my last few emails, I've shared some formulas that generate lists of dates, generally using a combination of the SEQUENCE function and the WORKDAY.INTL function. This week, I want to share a more advanced formula to list semimonthly pay dates. A semimonthly pay schedule means employees are paid twice a month, usually on the 1st and 15th or the 15th and the last day of the month. This is a bit tricky to do with an Excel formula, but it can be done! You can see wha... Read email | Read article
Sequence of custom days (special trick)
Fri, Sep 01, 2023
Hi - Dave here. Happy Friday! In my last email, we looked at how you can create a list of workdays using SEQUENCE and the WORKDAY.INTL function. One cool feature of WORKDAY.INTL is that it has a special trick that allows you to define any day of the week as a workday. This makes it possible to list custom days, like Tuesdays and Thursdays, or Mondays, Wednesdays, and Fridays. The worksheet below shows an example. [Download the workbook and read the full explanation] Note the code inside the form... Read email | Read article
Dynamic list of workdays only (formula)
Fri, Aug 25, 2023
Hi - Dave here. Happy Friday! In my last email, I shared a simple formula to generate a list of sequential dates. This works great if you want all days. But what if you need to list workdays only? This is a more complicated problem because we must exclude weekends and holidays. The best function for this in Excel is WORKDAY.INTL. The WORKDAY.INTL function takes a date and returns the next workday based on a given offset. WORKDAY.INTL will automatically exclude weekends and can optionally exclud... Read email | Read article
Sequence of days (formula)
Fri, Aug 18, 2023
Hi - Dave here. Happy Friday! One of the new functions in Excel is SEQUENCE, which is designed to generate numeric sequences. At first glance, you might wonder how you can use a function like this. Sure, you can use SEQUENCE to generate the numbers 1-10, or even 1-100, but what else? Well, one of the most useful applications for SEQUENCE is dates. Dates in Excel are just numbers, so you can use SEQUENCE to create a dynamic list of dates, as seen in the worksheet below: [Download the workbook a... Read email | Read article
How to extract the domain from an email address
Fri, Aug 11, 2023
Hi - Dave here. Happy Friday! In my last email, we looked at how to create an email address from a name and domain using concatenation. In this email, I want to show you how to get the domain from an email address, as seen in the workbook below. The formula in E5, copied down, is: =TEXTAFTER(C5,"@") [Download the workbook and read the full explanation] This is an easy problem in the current version of Excel, which offers the TEXTAFTER and TEXTSPLIT functions. In older versions of Excel, you can ... Read email | Read article
How to create an email address with a formula
Fri, Jul 28, 2023
Hi - Dave here. Happy Friday! One of the key skills you need to be good with Excel formulas is concatenation. Put simply, concatenation is just a fancy name for joining text together. In Excel formulas, the operator for concatenation is the ampersand (&) character. A good example of a simple concatenation task is the creation of an email address using a first and last name. The core problem is to join together parts of a name and a domain, as seen in the worksheet below. The formula in E5, copie... Read email | Read article
FILTER data between two dates
Fri, Jul 21, 2023
Hi - Dave here. Happy Friday! Of all the new functions in Excel, I think the FILTER function is the most generally useful because it lets you extract just the data you want to see. One problem that comes up often with FILTER is how to filter values between two dates, as seen in the worksheet below. Basically, we want to look for dates that are greater than or equal to a start date in F5 and less than or equal to an end date in G5. Then we want to extract the corresponding data. You might think w... Read email | Read article
How to get the last match
Fri, Jul 14, 2023
Hi - Dave here. Happy Friday! By default, lookup functions in Excel return the first match. This works fine in many cases, but there are many situations where you might need to get the last match, for example, the last order by a customer, the last contact date, the last exchange rate, the last update, etc. In the worksheet below, the goal is to look up the latest order for a given person by Name. In other words, we want the last match by name. In the current version of Excel, a good solution is... Read email | Read article
Simple dynamic range with XLOOKUP
Fri, Jun 30, 2023
Hi - Dave here. Happy Friday! One feature of XLOOKUP that most people don't know about is that it returns a reference, not just a value. This is somewhat confusing because normally Excel immediately returns the value at the reference so you never actually see the reference. Nevertheless, the reference is there and can be used in some interesting and useful ways. One example is to create a dynamic range based on two XLOOKUP formulas, as seen below. When a user enters a Start and End date, XL... Read email | Read article
How to get information about the max value
Fri, Jun 16, 2023
Hi - Dave here. Happy Friday! An interesting problem in Excel is how to look up information related to the maximum value in a set of data. For example, if you have a dataset of property listings and prices, you might want to find details about the property with the highest price. The best way to solve this problem depends on which version of Excel that you use. In Excel 2019 and earlier, the classic solution is to use the MAX function to find the maximum value, then use this value in an INDEX an... Read email | Read article
INDEX and MATCH two-column lookup
Fri, Jun 09, 2023
Hi - Dave here. Happy Friday! A couple of months ago, I published an article on XLOOKUP vs INDEX and MATCH. One of the pros I mentioned for INDEX and MATCH is that MATCH uses numbers for columns and rows, which means these numbers can be easily modified in the formula. You can see an example of this in the worksheet below, where the goal is to retrieve both the width and the length in a lookup operation. The first INDEX and MATCH formula gets the width. To get the length, we start with the sam... Read email | Read article
XLOOKUP wildcard match
Fri, Jun 02, 2023
Hi - Dave here. Happy Friday! One of the cool things you can do with XLOOKUP is create a wildcard search. This is a nice touch when you want to make it easy to match text anywhere in a cell. You can see an example in the worksheet below. When a search string is entered in cell G4, XLOOKUP will find the text anywhere in a book title, and return the matching Title, Author, and Year: [Click here to read the article and download the workbook] In older versions of Excel, you can use the same idea wit... Read email | Read article
Quantity based discount formula
Fri, May 19, 2023
Hi - Dave here. Happy Friday! An interesting problem in Excel is how to calculate quantity-based discounts. In the worksheet below, the goal is to calculate a discount for each item based on quantity using the discounts in the table to the right. The purpose of the table is to allow each item to have its own set of discounts. Notice that Donuts have a different discount for a quantity of 24. The discounts for other items can be customized as well. This is a classic two-way lookup problem. The... Read email | Read article
If NOT this or that
Fri, May 12, 2023
Hi - Dave here. Happy Friday! The IF function is one of the most popular functions in Excel. Easy to use and flexible, IF can be used to solve all kinds of problems. Yet many Excel users don't know you can use other functions and formulas directly inside of IF. You can use formulas for the logical test, for the TRUE result, or for the FALSE result, in any combination. In addition, you can use the NOT function to reverse the behavior of IF. You can see an example of this in the worksheet below, ... Read email | Read article
If cell begins with x, y, or z
Fri, May 05, 2023
Hi all, Happy Friday! How can you write a formula that will test if a cell begins with x, y, or z? As always in Excel, there are different ways to approach this problem. I think the simplest way is to use the OR function with the LEFT function to perform the test. Then use the IF function to return a final result. You can see this approach below, where the formula in cell D5 is: =IF(OR(LEFT(B5,1)={"x","y","z"}),"x","") [Download the workbook and read the full explanation] You might be curious ab... Read email | Read article
How to find the closest match (formula)
Fri, Apr 28, 2023
Hi all, Happy Friday! A classic formula problem in Excel is how to find the closest match. This is different from an exact match, or an approximate match, because the idea is to find the match with the least difference to a target value. The easiest way to solve this problem is with the XLOOKUP function because one of XLOOKUP's core features is the ability to perform an approximate match on unsorted data. This sounds very abstract, but we can use this feature to look for a difference of zero bet... Read email | Read article
XLOOKUP binary search
Fri, Apr 21, 2023
Hi all, Happy Friday! One of the useful features in XLOOKUP is "binary search." A binary search is optimized for speed. The idea is to repeatedly divide a lookup array in half until a target value is found. This "by halves" behavior means a binary search is very fast, but the data must be sorted. In the worksheet below, XLOOKUP is used to look up the amount for 1000 sequential invoice numbers in a table that contains 1 million invoices. The formula in cell F5 is: =XLOOKUP(E5,data[Invoice],data[A... Read email | Read article
XLOOKUP vs INDEX and MATCH
Fri, Apr 14, 2023
Hi all, Happy Friday! Last week I shared an article on the pros and cons of XLOOKUP vs VLOOKUP. You can find that article here: XLOOKUP vs VLOOKUP. This week, I wrote about XLOOKUP vs INDEX and MATCH: [Read the full article] XLOOKUP brings a ton of power and flexibility to the table. But INDEX and MATCH have always been a go-to solution for difficult lookup problems in Excel. Plus, the new XMATCH function gives this classic combo many new features. The verdict? It's close! Read the article for a... Read email | Read article
XLOOKUP vs VLOOKUP - the pros and cons
Fri, Apr 07, 2023
Hi - Dave here. I hope you're having a nice week. For many years, VLOOKUP has reigned supreme as the go-to lookup function in Excel. In almost every industry, millions and millions of existing spreadsheets use VLOOKUP to do something useful. However, with the introduction of XLOOKUP in 2019, Excel users have a powerful new lookup option available. XLOOKUP can do everything VLOOKUP can do, and more. Should you stop using VLOOKUP altogether? Should you even learn VLOOKUP if you are new to Excel? I... Read email | Read article
Basic INDEX and MATCH
Fri, Mar 31, 2023
Hi - Dave here. Happy Friday! Last week I sent out a more advanced INDEX and MATCH example. The formula works well but is hard to understand if you don't already know how to use INDEX and MATCH. To help explain how INDEX and MATCH work together, here is a more basic example that looks up the year, rank, and sales for a random group of 1990s movies. This is a classic "exact match" lookup based on the movie title. The gist is that the MATCH function locates the position of a movie in the list and ... Read email | Read article
INDEX and MATCH multiple criteria + approximate match
Fri, Mar 24, 2023
Hi - Dave here. Happy Friday! A tricky problem in Excel is how to configure INDEX and MATCH to apply multiple criteria and also perform an approximate match. For example, in the worksheet below, we want to lookup the correct cost based on the (1) Service and (2) Weight. Service is an exact match condition. But weight is approximate match. How best to combine these two conditions? One classic solution is to use the IF function to "filter out" weights for other services, then use the MATCH functio... Read email | Read article
VLOOKUP and XLOOKUP without #N/A errors
Fri, Mar 17, 2023
Hi - Dave here. Happy Friday! If you use lookup functions like VLOOKUP or XLOOKUP, one thing you need to be aware of is the possibility of encountering #N/A errors. This error occurs when a function cannot find the specified data. This is useful information, but spreadsheet errors are scary to many people, so you may want to return a more friendly result. The way to handle #N/A errors with VLOOKUP and XLOOKUP is different. For VLOOKUP, I recommend using the IFNA function, as shown below: Downloa... Read email | Read article
Automatic row numbers
Fri, Mar 10, 2023
Hi - Dave here. Happy Friday! How can you add automatic row numbers to a list or table? This has always been a tricky problem in Excel because there is no built-in function to create and maintain row numbers. In the current version of Excel, the SEQUENCE function is the easiest way to create automated row numbers with a formula. In older versions of Excel, you can use a formula based on the ROW function. However, for simple one-off numbering, you may want to use the Fill Handle, as seen below. ... Read email | Read article
Geeky formula example
Fri, Mar 03, 2023
Hi - Dave here. Happy Friday! This week, I worked on a geeky formula that uses a lot of functions to do something really simple: it displays the currently defined range of a named range or table. This is one of those Excel problems where you start off thinking "this will be easy" and then the hours go by....If you work in Excel a lot, I'm sure you've been there before :) Anyway, the image below shows the basic idea. The current table range is B5:D16, as reported by the formula in cell G5. As yo... Read email | Read article
Maximum change formula
Fri, Feb 24, 2023
Hi - Dave here. How can you calculate the maximum change between two sets of values with a formula, when the change itself is not part of the data? This is a classic array formula problem. The solution is simply to perform the change calculation inside the MAX function. For example, in the worksheet below, the formula to calculate the maximum change between high and low values in cell F5 is: =MAX(data[High]-data[Low]) [Download the workbook and read the full explanation] The tricky part of this ... Read email | Read article
Replace one delimiter with another (formula)
Fri, Feb 17, 2023
Hi - Dave here. Happy Friday! This week we look at how to replace commas with line breaks using a formula. More generally, the problem is how to replace one delimiter with another. There are two basic approaches to this challenge. The first and best approach is to use the TEXTSPLIT and TEXTJOIN functions as seen in the workbook below. Another approach is to find and replace commas with line breaks using the SUBSTITUTE function. Both methods are explained in the article. [Download the workbook... Read email | Read article
How to get the nth largest value (formula)
Fri, Feb 10, 2023
Hi - Dave here. Happy Friday! Last week, I shared a formula to average the top 3 values in a set of data. The formula uses the LARGE function to retrieve the 3 largest values, which are then fed into the AVERAGE function to get a final result. This week, I have a more basic example of the LARGE function in action. Here, we use LARGE to get the top 3 scores for each name in the list. Note that the order of the Quiz scores does not matter. The best score will always appear in column I. [Read the a... Read email | Read article
Average top 3 scores (formula)
Fri, Feb 03, 2023
Hi - Dave here. Happy Friday! The AVERAGE function is very easy to use. Simply give AVERAGE a range, and it returns an average: =AVERAGE(range) But what if you want to average just the top 3 values in the range? This is a slightly tricky problem in Excel, because it's not obvious how to limit the values included in the average. The solution is to combine the AVERAGE function with the LARGE function like this: =AVERAGE(LARGE(range,{1,2,3})) The second value given to LARGE, {1,2,3} is called an ar... Read email | Read article
Highlight specific day of week (formula)
Fri, Jan 27, 2023
Hi - Dave here. This week, I have a simple example of using a formula to apply conditional formatting. This is one of my favorite ways to pinpoint specific data quickly, without altering a worksheet. In the example below, we have a list of deposits by date. The conditional formatting is configured to highlight dates that occur on a specific day of week, which is selected in cell F5. When the day is changed, the highlighting updates. [Download the workbook and read the full explanation] The formu... Read email | Read article
Use Paste Special for in-place changes (tip)
Fri, Jan 20, 2023
Hi - Dave here. Sometimes, you want to change some numbers in Excel, but you'd rather not use formulas. Maybe you don't want to make room on the spreadsheet for the formulas, or maybe you just feel lazy :) One option to keep in mind is Paste Special. The Paste Special operation can do many things in Excel, including simple math operations. This means you can use Paste Special to change cell values without formulas. Watch the short video below to see how this works: [Watch video - 2m30s] This vid... Read email | Read article
Average last n rows (formula)
Fri, Jan 13, 2023
Hi - Dave here. Happy Friday! One of the challenges with Excel is that it's complex. It's an old application with many layers of features, and it's not uncommon to get lost in the weeds when trying to find a solution to a problem. However, there are new functions in Excel that are making things easier. A good example is the TAKE function, which makes it simple to retrieve the "last n entries" in a list or table. In the worksheet below, the "old" way to average the "last n rows" in the data shown... Read email | Read article
Sum if not blank (formula)
Fri, Jan 06, 2023
Hi - Dave here. Happy Friday! Let's start off the new year with a simple formula. In the screen below, the goal is to sum the amounts in C5:C16 when corresponding values in D5:D16 are not blank. The formula in cell G5 is: =SUMIFS(C5:C16,D5:D16,"<>") The main challenge with SUMIFS is the quirky syntax. For criteria, we simply use the "not equal to" operator, "<>". We don't provide a value, and it's implied that this means "not equal to nothing", i.e. "not blank". [Download the workbook and rea... Read email | Read article
New shortcut PDF + Holiday sale ends soon
Wed, Dec 21, 2022
Hi - Dave here. There have been a lot of small changes to Excel's shortcuts in the last couple of years and we finally have an updated PDF to share with you. You can download the latest and greatest here: [Download the new PDF] You can also find our most current list of Excel shortcuts online here: https://exceljet.net/shortcuts Holiday sale ends Friday! Start the new year with more Excel skills! Right now, you can save 25% on all of our training. Here's what you get: Bite-sized video training... Read email | Read article
How to SUMIFS with wildcards
Fri, Dec 16, 2022
Hi - Dave here. One of the most commonly used functions in Excel is the SUMIFS function. You'll see SUMIFS in all kinds of formulas that do a conditional sum. The basics are easy. If you want to sum cells in B1:B10 when cells in A1:A10 contain the color "Red", you can use SUMIFS like this: =SUMIFS(B1:B10,A1:A10,"red") Simple. But what if you need to do a SUMIFS based on a substring in another cell? For example, sum by state, where state appears embedded in other text? This is one of those Excel ... Read email | Read article
Case-sensitive sum + holiday sale!
Fri, Dec 09, 2022
Hi - Dave here. Happy Friday! How can you do a case-sensitive sum with SUMIFS? Unfortunately, you can't. The SUMIFS function doesn't offer a way to check case. However, you can create a formula to perform a case-sensitive sum. The trick is to use the EXACT function, as seen in the worksheet below: [download the workbook and read the full explanation] Tip: when you see the EXACT function in a formula, you can be sure you're looking at a formula designed to be case-sensitive. The article abo... Read email | Read article
Average if challenge
Fri, Dec 02, 2022
Hi - Dave here. Happy Friday! I ran into an interesting "average if" problem last week... How can we calculate the average for each group as seen below? [download the workbook and read the full explanation] I was inspired to create this example after watching a video by Excel expert Chandoo on the same topic. The article above walks through four different approaches and explains why AVERAGEIFS won't work in this case. Download the workbook and try it out yourself. Excel formulas To be good with ... Read more
XLOOKUP with multiple criteria
Fri, Nov 18, 2022
Hi - Dave here. One of the great new functions in Excel is XLOOKUP, a modern replacement for the famous VLOOKUP function. But how do you use XLOOKUP with multiple criteria? At a glance, this seems like a tricky problem because XLOOKUP only accepts one value for lookup_value and one value for lookup_array. How can we tell XLOOKUP to use values in multiple columns? The most flexible approach is to use Boolean logic. Boolean logic is a technique of building formulas with simple logical expressio... Read more
Does password contain a number?
Fri, Nov 11, 2022
Hi - Dave here. What formula will tell you if password contains a number? This is a surprisingly tricky problem because Excel doesn't have a function that will let you test for a number inside a text string directly. One option is to use the FIND function to look for all 10 numbers, then count the results, as seen in the worksheet below. The formula in cell C5 is: =COUNT(FIND({0,1,2,3,4,5,6,7,8,9},B5))>0 [download the workbook and read the full explanation] This is a good example of how formulas... Read more
Split comma-separated text (formula)
Fri, Nov 04, 2022
Hi - Dave here. Last week, I shared a formula to split dimensions into separate values. This week, I have a more generic example: how to split comma-separated text into separate cells. The "before" formula uses a nerdy FILTERXML trick: The "after" formula, based on TEXTSPLIT, is dead simple: [download the workbook and read the full explanation] Another good example of how new Excel functions really simplify things! TEXTSPLIT only works in Excel 365, but the FILTERXML formula above works in olde... Read more
New website + new formula!
Fri, Oct 28, 2022
Hi - Dave here. Last week, I shared an example of a formula makeover with the new TEXTBEFORE and TEXTAFTER functions. This week, I have a similar example with the TEXTSPLIT function. In the worksheet below, the goal is to split the text in column B into three separate dimensions. The original solution required 3 complex formulas: L=LEFT(B5,FIND("x",B5)-1)+0 W=MID(B5,FIND("x",B5)+1,FIND("~",SUBSTITUTE(B5,"x","~",2))-FIND("x",B5)-1)+0 H=RIGHT(B5,LEN(B5)-FIND("~",SUBSTITUTE(B5,"x","~",2)))+0 The ne... Read more
Split text with new functions in Excel
Fri, Oct 21, 2022
Hi - Dave here. Last week, I shared an example of how the new TEXTSPLIT function can simplify older complicated formulas. This week, I want to introduce two other new functions that will help simplify text processing formulas: TEXTBEFORE and TEXTAFTER. In the worksheet below, here are the "before" formulas: LEFT(B5,FIND("_",B5)-1) // left RIGHT(B5,LEN(B5)-FIND("_",B5)) // right And here are the "after" formulas: =TEXTBEFORE(B5,"_") // left =TEXTAFTER(B5,"_") // right As you can see, TEXTBEFORE a... Read more
Split text into words with TEXTSPLIT
Thu, Oct 13, 2022
Hi - Dave here. One of the most interesting new functions in Excel is TEXTSPLIT. In a nutshell, TEXTSPLIT splits a text string into an array using a custom delimiter. This is a major upgrade to Excel's ability to process text, and will radically simplify many complex formulas. As an example, I recently reworked a complicated formula that extracts the nth word from a text string. Here is the original formula: =TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (C5-1)*LEN(B5)+1, LEN(B5))) And here is ... Read more
Sum by year (formula)
Fri, Oct 07, 2022
Hi - Dave here. In the past few months, I've shared formulas to sum values by week, by month, and by quarter. This week, I want to round things off with a formula to sum values by year. Many people will try to solve this with the SUMIFS function, which does work, albeit with a slightly complicated formula. However, SUMPRODUCT is easier. In the worksheet below, we are using a formula to calculate total amounts for three years: 2020, 2021, and 2022. The formula in cell G5 is: =SUMPRODUCT((YEAR(d... Read more
How to sum every nth column (formula)
Fri, Sep 30, 2022
Hi - Dave here. One tricky challenge in Excel is how to sum every nth column. In other words, how to sum values in every 3rd column, every 4th column, etc. There are two basic approaches to this problem in Excel: 1. Extract nth values & sum the result 2. Cancel non-nth values & sum the result The worksheet below shows approach #1: [download the workbook and read the full explanation] The formula in the worksheet looks complicated. However, at a basic level, it just divides column numbers by n (... Read more
How to sum by day of week (formula)
Fri, Sep 16, 2022
Hi - Dave here. Here's a tricky problem for you.... How can you sum values by day of week like the worksheet below? [download the workbook and read the full explanation] Believe it or not, you can solve this problem nicely with the TEXT function :) Using the TEXT function this way is one of my favorite tricks for certain date problems. The link above includes the workbook and a detailed explanation. Have a look and try it out for yourself. Excel formulas We maintain a large list of working formu... Read more
How to sum by quarter (formula)
Fri, Sep 09, 2022
Hi - Dave here. How can you sum values by quarter with an Excel formula? There are a few ways to approach this problem. One option is to use the SUMIFS function with a helper column, as seen in the worksheet below. The formula in G5 is: =SUMIFS(data[Amount],data[Qtr],F5) If you want to sum values by quarter without a helper column, you'll need a more advanced formula like this: =SUMPRODUCT((ROUNDUP(MONTH(data[Date])/3,0)=F5)*data[Amount]) This formula will calculate quarters internally and retur... Read more
Sum by week (formula)
Fri, Sep 02, 2022
Hi - Dave here. I recently explained how to sum by month with a formula. This week, I want to explain how to sum by week with a formula. This turns out to be a pretty straightforward problem because dates in Excel are large serial numbers, so you can just add 7 to get a date one week later. In the worksheet below, you can see how this works with the SUMIFS function. The formula in cell F5, copied down, is: =SUMIFS(data[Amount],data[Date],">="&E5,data[Date],"<"&E5+7) [download the workbook and r... Read more
Left lookup with INDEX and MATCH
Fri, Aug 26, 2022
Hi - Dave here. How do you do a left lookup with INDEX and MATCH? This is actually a trick question. Unlike VLOOKUP, INDEX and MATCH doesn't care about lookup direction at all. You can see how this works in the worksheet below. The formula in H5 is: =INDEX(data[Item],MATCH(G5,data[ID],0)) The MATCH function finds the value in G5 (1003) in the ID column, and provides the position to INDEX, which returns a value at that row. The fact that the values being returned come from the left of the ID c... Read more
Sum by month with a formula
Fri, Aug 19, 2022
Hi - Dave here. A problem that comes up often in Excel is how to sum values by month. The main challenge is to work out the logic needed to target values from the first day of the month through the last day of the month, inclusive. In the worksheet below, we use the SUMIFS function with the EDATE function to do this. The formula in F5 is: =SUMIFS(amount,date,">="&E5,date,"<"&EDATE(E5,1)) As the formula is copied down, we get a total for each month listed in column E. Note: E5:E10 contains firs... Read more
Calculate a running total (formula)
Fri, Aug 12, 2022
Hi - Dave here. Happy Friday! A classic problem in Excel is how to calculate a running total with a formula, sometimes called a cumulative sum. The classic solution is to use what is called an expanding reference — a range that expands to include more cells as the formula is copied down a column. This is the solution seen in the workbook below. As the formula is copied down, the range expands to include each new row: [download the workbook and read the full explanation] Another approach is to ... Read more
Unique values from multiple ranges
Fri, Aug 05, 2022
Hi - Dave here. The UNIQUE function makes it incredible easy to extract unique values from a range. Just provide a range, and UNIQUE will give you back the unique values: =UNIQUE(range) But how do you extract UNIQUE values from more than one range at the same time? Up to now, this has been a difficult problem, since UNIQUE is programmed to accept only one array. However, with the introduction of the VSTACK function, the solution is simple. In the worksheet below, the formula in cell H5 is: =UNIQ... Read more
Two-way count with a mixed reference (formula)
Fri, Jul 29, 2022
Hi - Dave here. A key formula skill in Excel is using mixed references, which lock specific parts of a reference. For example, the reference $A1 locks the column, but allows the row to change. The worksheet below shows how mixed references can be used in an actual problem. The formula in cell G5 is: =COUNTIFS(dept,$F5,group,G$4) Here, the named ranges dept and group automatically behave like absolute references and will not change. The references to $F5 and G$4 however are mixed. As the formula ... Read more
Running count with COUNTIF (formula)
Fri, Jul 22, 2022
Hi - Dave here. There are many ways to count things in Excel, and many functions like COUNT, COUNTA, COUNTIF, etc. to help you do it. However, one challenge you might run into is how to create a running count, and the solution is not exactly obvious. One way to create a running count is to use the COUNTIF function with an expanding range. This is the approach used in the worksheet below. The formula in C5, copied down, is: =IF(B5=value,COUNTIF($B$5:B5,value),"") With "blue" in cell E5, we get a ... Read more
Count total matches in two ranges (formula)
Fri, Jul 15, 2022
Hi - Dave here. One problem that comes up a lot in Excel is how to compare two ranges and count values that appear in both. To solve this problem, you can use the COUNTIF function or the MATCH function, with help from our friend SUMPRODUCT to tally up results. In the worksheet shown, the formula in F5 is: =SUMPRODUCT(COUNTIF(range1,range2)) where range1 (B5:B16) and range2 (D5:D13) are named ranges. [download the workbook and read the full explanation] As I mentioned, you can also use the MATCH ... Read more
List dates expiring soon (formula)
Fri, Jul 08, 2022
Hi - Dave here. This week, I have an example that shows how to list dates that are expiring soon. In the worksheet shown below, the formula in G5 is: =SORT(FILTER(data,data[Expires]-date<=days),4) where data is an Excel Table in the range B5:E16, and date (H2) and days (J2) are named ranges The result is the five rows in the table with an expiration date in the next 15 days: [download the workbook and read the full explanation] This formula is based on the new FILTER and SORT functions, which ar... Read more
XLOOKUP with OR logic (formula)
Fri, Jul 01, 2022
Hi - Dave here. Last week, I explained how to use XLOOKUP to find the first negative value in a set of data. This week, I have a related example that shows how to use XLOOKUP to find the first value based on OR logic. The goal is to find the first "Red" or "Pink" record in the data. The formula looks like this: =XLOOKUP(1,(data[Color]="red")+(data[Color]="pink"),data) where "data" is an Excel Table in the range B5:E14. The double negative (--) converts TRUE and FALSE results to 1s and 0s. XLOOKU... Read more
Lookup first negative value with XLOOKUP
Fri, Jun 24, 2022
Hi - Dave here. I had a question recently about how to lookup the first negative value in a set of data. As usual, there are several ways to approach the problem in Excel. One nice solution is to use XLOOKUP like this: =XLOOKUP(1,--(data[Low] <0),data) where "data" is an Excel Table in the range B5:C16. The double negative (--) converts TRUE and FALSE results to 1s and 0s. XLOOKUP then matches the first 1, and returns the corresponding row in the table: [download the workbook and read the full e... Read more
Count cells that contain errors (formula)
Fri, Jun 17, 2022
Hi - Dave here. This week, another example where COUNTIF can't be used, but SUMPRODUCT works just fine. In the worksheet below, we want to count cells that contain errors. You might think we can use COUNTIF like this: =COUNTIF(ISERROR(data),TRUE) // fails The idea here is that the ISERROR function will return TRUE or FALSE, and COUNTIF will count the TRUE results. However, if you try to enter this formula, Excel won't let you. This is another case where COUNTIF won't work because it won't allo... Read more
Count if row meets multiple criteria
Fri, Jun 10, 2022
Hi - Dave here. Last week I sent out an article called Why SUMPRODUCT? This week, I want to followup with a specific example of a problem you can solve with SUMPRODUCT that you can't solve with COUNTIFS. In the worksheet below, the goal is to count orders (rows) where the state is Texas ("TX"), the amount is greater than $100, and the month is March. You would think that COUNTIFS would be the perfect tool for this job, but the problem is that COUNTIFS won't let you use the MONTH function l... Read more
Why SUMPRODUCT?
Fri, Jun 03, 2022
Hi - Dave here. As you spend more time working with Excel formulas, you'll start to run into the SUMPRODUCT function a lot. Why does the SUMPRODUCT function show up in so many Excel formulas? In this article, I attempt to explain why you see SUMPRODUCT so often in formulas, and when you can use the SUM function instead. [download the workbook and read the full explanation] The screens above show an example from the article. Download the workbook and have a look. This is a confusing topic, but ... Read more
Count birthdays by year (formula)
Fri, May 27, 2022
Hi - Dave here. Given a list of birthdays, how can you count birthdays per year? Although you can solve this problem with the COUNTIFS function, the formula is somewhat complicated because you must create a start and end date for each year. A better solution is to use the SUMPRODUCT function and Boolean logic. The formula in the worksheet below is: =SUMPRODUCT(--(YEAR(data[Birthday])=E5)) [download the workbook and read the full explanation] Boolean logic is a way of handling TRUE and FALSE ... Read more
Count cells that begin with certain text
Fri, May 20, 2022
Hi - Dave here. Counting cells that begin with certain text is simple with the COUNTIF function and the asterisk (*) wildcard, as seen below: Things get quite a bit more interesting if we want to make a case-sensitive formula. The article below explains all the gory details and includes a sample workbook: https://exceljet.net/formula/count-cells-that-begin-with The workbook is attached near the bottom of the article. Have a look and try it out. I included a bonus formula at the end. In Excel, th... Read more
How to concatenate in Excel
Fri, May 13, 2022
Hi - Dave here. One of the most important skills in Excel formulas is concatenation. Concatenation is just a fancy name for joining things together. You can see a basic example in the worksheet below: There are two basic ways to concatenate in Excel: 1. Manually with the ampersand operator (&) 2. With a function like CONCAT or TEXTJOIN The article below explains both approaches with many examples. https://exceljet.net/how-to-concatenate-in-excel The workbook is attached near the bottom of the a... Read more
Count numbers with leading zeros (formula)
Fri, May 06, 2022
Hi - Dave here. One limitation of the COUNTIF function is that it won't count numbers with leading zeros. For example, in the worksheet below we want to count codes equal to "009875". The correct result is 2, but COUNTIF returns 4: This is a limitation of the COUNTIF function. A good solution is to use the SUMPRODUCT function instead. Read how and download the workbook here: [Download the workbook and read the full explanation] Errata Last week's email about using XLOOKUP to look up an entire c... Read more
How to look up an entire column (formula)
Fri, Apr 29, 2022
Hi - Dave here. How can you lookup an entire column with a formula? The XLOOKUP function makes it really easy. For example, in the worksheet below, H3 contains the lookup value "Q3", and the formula in H4 is: =XLOOKUP(H4,quarter,data) The result is that XLOOKUP finds and retrieves all data for Q3: [download the workbook and read the full explanation] This problem can also be solved with INDEX and MATCH. See the article and attached workbook for details. Excel formulas We maintain a large list of... Read more
How to sum time in Excel
Fri, Apr 22, 2022
Hi - Dave here. Last week, we looked at a simple way to strip times from dates, without affecting the dates. This week I have an example that shows how to sum time in Excel. Dates and times are just numbers in Excel, so you can use them in any normal math operation. However, by default, Excel will only display hours and minutes up to 24 hours. This means you might seem to "lose time" if you are adding up time that is more than 1 day. The solution is to use a custom number format that will displa... Read more
How to remove time from dates
Fri, Apr 15, 2022
Hi - Dave here. How can you easily remove times from dates, leaving the dates alone? The solution is easy, but you need to understand dates and times in Excel. In short, Excel dates are just serial numbers and Excel times are just fractions of 1. For example, the date "April 15, 2022" is the serial number 44666. The date and time "April 15, 2022 12:00 PM" is the number 44666.5. The .5 portion of the number represents 12:00 PM, one half of 1 day. In other words, time shows up as a decimal value a... Read more
Rank and score with INDEX and MATCH
Fri, Apr 08, 2022
Hi - Dave here. I had an interesting question this week about how to assign points based on rank. Specifically, how to rank scores, and then award points based on the rank of each score. In the worksheet below, I used the RANK function to calculate rank in column D, and INDEX and MATCH to assign points in column E. The formula in E5 is: =INDEX(tblPoints[Points],MATCH([@Rank],tblPoints[Rank],1)) [download the workbook and read the full explanation] In the example above, everybody gets at least 3 ... Read more
Count rows that contain a specific value
Fri, Mar 25, 2022
Hi - Dave here. It's easy to count total values in a range with the COUNTIF function. For example, to count cells that contain 19 in the data below, we can use COUNTIF like this: =COUNTIF(data,19) // returns 7 However, to count rows that contain 19, we need a more advanced formula. In the worksheet below, I show two options: one based on the MMULT function, and one based on the newer BYROW function. [download the workbook and read the full explanation] Read the article for details and to dow... Read more
Dynamic summary count (formula)
Fri, Mar 18, 2022
Hi - Dave here. What's a good way to create a dynamic summary count with a formula? In the article below I walk through several options, from simple to very advanced. The goal is to build a simple summary count that will automatically update to show new values and counts when the source data changes. [download the workbook and read the full explanation] See the article for progressively more advanced options including all-in-one formulas. The more advanced formulas rely on new dynamic array fu... Read more
Range contains duplicates
Fri, Mar 11, 2022
Hi - Dave here. How can you test if a range contains duplicate values? This is one of those Excel problems that seems simple but is actually a bit tricky. One nice approach is to use the COUNTIF function with the OR function: =OR(COUNTIF(range,range)>1) This elegant formula uses COUNTIF to generate an array of counts, and the OR function to evaluate results. The worksheet below shows the formula in use: [Download the workbook and read the full explanation] Note: this is an array formula and must... Read more
Random numbers without duplicates (formula)
Fri, Mar 04, 2022
Hi - Dave here. Excel has several functions for generating random numbers, including RANDBETWEEN, and RANDARRAY. However, there is no guarantee that the numbers from these functions will be unique. To get a list of unique random numbers, you need a more robust formula. For example, the formula below generates 1000 random numbers starting at 10,000, sorts the numbers randomly, then extracts the first 12 numbers. Because the original numbers are unique, the final numbers contain no duplicates. [Do... Read more
VLOOKUP with multiple criteria
Fri, Feb 18, 2022
Hi - Dave here. Last week, I shared a formula that performs a case-sensitive lookup with VLOOKUP. This week, I want to share a formula that uses exactly the same trick to make VLOOKUP work with multiple criteria: [download the workbook and read the full explanation] The approach is the same as before: apply logical conditions to the data and create a new table that VLOOKUP can use to find the correct result. Note: This is an advanced technique. With XLOOKUP or INDEX and MATCH, the formulas are... Read more
If you would like to receive our newsletter, sign up using the form below. You can unsubscribe at any time.