Exceljet

Quick, clean, and to the point

Formulas

Excel formula: Remove text by variable position

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

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

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

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

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

Videos

Unique values with criteria

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

How to change case with UPPER LOWER and PROPER

In this video we show how to use the UPPER, LOWER, and PROPER functions to transform case with a formula.
Run time: 2:59

How to create a formula with nested IFs

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

Shortcuts to enter data

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

How to rank with a pivot table

In this video, we'll look at how add and display rank in a pivot table.
Run time: 3:09
more videos

Blog

What is an array formula?

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

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.

Download Coronavirus data to Excel

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

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.

Alternatives to Dynamic Array Functions

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.

more articles

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
more feedback
We offer free resources and paid training.