COUNTIFS, not FREQUENCY,
Is a Better Choice for Calculating
Frequency Distribution Tables
For Charting Histograms

Because the Texas and California governors have been bickering over the Texan's attempt to poach California employers, I got curious about the distribution of the unemployment rates in the two states.

So I used my KydWeb add-in to grab the data from the Federal Reserve Bank of St. Louis. Then I needed to summarize the data in a frequency distribution table before I could chart it.

Excel offers at least two ways to do this, and the "obvious" way to calculate the table isn't the best way.

Use the SUMPRODUCT Function
In a Column of Your Excel Tables
To Filter Any Number of Items

Excel 2007 introduced its powerful Tables feature, as illustrated here. Tables allow you to sort and filter your data easily.

However, the filter capability has at least two problems. First, you can use a maximum of only two criteria to filter any column.

Second, it takes about half a dozen steps each time you change a filter. So changing filters isn't a quick process. It would be so much easier if you could just copy and paste one or more filters to a range of cells, or even use formulas to change filters.

This figure illustrates a way around these problems.
 

Introducing Excels Three Types
Of Spreadsheet Databases

Excel offers three general ways to arrange data in your spreadsheet so you can use it as a database with your worksheet formulas:

1. Simple Tables, which Ive used since Excel 2.0.
2. Excel Tables, introduced in Excel 2007.
3. PivotTables with a Tabular Report Layout, introduced in Excel 2010.

Here's an introduction to this important topic.
 

How to Set Up a Pivot Table
As a Spreadsheet Database

You can use a Pivot Table as a database in the same way that you can use Simple Tables or Excel Tables.

However, you've probably never seen a description of how to do it. Thats too bad, because Excel 2010 gave Excel users the ability to use one or more Pivot Tables as a massive and powerful spreadsheet database.
 

Five Really Useful
Excel Keyboard Shortcuts

Several years ago, I created my Complete Excel Shortcuts Workbook. It contains more that 250 keyboard shortcuts we can use when the workbook is active.

I created the workbook as a reference, because I didnt know of any other source that had them all. Im fairly certain that this workbook is complete, because thousands of people have downloaded it and Ive added the few additional shortcuts that Excel users have sent my way.
 

Introducing the Power
Of Year-Over-Year
Performance Charts in Excel

The purpose of management reports should be to help readers find and track patterns of performancequickly and easily.

Thats the attraction of charts, of course. But should we always plot the raw data? Or should we ever transform it somehow? To see one type of transformation thats often revealing, take a look at these two figures...

The US Economic Overview
Excel Dashboard Report

This new Excel dashboard should interest you in several ways:

If your business relies on the health of the US economy, this dashboard provides insights you probably won't find elsewhere.

If you need to distribute your dashboards electronically, the linked page illustrates two techniques to consider.

And if you use Excel dashboards now, this dashboard illustrates some of the ways you can expand it to meet your information needs.

Click the link to get a closer look at our new US Economic Overview Excel dashboard.

Follow Recessions by Country
With a Chart That's Not a Chart

In this figure shows the top of a chart that shows recessions by country for the countries followed by the Federal Reserve Bank of St. Louis.

More generally, it's a chart of trends in yes/no conditions by month

Sometimes, however, the best chart for a task is a spreadsheet, as you see at This Chart of Recessions by Country Uses Excel Conditional Formats to Do the Work.

Help Readers Interpret Chart
Trends Correctly by Using
High-Quality, Dynamic Text

In a chart of business performance, an upward-sloping line could indicate good news or bad.

If the chart shows profits or sales, an upward-sloping line is good news. But if the chart shows expenses or measures about problem areas, an upward-sloping line, or a rising series of column plots, shows us bad news.

So when a report includes a mix of such charts, how can we help readers to interpret each chart trend quickly and correctly?

The Wall Street Journal uses indicators like the ones shown here. And you can add similar indicators to your own chart figuresif you know three tips.

At first glance, the solution looks obvious. This obvious solution uses two of my three tips. Both of them also work in Excel 2003. But because the third tip only works with New Excel (Excel 2007 and after), Ill discuss only that program.

How to Create Normal Curves
With Shaded Areas in New Excel

Now, in How to Create Normal Curves with Shaded Areas in New Excel, I explain how to create the figures in Excel 2007 and beyond.

Even if you have no particular in statistics, you might find the techniques interesting. This is because you might need to use similar techniques when you create other charts.
 

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

Excels 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 Acmes 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 its 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!
 

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.
 

Track Your Company's Private
Rate of Inflation

Your company buys a unique mix of goods and services. So you have a unique inflation rate. Here's how to track it.

Track Public
Confidence Daily

Analyze Seasonal Sales
Using Excel Formulas

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

Create Traffic-Light
Charts with Formulas

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 List

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.