Saturday, September 25, 2021
Two unusual formulas can use the SEARCH or FIND functions to search any number of cells for any number of words—almost instantly.

How I Set Up SEARCH and FIND Formulas on Steroids

I've used Excel long enough to recognize when I've stumbled across an unusual formula. And it happened again a few minutes ago. In the past,...
Excel's STOCKHISTORY function can return decades of history about the prices of stocks for thousands of public companies from many countries. Here's an introduction to that function.

Introducing Excel’s STOCKHISTORY Function

The STOCKHISTORY function recently showed up in my non-beta version of Excel 365. Its appearance was a surprise because it wasn't announced as a What's...
We compare Excel's five columnar lookup functions.

XLOOKUP vs VLOOKUP vs INDEX-MATCH vs SUMIFS

SUMIFS? Really? Why is SUMIFS included among those lookup methods? I'll explain in a few minutes. But first, let's look at the lookup methods shown in...
This Excel table shows the top and bottom five results, with charts that show the most recent three month trends. And it updates automatically.

Show Top and Bottom Results in a Chart-Table

The workbook that supports the following figure does a lot of work! First, it uses Power Query to download the weekly unemployment claims and the...
In Excel Tables, you can filter on any two conditions in a column. But by using the SUMPRODUCT function, you can filter on any number of items in a list.

How to Use SUMPRODUCT in an Excel Table to Filter Any Number of Items

Excel 2007 introduced the powerful Table feature, as illustrated below. Tables allow you to sort and filter your data easily. However, the filter capability has...

How to Use SUMPRODUCT to Create Two-Dimensional Lookups in Excel Formulas

A friend recently asked, "In this table, how can I return the date where the lowest value occurs?" More generally, however, he was asking how to...
Do you spend hours each period turning raw data into useful information? You can fight such Spreadsheet Hell with help from three key Excel functions.

How to Fight Spreadsheet Hell with Three Excel Lookup Functions

(I wrote this long ago, and there's much to be added. It's high on my Update list. Charley) Many Excel users build their reports like...
Does Excel's INDEX worksheet function work for you only some of the time? Here are ways to learn why your INDEX function isn't working as you expect.

How to Debug INDEX in Excel

"I'm not a very experienced Excel user. Why does = INDEX(...) sometimes work and sometimes not.... :-)?" -- Suzan G. Probably the best way to...
This simple VBA procedure finds the precise time it takes to calculate a workbook. You can use it to test competing workbook design methods.

Use Excel VBA to Test Report Calculation Times

I once exchanged email with an experienced Excel jockey who insisted that VLOOKUP was Excel’s fastest lookup method. It was even faster, he said,...
INDEX-MATCH formulas can perform many types of lookups that VLOOKUP can’t. And they're faster. Here are six examples.

How to Use INDEX-MATCH, Part 3: Six Lookups That VLOOKUP Can’t Do

In this three-part series, I'm showing you how to use INDEX-MATCH. In Part 1, I showed you how to use the INDEX function. In Part...

Latest Articles

Two unusual formulas can use the SEARCH or FIND functions to search any number of cells for any number of words—almost instantly.

How I Set Up SEARCH and FIND Formulas on Steroids

I've used Excel long enough to recognize when I've stumbled across an unusual formula. And it happened again a few minutes ago. In the past,...
Use this unique Excel dashboard to track the general rate of inflation, your personal rate, and the rate of the goods and services you buy.

Inflation Tracker Excel Dashboard with Consumer Prices

U.S. President John F. Kennedy once said of a growing economy, "A rising tide lifts all boats." However, a rising inflationary tide does not lift...
Excel's Quick Access Toolbar can save you many clicks and needless work. Here are the eight QAT tools that I use, and how I use them.

Eight Great Tools for Your Quick Access Toolbar

I use eight great tools in my Quick Action Toolbar (QAT), and you might want to consider using them yourself. Before I get into the...
Advertisement
Advertisement