From time to time, I get a worksheet full of data. There’s one column with a measurement. There are several other columns with different attributes. I want to find out whether any of these other attributes correlate with the measurement.

Here’s an example:

We might want to answer the questions:

• Is there a relationship between Score and Age?
• Is there a relationship between Score and Name? (Maybe people who are named Jim do better that people who are named Joe.)
• Is there a relationship between Score and Shoe Size?

I call these “slope charts” because you’ll typically have a lot of rows, you’ll sort the rows from small to large (or vice versa), and you’ll create a column chart that has a slope from one side to the other.

This is a job for Pivot Tables! I’ll tell you how to do one column (Name), and you’ll easily figure out how to do other columns.

1. Select the Score column.
2. Add the Name column to the selection (via Control-click on the column header).
3. Click Insert (on the menu), Recommended Pivot Table, and insert a pivot table to a new worksheet.
4. Change the Pivot Table settings (for Values) from Sum to Average (or not, if you really want to use Sum).
5. Sort the table by the Average column (by way of the drop-down in the Row Labels column – use More Sort Options).
6. Select the entire table.
7. Insert a clustered column chart.
8. Give your worksheet a good name, because you’ll probably be adding more tables/charts.