Exceljet

Quick, clean, and to the point

Blog

You have a fixed monthly payment, a start date, and a given number of months. You have five years shown on the worksheet. What formula can you use to sum total payments by year?

Friendly, dynamic messages with concatenation
In this article, we explain how to use concatenation to display friendly and useful messages in your spreadsheets. The messages are dynamic and respond instantly to changes, so the effect is polished and professional.
Use the EOMONTH function to get the last day of a month
The EOMONTH function is one of those little gems in Excel that can save you a lot of trouble. It's a simple function that does just one thing: given a date, it returns the last day of a month. You can use EOMONTH to build all kinds of useful Excel formulas.
Fiddles with new CONCAT & TEXTJOIN functions
The CONCATE and TEXTJOIN functions make it possible to join values together in a range of cells. Since a range is just an array, this opens the door to some interesting new formulas that loop through values.
How to create named ranges in Excel
Named ranges make formulas easier to read, faster to develop, and more portable. They're also useful for data validation, hyperlinks, and dynamic ranges. This article shows you how you can use named ranges to build better spreadsheets.
In this article, we show you how to replace a complicated IF statement with a clever and compact formula based on MIN or MAX. This is a great tip any time you need to choose the smaller or greater of two values inside a formula.
Conditional formatting with formulas
Formulas let you access the full power of Excel to apply conditional formatting with exactly the logic you need. This article shows how to highlight rows, column differences, missing values, and how to build Gantt charts and search boxes with CF formulas.
All about nested IFs
Are nested IFs evil? Are they necessary? Are there alternatives? The answer is Yes! This in-depth article explores nested IF formulas in detail, with lots of tips, and several alternatives.
Like a clock, the MOD function repeats
MOD is nerdy, but cool. If you've ever struggled to create a more complex formula in Excel, you'll like this one. It shows you how to build a more complex formula step-by-step, without going crazy.
How to set the default template used for new workbooks in Excel
Excel has the ability to use a custom template for all new workbooks. If you find yourself making the same changes to new workbooks (changing row height, font size, zoom, etc.) a default custom template can save you time and trouble.
Formulas for finding text in Excel
Does cell A1 contain "apple"? This is a surprisingly tricky problem in Excel. In this article, we look at several options, based on the functions FIND, SEARCH, ISNUMBER, and COUNTIF.
Excel shortcuts on a Mac
If you're used to working with Excel on Windows, one of the most confusing aspects of using Excel on a Mac is shortcuts. Excel shortcuts on the Mac are quite capable, you just have be aware of 5 key differences.
Popular Excel shortcuts
We asked users how important Excel shortcuts are to them and also about their favorite shortcuts. This is what we learned...
The evil scroll lock enabled in Excel 2010
Your report is due in an hour. You're working along in Excel, when suddenly you notice that you can no longer move around properly. Is it, perhaps, the evil Scroll Lock?
Learn Excel formulas and differentiate yourself
If you've ever wondered whether learning Excel formulas is worth your time, this list is for you. Formulas are the glue that hold spreadsheets together all over the world, and your skill with them can help you in many ways.
How to use VLOOKUP - things you need to know
Although VLOOKUP is a pretty simple function, there is plenty that can go wrong. Quickly learn more than 20 things you should know about VLOOKUP to make your experience pleasant and profitable.
By default, VLOOKUP will match in approximate mode, which can be disastrous. Learn how to avoid this problem by always setting the match mode yourself.

In this formula puzzle, we look at truck location GPS data, and ask: what formula will calculate the hours that each truck was stopped during the day?

How to test conditional formatting with dummy formulas
If you've ever tried to apply conditional formatting with a formula, you know the hardest part is making sure the formula actually works. Here's an easy way to test the formula, before you use it in a rule.
How do you figure out what formula to use? A teardown of 5 good formulas you can use to highlight dates based on a common month and year value using conditional formatting.

Pages

I love to use excel and use it a lot in my work. Your tips, tricks and examples have helped me enormously. - Lisa
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course