Exceljet

Quick, clean, and to the point

Formulas

Excel formula: Replace one character with another

Replace one character with another

The SUBSTITUTE function is full automatic. All you need to do is supply "old text" and "new text". SUBSTITUTE will replace every instance of the old text with the new text. If you need to perform more than one replacement at the same time, you'll need to nest multiple SUBSTITUTE functions. See the...Read more
Excel formula: Flip table rows to columns

Flip table rows to columns

The TRANSPOSE function is fully automatic and can transpose cells vertical to horizontal, and vice versa. The only requirement is that there be a one to one relationship between source and target cells. In the example shown, we are transposing a table that is 2 columns by 7 rows (14 cells), to a...Read more
Excel formula: Average and ignore errors

Average and ignore errors

In this example, the goal is to average a list of values that may contain errors. The values to average are in the named range data (B5:B15). Normally, you can use the AVERAGE function to calculate an average. However, if the data contains errors, AVERAGE will return an error. You can see this in...Read more
Excel formula: If Monday, roll back to Friday

If Monday, roll back to Friday

The WEEKDAY function returns a number, 1-7, that corresponds to particular days of the week. By default, WEEKDAY assumes a Sunday-based week, and assigns 1 to Sunday, 2 to Monday, and so on, with 7 assigned to Saturday. In this case, we only want to take action if the date in question is Monday. To...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
more formulas

Videos

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

How to chart sunrise and sunset

In this video, we'll look at how to plot average daylight hours per month in a chart, using sunrise and sunset data.
Run time: 3:49

What is an array?

The term "array" comes from programming, but you'll hear it often in the context of more advanced Excel formulas. In this video, we'll answer the question "what is...
Run time: 2:34

What is an array formula?

In this video, we'll answer the question: What is an array formula? The term "array formula" is probably responsible for more confusion than just about any other concept in...
Run time: 2:53

How to use VLOOKUP to merge tables

This video shows how to use VLOOKUP to merge data in two tables based on a common ID. You can even use this technique to reorder the columns you retrieve.
Run time: 3:06
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

I just want to say thanks for these emails. I’m not sure if people let you know or not but I love getting these and find them useful. You are making a difference in people’s lives. -Shane
Just signed up. Very, very impressed with your site. Looking forward to using so many of your great resources. PLEASE keep up the great work! -Troy
I wanted to say how much I love your website. I never used excel before my current position, which I started in 3 years ago, and I have since had to heavily rely on Excel for many, many things. I've looked at many different websites throughout my time in my current position, and by far I love yours the most. Your explanations are simple, straight forward, and have brought me to a point I feel extremely confident with my skills in excel. -Daniel
more feedback
We offer free resources and paid training.