Friday, July 1, 2022
It should have been a simple Excel formula. But it became a two-part puzzle needing arrays, ASCII, and text concatenation to answer.

A Two-Part Excel Puzzle, with Arrays, Text, ASCII, and Concatenation

Recently, I needed to compare two long columns of text of various lengths to make sure that each row of text in column A...
Using Excel's LET, SORTBY, and SEQUENCE functions, and dynamic arrays, you easily can list any number of top and bottom results from a Table.

How to Report Top and Bottom Results Using Dynamic Arrays in Excel

In How to Use Excel’s LET Function, I showed several examples of a powerful function that Microsoft added to Excel 365 in the summer...
The LET function is the most powerful function that Microsoft has released for Excel in years. Here's an introduction to its features.

How to Use Excel’s LET Function

In the summer of 2020, Microsoft introduced the LET function for Excel 365—one of the most-significant new worksheet functions that Microsoft has introduced in...
To use Account Groups in Excel formulas, you first must define the groups. Here’s how to do it using either simple lists or Dynamic Arrays.

How to Define General Ledger Account Groups in Excel

In Part 1 of this series, How to Report GL Account Groups in Excel, we explored the strategy for creating financial reports that use...
How to aggregate named groups of GL accounts.

How to Report GL Account Groups in Excel

Believe it or not, this income statement is quite sophisticated. It's not nearly as simple-minded as it looks. In fact, this income statement illustrates a...
How to Use SUMPRODUCT to Find the Last Item in an Excel List

How to Use SUMPRODUCT to Find the Last Item in an Excel List

Suppose you have a Sequential List of items, as in the second table in the figure below. And suppose you want to show the...

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...
Array formulas may be Excel's most-powerful feature for summarizing data. But they're also a little-used feature of Excel. Here's how to use their power.

How to Summarize Spreadsheet Data with Excel’s Array Formulas

Subtotals can reveal very useful management information. For example, managers might be interested to learn that sales increased by 10% last month. But they would...
Excel's SUMPRODUCT function offers much of the power of Excel array formulas, but without special treatment.

Use Excel’s SUMPRODUCT to Summarize Worksheet Data

After I posted  The Most Powerful Ways to Summarize Excel Data for Reporting and Analysis, a reader asked why I hadn't discussed the SUMPRODUCT function. I...

Latest Articles

How to Smooth Data by Using the TREND Function

Years ago, I read that Prof. William S. Cleveland had suggested that data could be smoothed by calculating a centered trendline through adjacent data—a...
Two economists have introduced a new leading indicator, which predicts a recession soon. These Excel charts illustrate their insight. If they're correct, Excel users will be very busy in the months ahead.

Consumer Sentiment Suggests a 2022 Recession

In recent months, business websites have speculated about recessions and stagflation in 2022. These predictions could affect your Excel work significantly in the next few...
Your data can contain many important, hidden patterns. But simple transformations in Excel can help to reveal them—as this example illustrates.

Simple Transformations Can Reveal Hidden Patterns in Your Data

Excel users have at least two significant advantages over business professionals who rely on other analytical and reporting tools. First advantage: We Excel users can...
Advertisement