Exceljet

Quick, clean, and to the point

Formulas

Excel formula: Remove text by variable position
The REPLACE function will replace text by position. You can use REPLACE to remove text by providing an empty string ("") for the "new_text" argument. In this case, we want to remove the labels that appear inside text. The labels vary in length, and include words like "Make", "Model", "Fuel economy...Read more
Excel formula: Get work hours between dates custom schedule
At the core, this formula uses the WEEKDAY function to figure out the day of week (i.e. Monday, Tuesday, etc.) for every day between the two given dates. WEEKDAY returns a number between 1 and 7. With default settings, Sunday=1 and Saturday = 7. The trick to this formula is assembling an array of...Read more
Excel formula: Longest winning streak
This is a tricky formula to understand, and it requires that you have a numeric id for each match, and that all matches are sorted by id. They key is in understanding how FREQUENCY gathers numbers into "bins". Each bin represents an upper limit, and generates a count of all numbers in the data set...Read more
Excel formula: Get middle name from full name
Assuming you have a full name in column B, a first name in column C, and a last name in column D, you can use a formula that looks like this: = TRIM ( MID ( B5 , LEN ( C5 ) + 1 , LEN ( B5 ) - LEN ( C5 & D5 ))) At the core, the MID function extracts text from the full name starting at 1...Read more
Excel formula: Calculate interest for given period
For this example, we want to calculate the interest portion for payment 1 of a 5-year loan of $5,000 with an interest rate of 4.5%. To do this, we set up PPMT like this: rate - The interest rate per period. We divide the value in C6 by 12 since 4.5% represents annual interest: = C6 / 12 per - the...Read more

Videos

In this video, we'll look at how to use the FILTER function together with the UNIQUE function to limit results using logical criteria.
Run time: 3:08
In this video we show how to use the UPPER, LOWER, and PROPER functions to transform case with a formula.
Run time: 2:59
In this video I'll show you how to create a formula that uses multiple, nested IF statements. This is a common technique to handle multiple conditions in a single formula.
Run time: 3:15
In this lesson, we cover shortcuts you can use to to enter data in one or more cells at a time. Knowing these tips can really speed up editing in Excel.
Run time: 3:33
In this video, we'll look at how add and display rank in a pivot table.
Run time: 3:09

Blog

What is an array formula?

In the world of Excel formulas, the term "array formula" is probably responsible for more confusion than just about any other concept. This is because the definition of an array formula has become mixed up with the requirement to enter some array formulas in a special way, with control + shift + enter.

Excel's RACON functions

There are eight functions in Excel that work differently than you might realize. The same features that were supposed to make them easier to use, can actually make them harder to use :) Read on for important information about COUNTIF, COUNTIFS, SUMIF, SUMIFS,  AVERAGEIF, AVERAGEIFS, MINIFS, and MAXIFS.

This article provides examples of public Coronavirus data you can download to Excel with Power Query. Each example has a link, a screenshot to show what the data looks like in Excel after being imported, and an Excel workbook.

Tracking COVID-19 with Excel

A quick example of how to track testing for COVID-19 using Excel and publicly available data. In this project, the data is fetched and "shaped" with Power Query, then dropped back into Excel, where it can be refreshed with a single click.

Dynamic Excel offers 6 brand new functions that solve hard problems in Excel like sorting, filtering, and working with unique values. For those not using Office 365, this page provides some alternative formulas that work in older versions of Excel.

Feedback

My excel savior! I'm so grateful for the existence of this site.*please receive a bow from me* -Franny
Hi guys, I just wanted to thank you for your super clear instructions on a tricky Excel problem I was tackling this morning - your simple-to-follow descriptions and instructions gave me the answer I needed straightaway! I have now bookmarked your website and subscribed to your email! Well done and thank you!!! -Cat
Thank you for helping me save countless hours and frustrations with a quick search and awesome, easy to read info. ExcelJet is always my first go-to resource when trying to untangle something in Excel. I can't thank you both enough <3 <3 <3 -Alexis