Thursday, January 21, 2021
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.

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...
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...

Latest Articles

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...
If you want all your Excel reports, analyses, forecasts, and other Excel work to be highly productive, this is the only strategy that will work for you.

An Introduction to Excel Data Plumbing

Would you like to: Create your new reports, analyses, forecasts, and other Excel work quickly? Update your Excel work with one command, without using...
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...