Monday, March 1, 2021
The SUMIFS function returns a sum for nearly any number of single criteria. But with SUMPFODUCT you can return the sum for many criteria listed in a Criteria List.

How to Use SUMIFS with Criteria Lists, Summarizing Sales

With one exception, SUMIFS is a very powerful function. And it's very fast. To understand the one exception, suppose you have a table of sales...
The extreme variability caused by seasonal sales makes it difficult to track and forecast your underlying sales trends. Here's how to solve that problem.

How to Create a Rolling Forecast of Seasonal Sales in Excel

The Excel chart below shows the typical saw-tooth pattern of seasonal sales. Seasonal sales have about the same pattern every year, every week, or both. In...
Pivot Tables aren't merely a way to interact with your data. You also can use them as a rich source of data for standard reports and analyses. And you don't need to limit formulas to GETPIVOTDATA; you also can use SUMIFS, SUMPRODUCT, and all other Excel functions with pivots. Here's how.

How to Set Up a Pivot Table as an Excel-Friendly Database

You can use a Pivot Table as a database in the same way that you can use Excel Tables and other Excel-Friendly Databases (EFDs). In...
A great way to distribute Excel reports is as a PDF file. Then you distribute the file. Here's how to save any number of Excel reports to one PDF file.

How to Save Multiple Excel Reports to One PDF File

A great way to distribute your Excel reports is to save them in a PDF file and then distribute the file. People who receive your...
Here's how select a value from a list of values from a worksheet's a Validation List. You can use the list to display interactive data in reports.

How to Select Values from a Validation List in Excel

Excel provides two ways to select a value from a list of values. I often use this feature in interactive Excel reports to select dates,...
Which is faster: VLOOKUP? Or INDEX-MATCH? Here are the test results.

Excel’s Fastest Lookup Methods: The Tested Results

This article presents the results of my tests to find Excel’s fastest lookup method. I discussed the report workbook in A Volatile Workbook to Test Calculation...
Read a Text File with VBA in Excel, and Write the Text to a Spreadsheet

Read a Text File with VBA in Excel, and Write the Text to a...

"I need to write a text file into one row of my Excel spreadsheet, cell by cell, 20 characters at a time. It's urgent....
Your Return On Equity ratio is a key indicator of financial health. This report lets you show the components of that ratio in a unique Excel display.

Map Your Financial Health With an Excel DuPont Dashboard

Management reporting is all about communication. Reporting Return On Equity (ROE) is a case in point. The ROE financial ratio is a key measure of...
The Internal Rate of Return calculation has very real problems, no matter where it's calculated. But with its MIRR function, Excel could offer a solution.

How Excel’s MIRR Function Can Fix the IRR Function

The calculation of an Internal Rate of Return is very appealing...at first glance. When you know the IRR of a prospective investment, you seem to...
Excel databases can be Tables of data, Pivot Tables, or even lists that your reports and analyses can use easily. This introduction will get you started.

What the Heck is an Excel Database?

You can raise your Excel productivity significantly and slash your Excel errors—if you keep your data in an Excel database. Microsoft, Oracle, IBM, and others...

Latest Articles

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...
You can ratchet down errors in your Excel reports by using an Error Summary Table that uses conditional formatting to alert you to errors.

How to Set Up an Automatic Error-Checking System in Excel Reports

Decades ago, I worked as a cost accountant for a large company. But because our department received terrible reports, I wrote my own reports...using...
Here's how to create an Excel array from two others, with the arrays stacked either one on top of the other, or side-by-side, like books on a shelf.

How to Stack and Shelve Dynamic Arrays

While using Excel 365 recently, I needed to create one dynamic array that would consist of two arrays, with one stacked on top of...
Advertisement
Advertisement