Exceljet

Quick, clean, and to the point

Formulas

Excel formula: If else
If you need to test a condition, then take one action if the condition is TRUE, and another action if the condition if FALSE, you can use the IF function. The IF function has the concept of "else" built-in. The first argument is the logical test, the second argument is the result (or calculation)...Read more
Excel formula: Round by bundle size
The gist of this formula is that it figures out bundles needed, given items needed, and a specific bundle size. For example, if you need 6 items, and the bundle size is 2, you'll need 3 bundles. If you need 3 items, and the bundle size is 5, you'll need 1 bundle (and you'll end up with 2 extra...Read more
Excel formula: Dynamic reference Table name
This formula behaves like these simpler formulas: = SUM ( West [ Amount ]) = SUM ( Central [ Amount ]) = SUM ( East [ Amount ]) However, instead of hardcoding the table into each SUM formula, the table names are listed in column K, and the formulas in column L use concatenation to assemble a...Read more
Excel formula: Count items in list
The COUNTIFS function takes range/criteria pairs, and delivers a count when all criteria match. This example, contains two range/criteria pairs. In pair 1, the range is B:B (entered as a full column reference) and the criteria is B5. By itself, this pair would return a count of every value in...Read more
Excel formula: Random date between two dates
The RANDBETWEEN function takes two numbers, a bottom and top number, and generates a random integer in between. Dates in Excel are serial numbers, so you can use the DATE function to create t he lower number and the upper number. RANDBETWEEN then generates a number that falls between these two date...Read more

Videos

In this video, you find your way around an Excel workbook, and how to see where you are now.
Run time: 2:32
In this video, we look at how to configure VLOOKUP to look up values based on an approximate match. This is good for tax rates, postage, commissions, and the like.
Run time: 2:53
In this video, we'll look at how to apply conditional formatting to one cell based on values in another, using a formula.
Run time: 2:24
In this video we demo how to join different values together in one cell with line breaks. This also includes a clever way to add spaces and commas without using double quotes.
Run time: 3:15
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

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 :)
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

Your site is the best I've found to date. It is incredibly clear and helpful. -Rene
You have explained everything so perfectly. I was always trying to learn match and Index. Your article on it was so detailed and easy to understand. Loved it. -Shashank
Your e-mail updates are very helpful and I learn something with each one. Great job and keep them coming! -Karen