Use COUNTIFS, not FREQUENCY,
To Calculate 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 SUMPRODUCT in an Excel Table
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, I’ve 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.

How to Create Normal Curves
With Shaded Areas in New Excel

In An Introduction to Excel's Normal Distribution Functions I presented several figures somewhat like this. In How to Create Normal Curves with Shaded Areas in New Excel, I explain how to create those figures in New Excel.

Even if you have no particular reason to chart a normal curve, you might find the techniques interesting. This is because you might need to use similar techniques when you create other charts.

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.

Use SUMPRODUCT to Find
The Last Item in an Excel 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?

This question isn't limited to dates, by the way. Your list could display numbers or text instead. How would you return information about the last occurrence of each item in the list?

Use SUMPRODUCT, of course.

Five Reasons Excel BI Is Key
To Your Company's Success

These are challenging times.

The Chairman of the US Federal Reserve System says we're in a financial storm.

Costs are rising. Credit is limited. Taxes are going up. Layoffs are in the news. Some businesses and cities are suffering record deficits; some are going bankrupt.

If companies do avoid the storm, we know that Excel users likely will show them the way.

We offer five reasons this is so.

Excel 2007's Ribbon Hurts
Productivity, Survey Shows

The 'Ribbon' in Excel 2007 is the most significant change that Microsoft has ever made to Excel's user interface. We wanted to find out what Excel users in business think of that change.

The results are in, and the answer is clear. You can see the results here.

The Excel 2007 Market Share

Excel 2007 is significantly different from earlier versions of Excel. So we wondered how widely used it is among its business users. That is, what's the Excel 2007 market share?

We've been asking which version of Excel you use. You can see the results of our poll here.

Use Excel's SUMPRODUCT Function
To Summarize Worksheet Data

Unfortunately, Excel's help topic ignores the real power of this function. We fill in the gaps.

Excel 97-2003 version
Excel 2007 version

Summarize Data With Excel Arrays

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.

If you've not using this powerful tool, you're working harder than you should.

Excel 2007's New User Interface

Menu bars are gone. All but one toolbar are gone. We now have a ribbon, tabs, groups, and dialog launchers.

We present a pictorial introduction to the key features of Excel 2007's
new user interface.

Excel's Shortcuts to Dialogs

Create Excel Bullet Graphs

Variable Lists

Use Any Color in Your Reports

Deleting Rows
With AutoFilter

Create Shaded Curves

Weekly Reporting

Tables with Class

When we saw an ad with a translucent figure like this, we had to see if we could create it in Excel. Even if you don't need this classy effect, our tutorial probably will teach you some new Excel tricks.

Cell Reference$

A visitor is confused about when to use "$" in cell references. Here's what every Excel user should know.

Top MVP Sites

Tab Key Primer

A visitor wants to tab from cell to cell. We explain how.

Normal Distributions

Excel provides several spreadsheet functions for working with normal distributions. Here's an introduction for people who are statistically challenged.

Import Text Files

Have you ever wanted to read a text file into a workbook, column by column? This article shows how to read text files with VBA. 

Fight Spreadsheet Hell

You don't need to spend hours turning raw data into useful information. You can fight this type of Spreadsheet Hell with help from three Excel key functions.

Combo Criteria

Suppose you've got an Excel database with many fields, and you want to find certain records using multiple criteria. We show how.

Avoid Errors

Debug INDEX

Range Name Roundup

Excel's range names offer more power than you might think. And they're not hard to use. Here's an introduction to the power that Excel provides.

Excel 2007 Intro

Here's your guide to key Microsoft sources that have information about the next version of Office.

Range Name Roundup

Excel's range names offer more power than you might think. And they're not hard to use. Here's an introduction to the power that Excel provides.

Excel 2007 Intro

Here's your guide to key Microsoft sources that have information about the next version of Office.

Work With Dates Before 1900

Rounding Times

Excel VBA Standards

Learn four VBA documentation standards that make your VBA code easier for you and others to understand.

Import Web Data

Excel's Web Query tool provides a quick and easy way to import web data to Excel.

Include Row & Column Headings

Round Data Up

MS Query with Excel

You can maintain spreadsheet databases that work like relational tables, and query them with SQL. This article shows you how.

SUM Errors

Simple edits to your spreadsheets can cause significant errors. We offer three ways to protect yourself.

Dynamic Range Names

Dynamic names can adjust their definitions in response to values in cells. They let you update a report merely by changing a value in a cell.

VBA Properties

These VBA spreadsheet functions can show when a file was saved and who last modified the workbook.

Using Array Formulas

Array formulas may be Excel's most powerful feature for summarizing data. Here's how to use this hidden power.

Local Names

Excel range names can be global to your workbook or local to a worksheet. We explain.