PivotTab.XLS  Cross-tabulating Data on a Microsoft Excel Spreadsheet
by John O. McClain    Links: [ Free Software | John McClain's Page | Errors ]   Feb. 27, 2008

New: A version compatible with Excel 2007.

This spreadsheet shows you in detail how to use Microsoft Excel's Pivot Tables to cross-tabulate data on two variables. An example is shown, with directions, on the "ExampleTable" worksheet.

When a Pivot Table displays "Count", you can do a Chi-Squared test of independence. To make this easier, I have written a Macro to do the calculation. The macro includes a warning if cells have expected values less than 5, since the Chi-squared approximation may be inaccurate. It also produces a "p-value" (significance level).

This is a very simple spreadsheet, intended for teaching purposes. You are welcome to use it in any manner, and change it as you see fit. It comes without any guarantee whatsoever, and is distributed free of charge.

Click here for the Office 2003 version: PivotTab.xls .

Click here for the Office 2007 version: PivotTab 07.xlsm .

---

If the ChiSquared Macro Doesn't Work:
Some users find that the macro does not appear in the Macro menu. If this happens, Excel has automatically disabled the macros that make it run. To fix this (or to make sure it does not happen) follow these steps:

1. Start Microsoft Excel.
2. On the Tools menu, point to Macro, and then click Security.
3. Click the Security Level tab.
4. Click Medium. Then click OK.
5. Exit from Excel.

"Medium Security" will cause your computer to warn you when an Excel File contains macros, giving you the chance to disable them if you do not trust the person who supplied the file.

Back to: [ Free Software | John McClain's Page ]