New Excel Functions
Nearly 50 new functions have been added to Excel! This is not your Dad's Excel anymore – a lot has changed. This article takes a quick tour of the new functions, with links to more detailed information.
Articles
XLOOKUP vs INDEX and MATCH
For many years, INDEX and MATCH have been the go-to solution for difficult lookup problems in Excel. While more complicated to configure, the two-function combination of INDEX + MATCH is flexible and powerful. But now that XLOOKUP is more widely available, will INDEX and MATCH remain popular? Is there any reason to still use INDEX and MATCH? Read below for a detailed comparison.
See INDEX and MATCH Two-Column Lookup Example
XLOOKUP vs VLOOKUP
For many years, VLOOKUP has reigned supreme as the most widely used lookup function in Excel. But now that XLOOKUP is more widely available, VLOOKUP's reign will likely come to an end. XLOOKUP is a modern replacement for the VLOOKUP function and is more capable in almost every way. Let's look at how these two functions stack up against each other.
Why SUMPRODUCT?
In this article, I attempt to explain why you see SUMPRODUCT so often in formulas, and when you can use the SUM function instead. The short version: SUMPRODUCT supports array operations natively, which makes it very useful for solving seemingly unrelated Excel problems. In the current version of Excel, you can use the SUM function instead, but SUMPRODUCT is better for backwards compatibility.
How to concatenate in Excel
This article explains how to concatenate manually with the ampersand operator (&) and with the three Excel functions designed for concatenation: CONCATENATE, CONCAT, and TEXTJOIN.
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 widely used functions in Excel that use a syntax different from other functions in Excel. This syntax can make these functions more challenging to use, because it is not intuitive. Read on for important information about COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS, MINIFS, and MAXIFS.
Download Coronavirus data to Excel
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.
Alternatives to Dynamic Array Functions
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.
Dynamic array formulas in Excel
Dynamic Arrays are the biggest change to Excel formulas in years. Maybe the biggest change ever. This is because Dynamic Arrays let you easily work with multiple values at the same time in a formula. This article provides an overview with many links and examples.
Excel Pivot Tables
Pivot tables are the fastest and easiest way to quickly analyze data in Excel. This article is an introduction to Pivot Tables and their benefits, and a step-by-step guide with sample data.
Excel Formula Errors
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.
How to lookup first and last match
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.
Excel formulas and functions
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 in Excel formulas
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.
101 Excel Functions you should know
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.
How to use formula criteria (50 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.
Excel shows formula but not result
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
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.
Pages
Excel is literally one of my favorite things in life + absolutely love learning new things through your site/newsletters!:)
Andrea
Get Training
Quick, clean, and to the point training
Learn Excel with high quality video training. Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. Each video comes with its own practice worksheet.
View Paid Training & Bundles
