Articles

Formula errors are useful because they tell you clearly that something is wrong in a worksheet. This guide shows examples of each of the Excel formula errors you might run into and provides information on how to investigate and correct the error. It also explains two methods to quickly find errors in a worksheet or workbook.

If VLOOKUP finds more than one match, will you get the first match or the last match? It's a trick question. It depends :) This article explains this confusing topic in detail, with lots of examples.

Most people never receive proper Excel training and spend years frustrated by the simplest tasks, especially tasks that involve formulas. To use Excel with confidence, you must have a good understanding of formulas and functions. This article introduces the basic concepts you need to know to be proficient with formulas in Excel.

The double negative coerces TRUE or FALSE values to their numeric equivalents, 1 and 0. This is a useful technique in many advanced formulas that work with cell ranges.

Excel contains over 500 functions, with more functions added every year. That is a huge number, so where should you start? This guide provides a walkthrough of over 100 important functions in Excel with many examples and links. Click function names for details and more examples.

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.

Have you entered a formula, but Excel is not showing a result? This can be very confusing, and you might think you've somehow broken your spreadsheet. However, it's likely a simple problem. With a little troubleshooting, you can get things working again.

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.

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.

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.

INDEX and MATCH is the most popular tool in Excel for performing more advanced lookups. This is because INDEX and MATCH are incredibly flexible – you can do horizontal and vertical lookups, 2-way lookups, left lookups, case-sensitive lookups, and even lookups based on multiple criteria. If you want to improve your Excel skills, INDEX and MATCH should be on your list. See below for many examples.

Number formats are a key feature in Excel. Their key benefit is that they change how numeric values look without actually changing any data. Excel ships with a huge number of different number formats, and you can easily define your own. This guide explains how custom number formats work in detail.

One of the most useful features of data validation is the ability to create a dropdown list that let users select a value from a predefined list. But how can you make one dropdown dynamically respond to another? In other words, how can you make the values in a dropdown list depend on another value in the worksheet? Read on to see how to create dependent dropdown lists in Excel.

Data validation can help control what a user can enter into a cell. You can 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, with many examples.

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.

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 CONCAT 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, and better formulas.

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.