Quick, clean, and to the point


Criteria are a key concept in Excel, but building useful criteria for text, numbers, dates, times, etc. is hard because it requires a good understanding of how Excel handles data. This guide will help you build formulas that work the first time with over 50 examples.
Every once in a while, you might find Excel behaving in a bizarre or unexpected way. One example is when you accidentally trigger the scroll lock feature. Another example is when one or more formulas suddenly stops working. Instead of a result, you see only a formula. What?
Excel Tables have a boring (and confusingly generic) name, but they are packed with useful features. This article is a summary of the things you should know about Excel Tables.
In this challenge, the goal is to end up with a text string like "MWF" for Monday, Wednesday, Friday. The problem is that the weekdays are input like as yes/no abbreviations like "NYNYNYN" for "MWF". What formula will translate the "N" and "Y" to weekday abbreviations?
Every day, thousands of questions about Excel are posted on the internet. Many of these questions go unanswered because they are unclear. This article contains some tips on how to write a question that people will quickly understand and answer if they can.
One problem that comes up a lot in Excel is counting or summing based on multiple OR conditions. For example, perhaps you need to analyze data and count orders in Seattle or Denver, for items that are Red, Blue, or Green? This can be surprisingly tricky, so naturally it makes a good challenge!

We have a simple list of 4-digit alphanumeric codes like A001, A002, A003, etc. What formula can we use to mark codes are "out of sequence" with previous entries? This problem includes two separate challenges.

In this formula challenge, we have data showing cups of coffee sold at a small kiosk for a week. What formula will look up and sum total cups sold after 12:00 PM on Tuesday and Thursday? Relevant cells are shaded in green.

28 ways to save time with Excel formulas
Working with formulas takes time, and too often a problem that seems simple ends up taking far too long. In this article, I share tips that will save you time when working with formulas in Excel.

In this formula challenge, we have four tests (Test A, Test B, Test C, and Test D), all with the same 19 questions, but listed in a different order. What formula can be used to match questions and construct an answer key for all tests? A perfect use for Excel's powerful lookup formulas.

This article explains in simple terms how to use INDEX and MATCH together to perform lookups. It takes a step-by-step approach, first explaining INDEX, then MATCH, then showing you how to combine the two functions together to create a dynamic two-way lookup.
Custom number formats
Custom number formats control how numbers are look in Excel. The key benefit is that they change how a number looks without changing any data. They are a great way to save time in Excel because they perform a huge amount of formatting automatically. As a bonus, they make worksheets look more professional.

The challenge - calculate the difference from the current and last entry, even when previous entries are skipped. This is one of those problems that seems simple at first glance, but is tricky when you look closely.

How to make dependent dropdown lists
Once you start to add dropdown menus to your spreadsheets, you'll inevitably run into a challenge: how can you make one dropdown dynamically respond to another? More generally, how can you make the values in a dropdown list depend on another value in the worksheet?
Excel data validation guide
Data validation can help control what a user can enter into a cell. You could use data validation to make sure a value is a number, a date, or to present a dropdown menu with predefined choices to a user. This guide provides an overview of the data validation feature.
Generate weekend dates only with a simple formula
Sure, you can use a complicated WEEKDAY formula to generate a list of weekends. But with WORKDAY.INTL, you can do the same thing with a WAY simpler formula. The trick is to use a special 7-digit "mask" to filter out all dates except weekends.

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.