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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Categorical variables
Encoded as…
Numerical variables
Good for…
Pie chart
1
Color
Angle
Simple proportions
Line chart
0 or 1
2 (including date)
Position
Trends over time
Bar chart
1 or 2
Position, color
Length
Broad audiences, precise comparisons
Scatter plot
2
Correlations
Bubble chart
3
Position, area
Extra variables
Heatmap
0, 1, or 2
1, 2, or 3
Color, position
Comparing categorical variables
For more about data visualizations including strengths and weakness of chart types, see 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
While some types of charts are more commonly used than others - such as bar charts, line charts, and scatterplots - there are many types of graphs available to choose from. Your choice of a particular chart type for your data visualization may be constrained by the type of data you are working with or the number of variables you want to graph, or you may want to choose your chart type based on what type of pattern you are trying to show (comparison, part-to-whole, hierarchy, etc.). Here are several interactive resources to help you decide on a chart and, in most cases, find a tool to create it.
When designing a data visualization, the primary goal is to communicate information using visual means, and whether you share your visualizations in print or digital form, designing for accessibility is important. Although color is most frequently mentioned in this context, accessible design goes beyond avoiding color palettes that make charts difficult to read by individuals with color vision deficiencies like red-green colorblindness, or protanopia. There are many ways to make your graphs easier to understand for people with visual impairments or other disabilities, and designing for accessibility can make your data visualizations more readable for all users.
The following articles offer good overviews of the issues involved in designing for accessibility.
Read more about the Web Accessibility Initiative at w3.org.
The following tools can help you get started with choosing accessible color palettes and contrast ratios in your visualization.
In addition to color, there are other aspects of visualizations to consider when designing for accessibility. For additional guidelines, consult the CFPB Design Manual for Data Visualization.
