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.
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 oddnumbered columns for the y axis values, and use the evennumbered 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.
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.

Categorical variables 
Encoded 
Numerical variables 
Encoded 
Good for… 

Pie chart 
1 
Color 
1 
Angle 
Simple proportions 
Line chart 
0 or 1 
Color 
2 (including date) 
Position 
Trends over time 
Bar chart 
1 or 2 
Position, color 
1 
Length 
Broad audiences, precise comparisons 
Scatter plot 
0 or 1 
Color 
2 
Position 
Correlations 
Bubble chart 
0 or 1 
Color 
3 
Position, area 
Extra variables 
Heatmap 
0, 1, or 2 
Position 
1, 2, or 3 
Color, position 
Comparing categorical variables 
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