Find Leading Indicators Using Automated Cross Correlations in Excel, Part 1
It all seems so simple...
To improve your forecasts of sales or other measures, you simply need to find leading indicators...measures that are highly correlated...
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...
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...
Track Dynamic Data with VBA or with Iterative Calculations
"My A1 cell changes value every minute because it imports data from a DDE server. To keep historical data, I would like to list...
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...
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...
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...
The First Spreadsheet Dashboard: Mini-Graph Reports in Lotus 1-2-3
This is the first dashboard report ever created with spreadsheets. I worked on this reporting technique in the early 1980s, then included this report...
The Basics of Professional Excel Charting
When you're reporting, you can't run a cable from your data to your readers' brains. But the next-best alternative is to use great charts.
By "great...
Show Useful Year-to-Date Variance Charts in Your Excel Dashboards
Many companies have a difficult time creating charts of spending variances. In fact, many companies rely on charts that are nearly useless for that...