Monday, April 12, 2021

Recent Content

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

How to Report Top and Bottom Results Using Dynamic Arrays in...

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

To use Account Groups in Excel formulas, you first must define the groups. Here’s how to do it using either simple lists or Dynamic Arrays.

How to Define General Ledger Account Groups in Excel

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

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

Chart Your Rate of Change to Reveal Hidden Business Performance

Charley Kyd -
0
What insights can you gain from the following Excel chart of Apple's quarterly revenues over the past 18 years? I see three things. First, their...
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

In a list of items that could appear in a worksheet any number of times, here's how to count the number of items that appear only once. Or twice. And so on.

How to Count the Occurrences of Items in a List

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

This simple invoicing system allows you to keep a list of products and prices in Excel, then use VLOOKUP or INDEX-MATCH to populate an invoice with the item and quantity you choose.

Use Excel’s INDEX-MATCH or VLOOKUP Functions to Populate Invoices and POs

Formulas & Functions

Here's how to use a formula that returns TRUE or FALSE in Excel's conditional formatting feature to highlight rows that contain specific numbers or text.
Conditional Formatting

How to Use Conditional-Format Formulas to Change Background Colors

Charley Kyd -
0
"I have an Excel table with a column that will be filled with 'yes' or 'no. When a user enters 'yes' to a cell,...
Business magazines and newspapers usually are an excellent place to find great ideas for Excel charts. But here's a Wall Street Journal chart figure that offers lessons about what NOT to do!

Bad Chart! Bad, Bad Chart!

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

Three More Simple Tricks to Improve Excel Charts for Business

Three More Simple Tricks to Improve Excel Charts for Business

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

Excel Methods

Most range names in Excel apply to the entire workbook. But you also can define them to apply only to one worksheet. Here's why and how to do that.
Excel Dashboards

How and Why to Define Excel Range Names with a Worksheet...

Charley Kyd -
0
In Excel, a name can be global to a workbook or local to a worksheet. The traditional method we all use when we create...
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...

INDEX-MATCH formulas can perform many types of lookups that VLOOKUP can’t. And they're faster. Here are six examples.

How to Use INDEX-MATCH, Part 3: Six Lookups That VLOOKUP Can’t...

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

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?

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.