Angela Zoss Guide for Excel: Data Visualization

Zoss, Angela M. “Designing Public Visualizations of Library Data.” In Data Visualization: A Guide to Visual Storytelling for Librarians, edited by Lauren Magnuson. Lanham, MD: Rowman & Littlefield Publishers, Inc., forthcoming. doi:10.6084/m9.figshare.3811713

Excel charts can be built either by using the "Select Data Source" box to select data for different parts of the chart or by highlighting a group of cells and using the "Insert Chart" menu.  The problem with the second method is that it's not always clear what arrangement of data will be appropriate for each chart.

Here is a list of data arrangements that should work well for the individual charts mentioned.  These "recipes" should work reliably, but if you have any trouble, you can always fall back on "Select Data Source" box, which can help you build the chart one axis and one data series at a time.

Pie Chart Recipe

Each row will become a separate slice, each with a separate color.  Rows should be sorted by the numerical column, in descending order, so the slices will all be in size order. 

Suggestion: try using the same color for all but one slice

Bar Chart Recipe

Each row will become a separate bar. Rows should be sorted by the numerical column, in descending order, so the bars will all be in size order.

Grouped Bar Chart Recipe

The same data structure should work for stacked bars and the other variations in Excel.  If the groups and colors don’t look right the first time, look for the “Switch Row/Column” button.

Line Chart Recipe

Each row will become a separate line in the chart. If the x positions and colors don’t look right the first time, look for the “Switch Row/Column” button.

Scatter Plot Recipe

When you go to insert the chart, select just the numbers. If you select the first column (the row names), Excel will use them as the x axis values, converting each name to an integer. 

Note: starting with Excel 2013 for Windows, you can use Format Data Labels to add unique names for each dot.  First Add Data Labels, then Format Data Labels.  Uncheck the default data label, then choose “Value From Cells”.  Select the cells that contain the labels for the individual data points.

Scatter Plot with Color

This is where Excel gets a bit wacky.  The first column should contain the x values for all data points, regardless of what color (or “series”) the point is in. Then, you will have two (or more) separate columns that contain y values for the separate series. 

In this example, each x value has only one y value, but that is not a requirement.  If there are two y values in the same row, Excel will still make two dots, both using the same x value.

Remember: select just number columns, no text columns. 

Bubble Chart Recipe

Three columns of numbers can either be a scatterplot with two colors or a bubble chart. The only difference is the chart type you pick from the menu.

Selecting only four columns gives unreliable results.  Excel might switch orientations and treat the columns as dots and the rows as the position variables.  Excel might use a sequence of integers for the x axis values, use the odd-numbered columns for the y axis values, and use the even-numbered columns for the sizes.  If there’s anything special you want to do, it’s probably best to build the chart one series at a time, manually selecting the values yourself.

Remember: select just number columns, no text columns.

Bubble Chart with Color

Selecting five columns of numbers before you insert a Bubble Chart will generate a bubble chart with two colors.  Just like the scatter plot with color, the first column contains the x axis values for both series.  Then Excel just cycles through groups of two columns, where the first column is the y axis position and the second column is the size. 

In this example, each x value has y and size values for only one series, but that is not a requirement.  You can use the same x value for two dots, but you need to have both size and value specified for both dots.    

Remember: select just number columns, no text columns. 

Different charts in Excel require the data to be organized in different ways.  Sometimes you will even want to summarize or filter the data before you create a visualization.  PivotTables make this process much easier by allowing each chart to have its own special data table, while the original data stays safe in a different sheet.  You should be able to use PivotTables to arrange data for the various chart recipes below.