Friday, April 30, 2021

Recent Content

I used the FILTER function to give me IFS-like power for PERCENTILE.INC. But then I realized the same pattern brings IFS-like power to many functions—including MEDIAN, STDEV.S, GEOMEAN, and perhaps even the FORECAST functions!
Aggregation Functions

Using the Hidden Power of Excel’s FILTER Function

Charley Kyd -
0
When you take the time to explore a new worksheet function, it's amazing what you can discover! And I won't tell you about the really...
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...

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

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

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

Featured Articles

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

Array Formulas Charley Kyd -
0
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...
How to aggregate named groups of GL accounts.

How to Report GL Account Groups in Excel

Accounting & Finance Charley Kyd -
0
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...
In Excel, you can report named groups of GL account numbers, product codes, and so on—just as you report one such number. Here’s how.

How to Use Array Formulas to Report Groups of Accounts

Accounting & Finance Charley Kyd -
0
Often, in accounting, marketing, and other departments, we'd like to report and analyze groups of items rather than one item at a time. In Part...
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

Training & Services Charley Kyd -
0
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

Training & Services Charley Kyd -
0
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...

Blog

Blog

Add Low-Overwhelm Context to Your Line Charts

Charley Kyd -
0
The data you display in charts often takes on new meaning when you display it in the context of other data. One way to add...
When you're refinancing, this chart allows you to use the tip of your finger trade off your monthly payments and your total payments as you consider the term of your loan.

Calculate Mortgage Payments and Your ROA with an Excel CalcPlot Chart

Each fat line in this chart indicates when a recession has occured for the specified country. You can use the same method to show other Boolean conditions.

Chart Recessions and Other Boolean Conditions in Excel

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

When you include several charts in one report, your managers must THINK about each chart to decide if it shows good news or bad. Here's a fix for that.

How to Remove a Speed Bump From Your Excel Charts

Business & Economics

Data Management

How to Reproduce Your Excel Work Across Many Categories, Part 3...

Charley Kyd -
0
Part 1 of this series about analytical spawning with Excel described how Excel users often need to spawn one model or report across many related...
When you combine conditional formatting with charts you can create detailed chart legends that match the relative positions of the lines in your chart.

How to Create Dynamic Chart Legends in Excel

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

Most loans and many investments are annuities, which are payments made at fixed intervals over time. Here's how to use Excel to calculate any of the five key unknowns for any annuity.

Excel’s Five Annuity Functions

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

Formulas & Functions

You can treat text files as relational tables. You can join them by common fields. Query them using SQL. And use the queries with PivotTables. Here's how.
Data Management

Use MS Query with Text Files for Dynamic Excel Reporting

Charley Kyd -
0
(Charley's Note: This article is very out of date. I'll update it as quickly as I can.) In the article, Use MS Query to Treat...
In Word, we can set up custom forms, and then tab through them to enter our data. Here's how to tab through forms in your cells in your worksheets.

How to Tab From Cell to Cell in an Excel Worksheet...

Excel ranges can work like relational tables. You can join them by common fields. Query them with SQL. And use queries in PivotTables. Here's how.

Use MS Query to Treat Excel as a Relational Data Source

By charting the annual Rate Of Change (ROC) of key measures, you can uncover significant information about your time-series data. And by using the right recession measure, you can put that information into better context.

Chart Your Rate of Change to Reveal Hidden Business Performance

Although Slicers normally are used with PivotTables and Excel Tables, you also can set them up to control any settings in your interactive workbooks, just as you would when you use Validation Lists.

How to Use Excel Slicers to Control Settings in Your Reports...

Excel Methods

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.
Accounting & Finance

How to Use SUMIFS with Criteria Lists, Summarizing Sales

Charley Kyd -
0
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...
We compare Excel's five columnar lookup functions.

XLOOKUP vs VLOOKUP vs INDEX-MATCH vs SUMIFS

The MATCH function makes the INDEX function useful. This is the second of a three-part series designed to INDEX-MATCH clearer.

How to Use INDEX-MATCH, Part 2: The MATCH Function

Most loans and many investments are annuities, which are payments made at fixed intervals over time. Here's how to use Excel to calculate any of the five key unknowns for any annuity.

Excel’s Five Annuity Functions

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

All Categories

Popular

Excel provides several worksheet functions for working with normal distributions or 'bell-shaped curves.' This introduction to Excel's Normal Distribution functions offers help for the statistically challenged.

An Introduction to Excel’s Normal Distribution Functions

Business Statistics Charley Kyd -
0
(Download the workbook.) When a visitor asked me how to generate a random number from a Normal distribution she set me to thinking about doing statistics...
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

Downloads Charley Kyd -
0
(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...
When you bring data from another source into an Excel worksheet, the data often includes rows that you’ll want to delete. Here's the first of two methods.

How to Delete Blank or Unneeded Rows, Method 1

Worksheet Interaction Charley Kyd -
0
When you bring data from another source into an Excel worksheet, the data often includes rows that you’ll want to delete. Often, you’ll want...
© ExcelUser, Inc. Copyright © 2004 - 2020 by Charles W. Kyd, all rights reserved.