Exceljet

Quick, clean, and to the point

Formulas

Excel formula: Add months to date
The EDATE function is fully automatic. Simply supply a valid date and a number of months and EDATE will return a new date. To subtract months from a date, supply a negative value. Adding years To move forwards and backwards in years from a certain date, you can multiply by 12 inside EDATE like this...Read more
Excel formula: Get day name from date
In this example, the goal is to get the day name (i.e. Monday, Tuesday, Wednesday, etc.) from a given date. There are several ways to go about this in Excel, depending on your needs. This article explains three approaches: Display date with a custom number format Convert date to day name with TEXT...Read more
Excel formula: Convert Excel time to decimal hours
In the Excel time system, one 24-hour day is equal to 1. This means times and hours are fractional values of 1, as shown in the table below: Hours Time Fraction Value 1 1:00 AM 1/24 0.04167 3 3:00 AM 3/24 0.125 6 6:00 AM 6/24 0.25 4 4:00 AM 4/24 0.167 8 8:00 AM 8/24 0.333 12 12:00 PM 12/24 0.5 18 6...Read more
Excel formula: Left lookup with INDEX and MATCH
One of the advantages of using INDEX and MATCH over another lookup function like VLOOKUP is that INDEX and MATCH can easily work with lookup values in any column of the data. In the example shown, columns B through E contain product data with a unique ID in column E. Using the ID as a lookup value...Read more
Excel formula: Count or sum variance
In this example, the goal is to sum or count a set of variances in different ways. Variances are listed in D5:D15, which is also the named range variance . The first formula in F5 simply sums all variances with the SUM function . = SUM ( variance ) // returns -175 Sum absolute variances The formula...Read more

Videos

In this video we build a formula that counts words in a cell with the LEN and SUBSTITUTE functions.
Run time: 3:44
In this video we'll look how to use the XLOOKUP function with Boolean logic to apply multiple criteria.
Run time: 3:05
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 video, we'll look at how to highlight the results of approximate match lookups with conditional formatting.
Run time: 3:55
In this lesson, we demonstrate a shortcut recipe you can use to remove blank rows quickly.
Run time: 1:38

Blog

How to concatenate in Excel

Concatenation is one of the most important operations in more advanced formulas. This article explains how to perform concatenation manually with the ampersand operator (&) and with the three Excel functions designed for concatenation: CONCATENATE, CONCAT, and TEXTJOIN.

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.

Feedback

You do a fantastic job of informing people how to use this powerful and complex software. Just applying what I have learned on your site has allowed me to automate and streamline many cumbersome updates and company communications into a set of files that anyone in my company can open and find what they need, when they need it. Thank you for the priceless education. -AJ
I just wanted to let you know that after years of being afraid of formula (we never learned Excel at school) and relying on others to do the complicated stuff - i found your website and it is now my go-to every time. I have learned so much and pretty self-sufficient now. I really appreciate your approach, clarity and logic when breaking down a formula and how it could be used. -Anna
Simple, effective help. Answered my question in seconds. Thank you very much for providing this platform. -Daniel