Exceljet Newsletter

New way to split a text string

March 21, 2025

Hi - Dave here. Happy Friday! I've been bothered for a long time that there isn't an easy way to convert a text string in Excel to an array of characters. Up to now, the best way is to use SEQUENCE with the MID and LEN functions like so: =MID(A1,SEQUENCE(1,LEN(A1)),1) This formula counts characters with LEN and then uses the count to generate a numeric sequence with SEQUENCE. Then, it uses the numeric sequence to ask MID for one character at a time...get 1 character starting at position 1, get 1... Read more

New Exceljet chatbot

March 14, 2025

Hi - Dave here. Happy Friday! I'm excited to announce that we've just launched the Exceljet Chatbot - an AI assistant that answers your Excel questions using Exceljet's trusted content for its knowledge base. You can try it our yourself here: https://chat.exceljet.net/ Here are some sample questions you can try:   XLOOKUP with multiple criteria FILTER example How to create a pivot table? How to get unique values from a list? Autosum shortcut When answering your question, the chatbot will pull fr... Read more

Ugly formula makeover with TEXTBEFORE

March 7, 2025

Hi - Dave here. Happy Friday! A few years go, I shared the formula below to remove the last word from a text string: =MID(B5,1,FIND("~",SUBSTITUTE(B5," ","~",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))))-1) So much ugly code for such a simple task! Thankfully, Excel now has much better tools to solve this problem. This week, I finally got a chance to update the example. The new formula now looks like this: =TEXTBEFORE(TRIM(B5)," ",-1) I think you'll agree that the new version is much prettier 🙂 [Download... Read more

Sum if with multiple ranges

February 28, 2025

Hi - Dave here. Happy Friday! A couple of weeks ago, a reader asked me how to conditionally sum values in more than one range. More specifically, how to sum quantities by color, as seen in the workbook below. Traditionally, this is a tricky problem in Excel because functions like SUMIFS aren't made to accept more than one range. Instead, you need to use SUMIFS more than once. For example, to sum the quantities for Red, you could use a formula like this: =SUMIFS(C5:C12,B5:B12,"red")+SUMIFS(F5:F11... Read more

Step-based lookup formula example

February 21, 2025

Hi - Dave here. Happy Friday! Last week, I shared a worksheet to calculate US income taxes in brackets, updated with 2024 tax rates. One of the techniques I used is a "step-based lookup formula". This is a clever way to navigate data that follows a pattern. Unless you've seen this trick before, you might not understand how it works, so I have a simpilfied example to explain the idea. In the worksheet below, we have three tiers of pricing (Bronze, Silver, and Gold) listed in columns B and C. We w... Read more

Tax rate worksheet updated for 2024

February 14, 2025

Hi - Dave here. Happy Friday! US taxes are due just around the corner in April, so I wanted to let you know that I've updated my income tax bracket calculation worksheet to include 2024 rates. I've also modified the worksheet to store tax rates rates for different years. This means you can change the year to compare the effect of tax rate changes. You can see what this looks like in the worksheet below, where both C4 and F4 contain dropdown menus: [Download the workbook and read the full explana... Read more

New function: TRIMRANGE

February 7, 2025

Hi - Dave here. Happy Friday! This week, I want to introduce TRIMRANGE, a geeky but cool new function that solves an important problem in Excel – how to create a simple dynamic range with a formula. A dynamic range is a range that expands and contracts with your data. This is a tricky problem and, in the past, it required a complicated formula like this: =OFFSET(B5,0,0,COUNTA($B$5:$B$100),COUNTA($B$4:$Z$4)) With TRIMRANGE, you can use a simple formula like this: =TRIMRANGE(range) In fact, you ca... Read more

Introducing the TRANSLATE function

January 24, 2025

Hi - Dave here. Happy Friday! I'm still catching up on all the new functions the Excel team has added. A new function called TRANSLATE is pretty interesting. As the name suggests, this function will translate the text in a cell from one language to another. You can see an example of how TRANSLATE can be used in the worksheet below, where we dynamically translate instructions for making a peanut butter sandwich based on the language selected in a dropdown menu: [Download the workbook and read the... Read more

How to build a cash denomination calculator

January 17, 2025

Hi - Dave here. Happy Friday! This week, I want to explain how to build a "cash denomination calculator." A cash denomination calculator is a tool for counting and verifying cash amounts. You can see an example in the worksheet below. The idea is to calculate the correct counts for the denominations in row 4, given the amounts in column B. For example, the counts for the amount $32 in cell B9 should be 1 x $20, 1 x $10, and 2 x $1, which add up to $32. [Download the workbook and read the full ex... Read more

Simple investing worksheet

January 10, 2025

Hi - Dave here. Happy New Year! A while back, I got from a reader about investing for his grandkids: I hope you can assist me with an Excel calculation. I am keen to show my grandchildren the power of compound interest and the value of investing (patiently) for the long term. And I want to graph this table of annual returns to demonstrate the effect of compounding. I have started an investment program and have invested $100 per quarter for each grandchild for several years. I want to give each o... Read more

XLOOKUP with regex (new)

December 13, 2024

Hi - Dave here. Happy Friday! Last week, I shared an article introducing Regular Expressions (regex) in Excel. This is a big upgrade in Excel's text-handling capabilities! I also mentioned that two functions, XLOOKUP and XMATCH, now also support regex. What does that mean, exactly, and how might you use it? Great question! The main benefit of regex with XLOOKUP (and XMATCH) is the ability to construct very specific matching patterns for your lookups. For example, in the worksheet below, we have ... Read more

Regular Expressions in Excel!

December 6, 2024

Hi - Dave here. Happy Friday! Big news...Microsoft just added regex support to Excel! After decades of clunky workarounds and terribly complicated formulas, you can now use three powerful new functions - REGEXTEST, REGEXREPLACE, and REGEXEXTRACT - to handle the trickiest text operations. For example, the workbook below shows the "before" formula used to extract the number from each product code. The full formula in D5 looks like this: =TEXTJOIN("",TRUE,IFERROR(MID(B5,SEQUENCE(LEN(B5)),1)+0,"")) ... Read more

How to add years to a date

November 22, 2024

Hi - Dave here. Happy Friday! Ever needed to calculate someone's retirement date? Or figure out when a 30-year mortgage will end? Or maybe you're setting a contract expiration date? In each case, you need a way to add years to a date. Here's something that might surprise you: most Excel users tackle this problem with a formula that's 3x more complex than it needs to be. And worse, it can fail in leap years. You can see the formula in the worksheet below. Can you spot the problem in the results? ... Read more

Sum if with multiple columns (SUMIFS fail!)

November 15, 2024

Hi - Dave here. Happy Friday! The SUMIFs function is useful for all kinds of things, but there are some "simple" problems it just can't solve. I ran into this recently in the worksheet below. The goal is to calculate a sum for each group (A, B, and C) across all three months of data in the range C5:E16. In other words, we want to perform a "SUMIF" with data in three columns. You might think this is a perfect fit for SUMIFS, but no. The formula below returns a #VALUE! error. The problem is that S... Read more

500 Excel functions + sale ends today

November 8, 2024

Hi - Dave here. Happy Friday! Did you know that Excel has over 500 functions? It's true. Plus, the Excel team adds more every year! Functions are the building blocks of the formulas you use daily in Excel. 500 is just a crazy big number, and I've been working for more than 10 years on pages that describe each function. So far, I've published almost 400* in a list here: [Exceljet list of functions] Each function links to a detailed page (example here), where I explain how the function works in th... Read more

1000 Excel formulas!

November 1, 2024

Hi - Dave here. Happy Friday! About 10 years ago, I started to document Excel formulas. I like to work by example, and for me, that's the best way to learn. I also wanted my own notes on what worked, what didn't, and why. Sometime in the past year or so, I published my 1000th Excel formula. It's been quite a ride, and I've learned a lot about Excel along the way! You can find the complete list of formulas here: 1000 Excel Formulas I use the list a lot myself! That may sound strange, but I've per... Read more

Making a formula better (example)

October 25, 2024

Hi - Dave here. Happy Friday! This week, I have an example of an older formula I reworked recently to simplify and streamline the logic using some tricks I've learned over the years. The formula is simple; it marks dates that share the same month and year. The original formula looks like this: =IF(AND(YEAR($B$5)=YEAR(D5),MONTH($B$5)=MONTH(D5)),"x","") The improved formula looks like this: =IF(TEXT($B$5,"mmyyyy")=TEXT(D5,"mmyyyy"),"x","") Notice that we've cut the number of function calls in half... Read more

Excel's new IMAGE function

October 11, 2024

Hi - Dave here. Happy Friday! This past week, I finally took a look at Excel's new IMAGE function, which is designed to fetch images from the Internet into Excel with a formula. What's cool about IMAGE is that it allows you to programmatically import an image at a known location with just a formula.  You can see how it works in the worksheet below, where the IMAGE function has been configured to fetch 6 different country flags based on the URLs in column C: [Download the workbook and read the fu... Read more

XLOOKUP with horizontal data

October 4, 2024

Hi - Dave here. Happy Friday! One of XLOOKUP's nice benefits is its ability to work with vertical or horizontal data. The syntax used for horizontal lookups is the same as for vertical lookups. In the past, you would typically use VLOOKUP for vertical data and HLOOKUP for horizontal data. But XLOOKUP makes this choice unnecessary. For example, in the worksheet below, we want to determine the right discount for a given quantity. Because the table is horizontal, the "old" solution is based on HLOO... Read more

Almost 50 New Excel Functions!

September 27, 2024

Hi - Dave here. Happy Friday! For a very long time, Excel introduced new functions at a leisurely pace. Every few years, a handful of new functions would appear, most aimed at technical and edge-case problems. Most users greeted these new functions with a yawn, if they noticed at all. All that changed in 2019 when Microsoft's Excel team kicked things into high gear and suddenly began introducing brand-new functions at a furious pace. You might not know it, but Excel now has nearly 50 new functio... Read more

How to extract a date from a test string

September 13, 2024

Hi - Dave here. Happy Friday! How can you extract a date like "6/17/24" from a text string? A classic way is to use a formula based on the MID and SEARCH functions. You can see this approach below, where the formula in cell D5 is: =MID(B5,SEARCH("??/??/??",B5),8)+0 [Download the workbook and read the full explanation] This works pretty well, but the formula is not very robust because it will also match strings like "AA/BB/CC" or even "AAAA/BB/CCCC". The problem is that Excel wildcards are quite ... Read more

How to validate a strong password

September 6, 2024

Hi - Dave here. Happy Friday! How can you validate a "strong" password with a formula in Excel?   At least 8 and not more than 15 characters long Contains at least one uppercase (A-Z) letter Contains at least one lowercase (a-z) letter Contains at least one number (0-9) Contains at least one punctuation character Contains no whitespace Historically, this has been a challenging problem in Excel because there have not been great tools for the job. You have to cobble together many functions to perf... Read more

Dynamic tax brackets

August 30, 2024

Hi - Dave here. Happy Friday! Last week, I shared a formula to calculate tax brackets with a formula in Excel. However, I did not include a way to easily change the taxpayer status (e.g., married vs. single), which affects how taxes are calculated. This week, I've updated the worksheet to allow dynamic selection of taxpayer status. Now, when a different status is selected, the correct rates are fetched from a master tax rate table: [Download the workbook and read the full explanation] The update... Read more

Income tax bracket formula

August 23, 2024

Hi - Dave here. Happy Friday! What's the best way to calculate income tax brackets in Excel? Well, the math is quite simple, but the implementation can be tricky, and there are many approaches. However, I like this simple and elegant formula based on Excel's new dynamic array engine: =LET(   income,I4,   upper,C5:C11,   lower,DROP(VSTACK(0,upper),-1),   IF(income<=lower,0,     IF(income>upper,upper-lower,income-lower)) ) You can see this approach in the worksheet below, where this single formula... Read more

Tiered discounts based on quantity (formula)

August 16, 2024

Hi - Dave here. Happy Friday! The workbook below applies discounts based on seven pricing tiers. Each tier has a different unit price, which decreases as the quantity increases. The tricky part of this problem is to calculate the correct quantities in the range D7:D13. Essentially, we need to distribute the value in C4 according to the thresholds in column C: [Read the article and download the workbook] In the article, I explain two ways to solve this problem. One way is to use the MAP function ... Read more

How to find matching values in a table

August 9, 2024

Hi - Dave here. Happy Friday! One of the most interesting aspects of the new functions in Excel is how they can radically change the way certain problems are solved. For example, over the years I've wanted a formula that can easily report the location(s) of a value in a table. This seems like it would be a simple problem in Excel, which is all about cell addresses, but it's not in older versions of Excel. However, I looked at this challenge again recently and realized that Excel now has the tool... Read more

STOCKHISTORY function - 8 examples

August 2, 2024

Hi - Dave here. Happy Friday! Sorry for the long delay between emails! Lisa and I have been traveling... I was updating our page on the STOCKHISTORY function recently and wanted to mention that I've finally created a workbook with many examples. The STOCKHISTORY function was introduced in Excel a few years ago and has one primary purpose: to retrieve historical stock price information to Excel. It's a pretty cool function that lets you get daily, weekly, and monthly stock price information into ... Read more

Mini-dashboard with FILTER and CHOOSECOLS

July 12, 2024

Hi - Dave here. Happy Friday! One of the coolest things about the new functions in Excel is how easily you can build a mini-dashboard report with a single formula. For example, in the worksheet below, the goal is to display projects over 90,000, sorted by value in descending order. In addition, we only want the name of the project and the value. This is all done with this formula in cell F4: =CHOOSECOLS(SORT(FILTER(B9:G20,E9:E20>90000),4,-1),1,4) The beauty of this approach is that the source da... Read more

How to generate quarter dates

June 28, 2024

Hi - Dave here. Happy Friday! How can you generate a list of quarter dates in Excel with a formula?   This is an interesting problem that you can neatly solve with two old functions, EDATE and EOMONTH, and one new function, SEQUENCE. You can see the result in the worksheet below, where the formula in cell D5 is: =EDATE(B5,SEQUENCE(12,,0,3))   With a start date of 1-Jan-2024 in cell B5, the formula returns the next 12 quarter start dates. A similar formula in cell E5 generates the next 12 quarter... Read more

How to calculate hours between two times

June 21, 2024

Hi - Dave here. Happy Friday! One of the weirdly complicated problems in Excel is calculating the hours between two times. In part, this is because Excel stores time as fractional values. For example, 0.25 is 6:00 AM or 6:00 hours, depending on formatting. The number 0.25 makes sense when you consider that 6 hours is one-quarter of a day, and a day in Excel equals 1. But it isn't the way most people think about time. The other reason time is complicated is that it resets to zero again at midnigh... Read more

Calculate time before expiration date (3 formulas)

June 7, 2024

Hi - Dave here. Happy Friday! All food products have expiration dates and you might want a simple way to track how much time remains before a product expires. A perfect job for Excel :) In this example, I provide 3 formulas that get the job done in different ways: 1. Calculate remaining time in days 2. Calculate remaining time in years, months, and days 3. Calculate remaining time as a percentage of shelf life In the worksheet below, you can see the first approach: [Download the workbook and rea... Read more

What is a workday in Excel?

May 31, 2024

Hi - Dave here. Happy Friday! What is a workday in Excel? The main way to answer that question is the WORKDAY function. WORKDAY is designed to return a date in the future or past that is a given number of working days from a start date, excluding weekends and any holidays you specify. The way WORKDAY works can be confusing because it's hard to see a calendar in your head when you are looking at a formula. I wanted to visualize this process and, naturally, I wanted to use Excel to do it :) I came... Read more

How to extract numbers from a text string

May 24, 2024

Hi - Dave here. Happy Friday! Recently, I was playing with a new function in Excel called TOROW. This humble little function has just one job: transform an array into a single row. While this can be useful in certain problems, TOROW has another handy feature I had never really used - it can ignore errors or empty values. You can see a nice example of this feature in action below, where the goal is to extract property information from the text in column B. The core of this formula in C5 is: =TORO... Read more

The date is 45429

May 17, 2024

Hi - Dave here. Happy Friday! You may not realize it, but today is 45429. That is, May 17, 2024, is stored as the serial number 45429 by Excel. It is only by applying number formatting that Excel will display this number as a date. Number formats in Excel are very common, and you probably use them every day to control the display of numbers in Excel. However, if you try to join a date to a text string in Excel, you'll lose the number formatting. For example, with the date 17-May-2024 in cell A1,... Read more

Excel time in hundredths of a second

May 3, 2024

Hi - Dave here. Happy Friday! How do you work with time in hundredths of a second in Excel? For example, the race results in the worksheet below. This article offers tips to keep you from going crazy :) [Download the workbook and read the full article] The solution starts with applying the right number format. However, there are several quirks in Excel that you should be aware of when working with decimal seconds. The article above provides tips, formulas, and the workbook. Excel formulas We mai... Read more

How to count consecutive wins (formula)

April 26, 2024

Hi - Dave here. Happy Friday! How can you count consecutive wins in Excel? This is a traditionally challenging problem in Excel because it's not obvious how to solve it. Excel has many functions designed to count things, but none are dedicated to counting consecutive things. Until now, the simplest way to solve this problem is to create a helper column with a running count of wins, and then use the MAX function to find the maximum count. However, the SCAN function makes it easy to use one formul... Read more

Year is a leap year?

April 19, 2024

Hi - Dave here. Happy Friday! How can you test if a year is a leap year? If you are testing any year after 1900, you can do it with a formula like this: =MONTH(DATE(YEAR(date),2,29))=2 This is the formula in cell C5 of the worksheet below. [Download the workbook and read the full explanation] Of course, the devil is in the details. The clever formula above works great for years after 1900, but it will fail in 1900 and earlier for two reasons: (1) Excel incorrectly thinks 1900 is a leap year and ... Read more

Randomly assign people to groups

April 12, 2024

Hi - Dave here. Happy Friday! In my last email, I shared a simple formula to create random groups of data. This is a quick way to put rows of data into random groups, but the number of rows in each group will vary because the data is truly random. Is it possible to create random groups of equal size? Yes! The formula is longer because we need to perform some math. The basic process looks like this: 1. Generate random numbers for each row 2. Rank the random numbers 3. Count groups and calculate t... Read more

Randomly assign data to groups (formula)

April 5, 2024

Hi - Dave here. Happy Friday! An interesting problem in Excel is how to assign random groups to a set of data. For example, in the worksheet below, the goal is to randomly assign a group of "A", "B", or "C" to each row in the data. The formula in cell F5 looks like this: =CHOOSE(RANDBETWEEN(1,3),"A","B","C") As the formula is copied down it returns a random group ("A", "B", or "C") at each new row. The values to assign can be customized as you like. [Download the workbook and read the full expla... Read more

How to find all formula errors (tip)

March 29, 2024

Hi - Dave here. Happy Friday! 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: [Read the complete article here] Notice the list of errors includes multiple sheets. The asterisk (*) is a wildcard in Excel that will match any number of characters, so the idea he... Read more

Count errors on all worksheets

March 20, 2024

Hi - Dave here. 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")))) [Download the workbook and read the full explanation] This problem breaks ... Read more

How to combine data in multiple sheets (formula)

March 8, 2024

Hi - Dave here. Happy Friday! 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) [Download the workbook and read the full explanation] The 3D reference makes it eas... Read more

Extract common values from text strings

March 1, 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 more

Extract common values in two lists (formula)

February 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 thi... Read more

Categorize text with keywords (formula)

February 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 formula ... Read more

How to get numbers from TEXTSPLIT

February 9, 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 value... Read more

TEXTSPLIT with multiple delimiters - cool trick!

February 2, 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 tri... Read more

Excel's TEXTSPLIT function - new video

January 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 mi... Read more

TEXTAFTER with TEXTBEFORE - new video

January 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 engine... Read more

Excel's TEXTAFTER function - new video

January 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 bett... Read more

Count words in cell - new and improved!

January 5, 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 workboo... Read more

How to capitalize the first letter with a formula

December 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 more

Formula Friday - Clean up phone numbers

December 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 i... Read more

LET there be names

December 8, 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),   last... Read more

Get last name from name (cool formula)

December 1, 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 :) [Downl... Read more

Happy Black Friday!

November 24, 2023

Hi - Dave here. Happy Black Friday! Just a friendly reminder that today is the last day of our anniversary sale. For the rest of today, you can enjoy a 30% discount on any course or bundle with the special links below:   Individual courses Course bundles The sale ends at midnight US Pacific time. All courses have lifetime access with no expiration date. You will see the 30% discount applied on the checkout page. Free resources In addition to paid training, we offer many free resources as well: F... Read more

XLOOKUP w/ multiple criteria + sale continues

November 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 t... Read more

10-year anniversary sale + more SORTBY

November 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 more

How to sort by a substring

November 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 more

The SORTBY function, part 2

November 3, 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 watch... Read more

Why is the SORTBY function useful, part 1

October 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 more

Mortgage payment schedule - one formula!

October 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,p... Read more

Estimate your mortgage payment

October 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 more

FILTER with boolean logic - useful!

October 6, 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 so... Read more

List "Second Tuesdays" of the month

September 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 nex... Read more

Sequence of custom days (special trick)

September 1, 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 more

Dynamic list of workdays only (formula)

August 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 exclude... Read more

Sequence of days (formula)

August 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 an... Read more

How to extract the domain from an email address

August 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 more

How to create an email address with a formula

July 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 more

FILTER data between two dates

July 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 more

How to get the last match

July 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 more

Simple dynamic range with XLOOKUP

June 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, XLOOKUP... Read more

How to get information about the max value

June 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 more

INDEX and MATCH two-column lookup

June 9, 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 same ... Read more

XLOOKUP wildcard match

June 2, 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 more

Quantity based discount formula

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 more

If NOT this or that

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, w... Read more

If cell begins with x, y, or z

May 5, 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 more

How to find the closest match (formula)

April 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 more

XLOOKUP binary search

April 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 more

XLOOKUP vs INDEX and MATCH

April 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 more

XLOOKUP vs VLOOKUP - the pros and cons

April 7, 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 more

Basic INDEX and MATCH

March 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 more

INDEX and MATCH multiple criteria + approximate match

March 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 more

VLOOKUP and XLOOKUP without #N/A errors

March 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 more

Automatic row numbers

March 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. T... Read more

Geeky formula example

March 3, 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 you... Read more

Maximum change formula

February 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 more

Replace one delimiter with another (formula)

February 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 an... Read more

How to get the nth largest value (formula)

February 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 more

Average top 3 scores (formula)

February 3, 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 more

Highlight specific day of week (formula)

January 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 more

Use Paste Special for in-place changes (tip)

January 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 more

Average last n rows (formula)

January 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 more