Exceljet

Quick, clean, and to the point

Formulas

Excel formula: If this AND that

If this AND that

To do something specific when two or more conditions are TRUE, you can use the IF function in combination with the AND function to evaluate conditions with a test, then take one action if the r esult is TRUE, and (optionally) do take another if the result of the test is FALSE. In the example shown...Read more
Excel formula: Partial match with VLOOKUP

Partial match with VLOOKUP

In this example, the goal is to retrieve employee information from a table using only a partial match on the last name. In other words, by typing "Aya" into cell H4, the formula should retrieve information about Michael Ayala. The VLOOKUP function supports wildcards , which makes it possible to...Read more
Excel formula: Last n days

Last n days

In the image shown, the current date is August 19, 2019. Excel dates are serial numbers , so you can manipulate them with simple math operations. The TODAY function always returns the current date. Inside the AND function , the first logical test checks to see if the date in B5 is greater than or...Read more
Excel formula: Last n weeks

Last n weeks

In the image shown, the current date is August 24, 2019. Excel dates are serial numbers , so they can be manipulated with simple math operations. The TODAY function always returns the current date. Inside the AND function , the first logical test checks to see if the date in B5 is greater than or...Read more
Excel formula: Calculate years between dates

Calculate years between dates

The YEARFRAC function returns a decimal number representing the fractional years between two dates. For example: = YEARFRAC ( "1-Jan-2019" , "1-Jan-2020" ) // returns 1 = YEARFRAC ( "1-Jan-2019" , "1-Jul-2020" ) // returns 1.5 Here are a few examples of the...Read more
more formulas

Videos

How to filter a pivot table to show top values

In this video, we show you how to filter your pivot table to show top and bottom values.
Run time: 1:57

Filter with dynamic dropdown list

In this video, we'll build a dropdown list using dynamic arrays to filter data by color. The dropdown list is created with data validation.
Run time: 2:55

How to build a combo chart

In this video, we'll look at how to build a combo chart. A combo chart is a name for a chart that combines more than one chart type.
Run time: 2:51

How to clean text with CLEAN and TRIM

This video shows you how to use two text functions, CLEAN and TRIM, to strip extra spaces and remove line breaks from text.
Run time: 2:22

What's a relative reference?

In this video we explain the concept of a relative cell reference; perhaps the most important concept in Excel formulas.
Run time: 2:38
more videos

Blog

Why SUMPRODUCT?

In this article, I attempt to explain why you see SUMPRODUCT so often in formulas, and when you can use the SUM function instead. The short version: SUMPRODUCT supports array operations natively, which makes it very useful for solving seemingly unrelated Excel problems. In the current version of Excel, you can use the SUM instead, but SUMPRODUCT is better for backwards compatibility.

How to concatenate in Excel

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?

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.

more articles

Feedback

Very satisfied with the product! It has made me more efficient at work! -Evan
I just wanted to let you know that I love your website. Your examples are clear and relatable, and I almost always find the answer I'm looking for. I've learned so much over the past few months. -Karen
I want you to know how much I appreciate your emails with very helpful information! I’m getting to the point that I can talk about excel with my son-in-law instead of always asking questions/guidance! So thank you so much. -Karen
more feedback
We offer free resources and paid training.