Home ExcelUser Blog Site Map Contact Excel for Business Excel Dashboards Excel Solutions Exploring Excel BI for Excel Business Tools Excel Catalog Affiliate Program Excel Help Portal

# 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 Excel’s 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 I’ve 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. That’s 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 didn’t know of any other source that had them all. I’m fairly certain that this workbook is complete, because thousands of people have downloaded it and I’ve 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 performance…quickly and easily.

That’s 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 that’s often revealing, take a look at these two figures...

# The US Economic OverviewExcel 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 ChartTrends 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 figures…if 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), I’ll discuss only that program.

# How to Create Normal Curves With Shaded Areas in New Excel

In An Introduction to Excel's Normal Distribution Functions I included several figures somewhat like this.

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

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.

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

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.

# 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

Here's how to track public confidence both monthly and daily, for free.

## Analyze Seasonal SalesUsing 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

Use worksheet formulas to turn ordinary charts into traffic-light charts.

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

Learn the problems with Excel's lookup functions,  and then learn about Excel's best option

## Find the Last Item in a List

Suppose you have a Sequential List of items. And suppose you want to show the date of the first and last entry in the list. How would you do it?

Use SUMPRODUCT, of course.

## Numbers or Charts?

Reports are supposed to give readers the most-useful information in the shortest-possible reading time. Which is best for this? Numbers or charts?

## Add Cash Flow to Balance Sheets

You can make balance sheet reports a lot more useful if you add Sources and Uses information to them.

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

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.

The Z Score is the best-known analytical tool for predicting the likelihood of 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?

Charley Kyd created the first spreadsheet dashboard more than 25 years ago using Lotus 1-2-3. Every line in every chart consists of text in cells. See examples of the first spreadsheet dashboard report, and read a brief description of how it was created.

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

One easy way to return an item from an Excel database is to add a dropdown list box to your spreadsheet. But suppose you want to return additional information about that item, how do you do it? We show you how.

## Database Reports

Do you create periodic reports using data stored in an Excel databases? We show an easy way to let Excel formulas do the work.

Is debt your enemy or your friend? It's your friend if you're making money on the cash you borrow. The EOA ratio compares directly to the interest rate on your debt. When your EOA exceeds your interest rate, you're making money from your debt.

## Growing Too Fast?

Is your company growing faster than it can afford? The Sustainable Growth Rate can help you manage your company's financial ability to grow.

## Pablum Dashboards

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

## Stuff We Use

Here are some products and services we use on our own computers. Our first recommendation: Remote Data Backup for Businesses

## Benford's Law & Planning

Unless you're a public accountant, you probably haven't experimented with Benford's Law.

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

SUMPRODUCT offers great power to summarize lists of data in Excel worksheets. It works somewhat like array formulas, but without the complications.

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

## Array Formulas

Excel array formulas can summarize Excel data quickly and easily.

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

A reader asks how to calculate Future Values from cash flows that aren't necessarily periodic.

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

ExcelUser, Inc.
http://www.ExcelUser.com