Friday, January 22, 2021
Do you spend hours each period turning raw data into useful information? You can fight such Spreadsheet Hell with help from three key Excel functions.

How to Fight Spreadsheet Hell with Three Excel Lookup Functions

(I wrote this long ago, and there's much to be added. It's high on my Update list. Charley) Many Excel users build their reports like...
On Valentine's Day, 2018, tens of thousands of major sites warned their readers that inflation is rising. But that might not be true. These analytical Excel charts suggest a different conclusion.

What Inflation? How Excel Charts Can Help You Avoid Fake Economic News

The business news on Valentine's Day, 2018, was dominated by talk of rising inflation. A search of Google News showed 67,100 results for the...
Using gauges in your management dashboard reports is a big mistake. Gauges hide information that managers need and waste space in management reports.

Down With Gauges!

It's possible to carry an analogy too far. Dashboards for managers are logically similar to dashboards for cars. Both types of dashboards provide key information...
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...
If the formulas in your Excel reports and analyses display errors like #DIV/0! or #VALUE!, here are some simple ways to trap them.

Error Handling in Excel Spreadsheets

It’s virtually guaranteed. If you have a bunch of formulas like this… M43:   =C43/D43 …then the divisor, D43 in this instance, will have a value of...

Show Key Stats Automatically in Periodic Excel Reports

Each issue of Business Week magazine used to include several figures titled "The Stat". These figures emphasized key data related to the article on...
Excel offers several ways to summarize data quickly and easily. Here are the most powerful and flexible approaches, which include using Excel array formulas.

The Most Powerful Ways to Summarize Excel Data for Reporting and Analysis

(Note: I wrote this before Microsoft introduced Excel Tables or SUMIFS. This post is scheduled for an update.) Excel users often need to summarize data...
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...
Here's how to use formulas and conditional formatting to expand or contract your Excel report automatically, without macros, when you change a cell value.

Create Variable-Length, Dynamic Reports Linked to Excel Tables

Excel Tables are a powerful feature introduced in Excel 2007. Not only can you report from them directly, you can use them as a...
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

A visitor asked how to set up a simple invoicing system in Excel. This is a common problem in many small businesses, divisions, and sales...

Latest Articles

We compare Excel's five columnar lookup functions.

XLOOKUP vs VLOOKUP vs INDEX-MATCH vs SUMIFS

SUMIFS? Really? Why is SUMIFS included among those lookup methods? I'll explain in a few minutes. But first, let's look at the lookup methods shown in...
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...
Advertisement
Advertisement