• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

Excelypedia

The Excel Encyclopedia of The Internet

  • Tutorials
  • Functions
  • Formulas
  • Templates

How to Do a Contingency Table in Excel

October 8, 2021 by Andrew Childress
Contingency table Excel

Contingency tables are commonly used in statistics. Let’s learn how to do a contingency table in Microsoft Excel.

Contingency tables are commonly used in statistics. In essence, they measure frequency distributions of multiple variables. They’re a quick visual way to count frequencies in an intuitive way. Fortunately, they’re easy to build with spreadsheets. Let’s learn how to do a contingency table in Microsoft Excel.

How to Do a Contingency Table in Excel

Imagine that you’re a sales manager, and you want to track product sales by color, by region. A contingency table is the perfect way to do it. Begin by building out a quick layout in an Excel spreadsheet like the one below.

As you can see, the purchase order numbers are listed in column A. The regions are in column B, and the color of the products are in column C. To add a contingency table, you can convert this simple layout into a pivot table.

To do so, click and drag to select the range of data. Here, the range is A1:C8. Then, go to the Insert tab on Excel’s ribbon. On the Insert tab, click PivotTable over on the left side.

Contingency table Excel

On the Create PivotTable menu, click Existing Worksheet, then click into cell A10 in the Table/Range box. Then, click OK. Excel will add your PivotTable.

Contingency table in Excel

On the PivotTable Fields box, move Region into the Rows box. Color goes in the Columns box, and PO #Number goes in the Values box. It will default to Sum, but you can click on the i icon and choose Count instead.

How to do a contingency table in Excel

Just like that, you’ve created a contingency table in Excel. At a glance, you can tell how many products were sold by color, by region. Plus, the PivotTable automatically calculates totals for both colors and regions. This is a great fast way to perform a visual statistical analysis on your data. Try it out next time you need to build tables quickly.

Category: Excel TutorialsTag: Excel, How To, Instructions, Tutorial

About Andrew Childress

Previous Post: « Income tax calculation in Excel Formula for Income Tax Calculation
Next Post: Backtesting VaR in Excel Backtesting Var in Excel »

Sidebar

Recent Posts

  • Format Cell Alignment in Excel
  • Default Width in Excel
  • Translate in Excel
  • Switch Windows in Excel
  • EXACT in Excel

Categories

  • Basics
  • Examples
  • Excel Formulas
  • Excel Functions
  • Excel Shortcuts
  • Excel Templates
  • Excel Tutorials
  • Uncategorized

Excelypedia

Master Excel to add a highly valuable asset to your employability portfolio. Learn how to analyze data in Excel.

  • Facebook
  • Twitter
  • Instagram
  • LinkedIn
  • YouTube

Learn

  • Functions
  • Formulas
  • Shortcuts

Legal

  • Privacy Policy
  • Cookie Policy
  • Terms & Conditions

Help

  • FAQ
  • Contact Us

Copyright © 2022 · Excelypedia · All Rights Reserved

Back to top