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.
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.
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.
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.
Recommended tutorials:
Format Cell Alignment in Excel