Create Dynamic Chart Legends

If you pay attention to charts in magazines and newspapers, you might have noticed a chart like this.

This chart is unusual because colors and data in its legend bar are ranked by the values for the current period in the chart.

In other words, the green line at the top of the chart corresponds with the green box at the top of the legend bar, and the other boxes and data series also are in sync.

Learn how to create charts like this in How to Create Dynamic Chart Legends in Excel.
 

Never Use VLOOKUP

Excel’s VLOOKUP function is a lot more popular than the INDEX-MATCH function.

This is too bad, because INDEX-MATCH is much more flexible than VLOOKUP. Also, at its worst, INDEX-MATCH is slightly faster than VLOOKUP; at its best, INDEX-MATCH is many-times faster.

For more details Why INDEX-MATCH Is Far Better Than VLOOKUP or HLOOKUP in Excel.

Set Up Two-D Lookups In Excel

A friend recently asked, "In this table, how can I return the date where the lowest value occurs?"

More generally, however, he was asking how to look up a value in two dimensions. This is a different challenge than most Excel lookups, which are limited to a single row or column.

You can find the solution in, How to Create Two-Dimensional Lookups in Excel Formulas.

How to Add Web Data to Your
Excel Dashboard Reports

Excel dashboard reports typically show internal data. But you can easily add public data that managers need.

Here, for example, is a modified version of Report 5 from IncSight DB. In the original version, the entire report showed internal data.

But in this version, the nine small charts show web data of interest to Acme’s managers: performance of top competitors, key economic data, and price trends for key resources.

Best of all, when new data is available on the web, the report can update when Excel recalculates.

Improve Business Performance
By Linking Excel to the Web

If our rotting economy causes most of our business problems these days, why is it that so few Excel reports and analyses include economic data?

These days, the road to business success seems to lead through a maze. Relevant economic data is like a photo taken from above the maze; it offers a view that helps managers find a path to safety. So it’s a mistake to deprive them of this information.

Here are Ten Ways to Improve Business Performance with Excel Formulas Linked to the Web.

Excel Swipe Files

This is an Excel figure. It's one of 16 that Charley has created (so far) in Excel.

These figures show that Excel users can create professional-looking displays for Excel reports, PowerPoint presentations, Word documents, and web pages.

See the high-quality displays you can produce with Excel!
 

Track Public Confidence Daily

When you’re planning for your company’s future, it would be great to have an accurate and current indication of how optimistic or pessimistic the public is about future economic performance.

Here's the only way we know to track public confidence both monthly and daily, for free.
 

How to Track Your Company's
Private Rate of Inflation

When the economy starts to improve, it's likely that inflation will sky-rocket. So it's important to keep an eye on inflation.

Keep in mind, however, that your company buys a unique mix of goods and services. So you have a unique inflation rate.

Here's how to use data from the Bureau of Labor Statistics to track your Private Rate of Inflation
 

An Introduction to Excel's
Normal Distribution Functions

Excel provides several statistics functions for working with normal distributions. Also, New Excel (Excel 2007 and after) introduced a significant number of functions to replace earlier versions.

This article introduces Excel's functions that work with normal distributions, with examples from both versions of Excel.
 

How to Use Excel Formulas
To Create Traffic-Light Charts 

You can use worksheet formulas in Excel to turn ordinary charts into traffic-light charts like these. Of course, you'll have to ignore the experts who tell you that's impossible!

See Our Free Recorded Webinars

Charley Kyd recently started to give free webinars about his Excel dashboard products. Honestly, giving webinars is a new experience for him, so he won't win any Emmy Awards. But even so, the webinars offer some good information. You'll find them here:

• IncSight DB

• IncSight QnE

• Questions and Answers from both webinars
  

Analyze Seasonal Sales
Using Excel Formulas

Seasonal sales are difficult to forecast and analyze. It's hard to pick up on monthly trends. It's hard to forecast future sales. It's hard to plan spending a staffing. Seasonal sales are...messy.

Here's how to deseasonalize your sales data so you can gain greater insight about your sales.

Encourage Urgent Change

In too many companies, change crawls along at a snail's pace. Harvard's John P. Kotter explains how to encourage A Sense of Urgency for changing your organization's performance. And Excel plays a critical role.

Excel's Best
Lookup Function

Find the Last Item in a Listt

Use SUMPRODUCT, of course.

Numbers or Charts?

Add Cash Flow to Balance Sheets

This simple change tells readers how each balance sheet item has changed, and how those changes have affected cash flow.

Your Cost Lag Loop

Some companies are in bad shape these days.

Whatever your company's condition, you'll survive the downturn more easily if you master the cost-lag loop.
 

Predict Business Bankruptcy

We show you how to calculate Z-Scores with Excel. 

Excel BI

Costs are rising. Sales are falling Credit is limited. Taxes are going up. We offer five reasons that Excel users will find solutions, if they exist.

Track Receivables With Excel

Do you monitor your receivables with the AR Collection Period, also called "Days Sales Outstanding in Receivables" (DSO)?

Learn why DSO fails and how Excel can give you more accurate results, results that also can improve your cash flow forecast.

Raise or Cut Prices?

Use this formula to learn whether your price change will help your gross profits or hurt them. It will help you to brainstorm your pricing strategy more easily.

The First Spreadsheet Dashboard

CyclePlot Charts

If you have seasonal sales, or other measures of performance, Cycle Plots can offer greater insight about your performance than traditional charting techniques.

Returning Items from a List

Database Reports

Weighing Your Debt Load

Growing Too Fast?

Pablum Dashboards

Is your company spending thousands of dollars for business dashboards that serve up your performance measures like pablum?

Stuff We Use

Benford's Law & Planning

Auditors sometimes use this fascinating statistical insight to uncover fraudulent accounting data. But it might reveal a useful strategy for investing in the stock market. And it might help you to improve the accuracy of your budgets and forecasts.

Array Alternative

Unfortunately, Excel's help topic ignores the real power of this function.

Array Formulas

We explain the most powerful and flexible approaches. The most powerful method is to use Excel arrays, which can give you summaries using any number of criteria.

Future Values

We show how to calculate both Future and  Present non-periodic values.