My data set consists of columns containing universities in the Boston area, and rows containing zip codes. For each university and zip code, there are two data points: number of students in campus housing, and number in non-campus housing. So that's two independent variables (x, y), and for each such pair, there are two dependent variables z1 and z2. I'm trying to visualize this in Excel, but not finding a chart type that could handle it. My best idea would be a sort of bubble chart, with x and y on the axes, and the z1 and z2 represented as bubbles, but I can't make this work. Can anyone suggest a way to do this, or another visualization that would work?

  • 2
    $\begingroup$ Mapping would be a natural form of visualization. The zip codes tend to be associated with separate polygonal regions (whose coordinates can be found online or through third-party providers). Regardless, two comments seem particularly worth sharing here: (1) restricting yourself to Excel seems like it places unnecessary (and rather severe) limitations on what can be accomplished: is this restriction really necessary? and (2) the choice of visualization depends on why you want to do it: what information is it intended to present to whom? $\endgroup$
    – whuber
    Jul 9 '12 at 13:42
  • 1
    $\begingroup$ The person to ask is Jon Peltier, who does amazing graphics with Excel. $\endgroup$
    – Peter Flom
    Jul 9 '12 at 14:44
  • $\begingroup$ @whuber: +1 for mapping, but mapping two variables on a single map can get tricky (and you've actually got 6 here: zip$\times$on/off campus). I guess you could do a side-by side comparison, or difference map though. $\endgroup$
    – naught101
    Jul 11 '12 at 1:02

Using Excel, a quick way to visualize your data set is a small-multiple dot plot. Take each University in a separate chart and plot their housing counts per zip code. Your result could look something like this:

Small Multiple Dot Plot

Obviously, sorting and layout (columns v rows) will signicantly change what is emphasized. As an example, this chart is sorted by College A's difference between on and off campus housing.

Also, as @whuber mentioned, mapping is another good option (even in Excel). You could also build a small multiple choropleth set of zip codes with the difference between on and off campus counts expressed through a divergent color scheme. My question would be if their location is really an important analytical component, or if their zip code really was more a proxy for another character (e.g. income, race, age, etc...).

For a quick easy way to map in Excel, check out the tutorials at tushar-mehta and Clearly and Simply

And, you can't go wrong checking out John Peltier's website.

EDIT: How to create a small-multiples dot plot in Excel (that doesn't look like Excel).

  1. Start with your data-I used the same format you described in your question, with two columns per university (on and off campus) and zip codes in rows. To make things easier I formatted them as a table. Then I added an additional column (that I used to sort the results) for each university that calculated the difference between on and off campus housing. Now's as good a time as any to choose your focus-which university will be first and how will the data be sorted? For the example, I chose university A, sorted by the largest difference between on and off campus housing.

  2. Next, create your first chart (there's a total of three, one per university). The chart is a simple line chart with two series on-campus and off-campus counts. To make things simple, format this chart like you want the others. In the case of the example, I did the following:

    • formatted each series with no line
    • formatted each series with the same size/shape marker, but had one with white fill, and the other with gray fill
    • added high-low lines and formatted them with a dash
    • removed the x-axis (markers, labels and line
    • formatted the y-axis to have a max that was greater than all values (since this chart will be copied repeatedly, once for each university)
    • formatted the chart and plot areas to no fill and no border
    • removed legend

    Here's an example of the before/after of the chart (pay no attention to the chart junk shadows I have on the data points). Once you find a style you like, its worth saving as a chart template so you can quickly apply it in the future.

    before and after

  3. Now that your chart looks like you want them all to look, copy the chart for as many universities as you want to compare.

  4. Now modify each series for the appropriate university's data. It's easiest to do this by selecting the series in the chart and modifying the formula directly in the formula bar. In this case, its simply a case of changing the column reference (e.g. B1:B10 to C1:C10). If its more complex, or if you have alot of changes to make, I would suggest named ranges or VBA.

  5. Using Excel's grid, line the charts up so that the y-axis and the x-axis categories are aligned. To make it easy, use Excel's Snap-to-Grid feature (Page Layout > Arrange > Align) and align both the chart and plot areas, both vertically and horizontally. If you size approriately before copy/pasting, all you need to do is stack.

  6. Add a data legend at the bottom of the bottom chart.

  7. If your Excel columns are equal width (which they should be unless you modified them, in which case, make them equal again), you can put your column labels in the cells directly above each dot-plot column.

  8. Add a title in the cells above your column labels and center across your selection.

  9. Finally, in view turn off your grid-lines and you'll have a chart that doesn't look like Excel.

Here's what it looks like when complete. The red lines show how the charts match up with the Excel grid (which I've turned back on for the image). All the text in red is directly in Excel (I used formulas to pull the appropriate zip code from the table, based upon the sort order). All other text, lines and markers are in the 3 charts, but instead of default black, I've changed them all to dark gray.

enter image description here

Hope this helps.

  • $\begingroup$ Wow, this is terrific. I need to think about this. Did you make that chart in Excel (doesn't look like it to me), or in a much more sophisticated charting tool? $\endgroup$ Jul 10 '12 at 14:23
  • $\begingroup$ Thanks, its just Excel (no special add-ons or VBA). I'll post more detail about how later, when I'm back at my computer. $\endgroup$
    – dav
    Jul 10 '12 at 14:57
  • 3
    $\begingroup$ (+1) nice example. Mapping in excel is difficult, Jorge Camoes has some nice examples, but visualizing detailed choropleth maps is quite impractical (it takes manually digitizing the shapes in excel). Point patterns aren't as hard though. A related question came up recently on the GIS site and suggested an ESRI ArcMap add-on for excel. $\endgroup$
    – Andy W
    Jul 10 '12 at 14:57
  • $\begingroup$ I just tried the ESRI for Office add-on and it's neat, but still very much a beta (e.g. doesn't appear you can add custom polygons), so the above suggestions are still your better mapping bet. Regardless, the broader point about when a map is more appropriate then a chart still stands. $\endgroup$
    – dav
    Jul 10 '12 at 21:19
  • 1
    $\begingroup$ FYI, if you want to preserve list numbering, you need to indent (usually 2 spaces are enough) your text after a paragraph break. I fixed it for you. $\endgroup$
    – chl
    Jul 11 '12 at 13:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.