# Blog

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.

Many advanced lookup formulas require INDEX and MATCH. This article explains how to use INDEX and MATCH together with a simple step-by-step approach.

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.

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?

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.

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?

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.