Monday, March 1, 2021

Formulas & Functions

Virtually everything business users do with Excel involves worksheet formulas and functions. And this category concentrates on that topic.

This category also includes what Microsoft calls “Names”—which many of us call “Range Names.” More accurately, however, “Names” are named formulas.

Check tags for information about specific functions.

Excel's CLEAN function does more than Excel's help topic says. In fact, it cleans all but two nonprinting characters in the ASCII and Unicode character sets. Here's how to deal with nonprinting characters.

Excel’s CLEAN Function is More Powerful Than You Think

The Excel 2016 help file for the CLEAN function provides more information than earlier versions: “Removes all nonprintable characters from text. Use CLEAN on text imported...
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...
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...
When you want to look up data in Excel, you should never use a worksheet function with "lookup" in its name. Here's why...

Why INDEX-MATCH Is Far Better Than VLOOKUP or HLOOKUP in Excel

(Download the workbook.) Excel’s VLOOKUP function is more popular than the INDEX-MATCH function combination, probably because when Excel users need to look up data then a "lookup" function...
You can use probability distributions to manage the uncertainty about your assumptions when you create simulations in Excel. Here's how.

How to Return Random Numbers from a Normal Distribution for Your Excel Forecasts

(This is the first of two articles about normal distributions. The second article is, How to Create Monte Carlo Models and Forecasts Using Excel Data...
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,...
Although Excel provides two worksheet functions that ignore filtered rows in a Table, nearly any function can ignore those hidden rows if you use this new trick.

Use a ‘Visible’ Column in Formulas to Ignore Hidden Rows in Filtered Tables

Excel Tables, introduced in Version 2007, give us the ability to use column filters to hide rows in a Table. And slicers for Tables, introduced...
How to calculate and highlight the standard error of the estimate on each side of the trend in an Excel chart to make exceptional results stand out.

Highlight Normal Results in Line Charts to Make Exceptional Results Stand Out

Line charts that show trends in performance are the most useful type of chart that management reports can contain. All managers want good performance to...
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...
How to Work with Dates Before 1900 in Excel

How to Work with Dates Before 1900 in Excel

(Download the workbook.) If you work with dates prior to the year 1900, Excel's standard date-handling system will be no help. However, there are several...

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