Sunday, October 25, 2020
Home Formulas & Functions

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.

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...
This figure uses the Chicago Fed's National Financial Conditions Index to illustrate how to create an Excel panel chart.

US National Financial Conditions Using Excel Panel Charts

I’ll explain the meaning of this chart figure shortly. But first, let’s look at it from an Excel perspective. (Note: I’ve begun to use economic...
Microsoft tells us that many worksheet functions are 'deprecated' or that thy're 'compatibility' functions. Here what that means and why you should care.

What’s a ‘Deprecated’ Function in Excel?

Wikipedia tells us that deprecation is a status applied to a computer software feature, characteristic, or practice indicating it should be avoided, typically because of being superseded. Each new generation of...
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...
Excel's FREQUENCY function was first created to calculate frequency distribution tables, which are needed for charting histograms. But the COUNTIFS function offers more power, and it's easier to use.

Use COUNTIFS, not FREQUENCY, to Calculate Frequency Distribution Tables for Charting Histograms

Because the Texas and California governors have been bickering over the Texan's attempt to poach California employers, I got curious about the distribution of...
Benford's Law reveals an amazing characteristic of data. Not only does it help to identify fraud, it could help you to improve budgets and forecasts.

Use Benford’s Law & Charts in Excel to Improve Business Planning

Unless you're a public accountant, you probably haven't experimented with Benford's Law. Auditors sometimes use this fascinating statistical insight to uncover fraudulent accounting data. But it might reveal...
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...
Here are the only two ways I know to set up formulas that look up data in an Excel Table, using more than one criteria.

Two Ways to Set Up Multi-Criteria Lookup Formulas in Excel

The Excel Table below illustrates a common type of lookup problem…perhaps taken to a slight extreme. Here, we have a specific manager for each month...
Simple spreadsheet edits can introduce costly Excel errors. Here are three techniques that can help you to reduce your errors in Excel.

Three Ways to Reduce Errors in Your Excel SUM Formulas

If you aren't careful, simple edits to your reports and analyses can cause significant errors. Years ago, for example, I heard about an expensive error...
Excel's SUMPRODUCT Function offers more power and flexibility than its help files describe. Here's a new way to use one of Excels' most-powerful functions.

The SUMPRODUCT Function’s Undocumented Method

SUMPRODUCT and SUMIFS are Excel's two most powerful functions for returning filtered data from a table. SUMPRODUCT is more powerful, but SUMIFS is faster. Unfortunately, Microsoft's documentation about...

Latest Articles

Here's how to boost your Excel output, slash your Excel errors, and improve your Excel reports and analyses—whether you work for a large or small company.

How to Be Significantly More Productive in Excel

The Excel charts below are professional-quality. But that's not what makes them so unusual. Instead... ● You can update them in a few seconds, with...
Use this Excel dashboard to track 27 economic indicators of the United States' recovery from the Covid-19 recession.

Learn How to Use Excel to Track the US Recovery from the Covid Recession

The Covid recession is the worst recession the world has experienced since the Great Depression. And the Recovery Tracker workbook and Excel training can...
This Excel dashboard report lets you follow key economic trends while showing you how to gain a HUGE increase in your Excel productivity at work.

Track Key Economic Indicators Every Weekday with this Excel Dashboard

You can update this Excel dashboard report every weekday—with one command—if you have Excel 2016 or above. This report of current economic data introduces you...