For a media planning project, we needed to find a simple way to illustrate how people in a particular segment engage with different media. After some experimentation, we came up with this "media wheel" chart that summarizes 216 data points from a media spreadsheet.  It shows (zoom in)  where -- at work, at home, in a restaurant, or in the car -- people are when they read newspapers, watch TV, or listen to the radio throughout a typical day. Lighter hues correspond to higher levels of activity.

Here's how we made it.

1. We started with a regular spreadsheet of daily activities data from a syndicated research run, similar to what you see in the first table below.  Activities are rows, day parts in one-hour increments are columns.



2. Since we wanted to show how an activity takes place throughout the day instead of comparing different activities with each other, we needed to "normalize" the data.  We used the LARGE function in Excel to identify the cells with the highest value in each row.  We then set all other cells in the same row as percentages of that highest value. The second table contains the "normalized" data. We also applied conditional formatting to the resulting table; here, the darker the hue, the higher the relative value. The formatting is optional but it gave us an early idea of how things were looking.

3.  The wheel graph itself was built by hand in Adobe Illustrator. Each data point's value relative to the highest in its row is tied to the corresponding level of color transparency.

Design: Eric Fensterheim, media design intern.