Excel Chart Tips

See our handy Excel Charts and Graphs Tips sheet here.

For more details on formatting your chart such as editing titles, axes, changing the legend etc see this screen cast: https://youtu.be/BVXFiuM3DAw 

TIP 1. Before you start:

  • Know your data: spend some time examining the data and making sure you know exactly what is being measured and how it is being measured.
  • Think about the story you want to tell. What do you think is the most important feature of the data? What catches your attention, and how can you highlight that feature of the data? Or, if the assignment asks you to answer a specific question with the data, how can you organize the data to best answer that question?

TIP 2. Decide what types of charts are appropriate to represent the type of data you have: See https://support.google.com/docs/answer/190718 for more info

Pie Chart: Best to show parts of a whole, but hard to estimate size of slices. (E.g., the different components of government spending – military, education, social & welfare, infrastructure)
This example has more “slices” in the pie than recommended but since it is clearly labeled and the main story is about showing the relative (not exact) sizes of each section it is OK
Govt spending pie
 Line Chart: Best to show change over time with continuous data. (E.g., unemployment rate from 1995 to 2015) This example compares the unemployment rate for Whites and Blacks.  black-and-white-uer
 Bar/Column Chart: Best to compare data categories. Vertical columns or horizontal bars. (E.g., compare population between countries, compare sugar intake between countries or populations)  sugar chart
 Stacked Column/Bar: Best to compare data sub-categories or parts-to-whole. (E.g., what components makeup the cost of a product: labor, materials, equipment, marketing etc.; ethnic or age makeup of a population; comparison of # of users of Mac, Windows and Lunix; amount of time spent on various types of apps on a phone)  income_by_quintile_stacked_column
 Scatterplot: Best to show relationship between two series of data. Also called an XY chart. (E.g., relationship between education and income; compare education level and birth rate; or compare time spent studying and final grade)  pa.5.076

 

TIP 3.  Be willing to work with your data to tell you story:

If you want to compare a measure between 2 years, decide whether it would be best to graph the data for each of the two years or to calculate the difference between the two years and graph that.  Sometimes it’s best to try both and see which one communicates your message most clearly. Often you can tell a better story by changing your data a bit: calculate a percentage rather than the raw numbers, or calculate a change rather than plotting the original data.

Which chart tells the story better?

compare_years_income_distribution
Data as originally formatted
change_in_income_distibution
Calculate and graph the change rather than the original data

 

 

TIP 4.  Let Excel do the work for you:  

When you select the data and insert a chart, Excel with display it based on how it interprets the data. Look at the recommended charts option. Many times transposing the data, or retyping it in a cleaner way (leaving off some labels for example) will result in a very different (and possibly better) chart. Look at the differences in the charts below – the only difference is the word Quintiles in cell A3

Selected Data Resulting chart
excel_chart_bad
excel_chart_good

 

If you need to edit axis/category labels the easiest way is to edit the text in the cells containing the labels, not directly on the chart itself.

If you think your data is formatted properly but Excel still doesn’t display the chart how you think it should, look at the Select Data options and consider switching rows and columns. It may have unexpected results.

select_data1 select_data1chart
select_data2 select_data2chart

 

 

Do you want to change the order the legend_orderbars/lines/legend items appear? You can move the data into different columns or look at the Select Data window and you will see a way to change the order of the legend. This changes the order the bars/lines etc. are displayed not just the legend itself.

 

TIP 5.  Use colors wisely

  • Make sure your colors contrast and complement each other. If different shades are important, make sure they are easily distinguishable.
  • If your chart will be printed in black and white consider using different types of crosshatching rather than color or shades of gray.
  • Be aware colors appear differently on different screens and projectors.
  • Consider color blind viewers (8% of men, 4.5% of the total population) – will your color palette work for them?
  • Consult http://colorbrewer2.org/ for great advice on using colors.

In any case, be sure to print your graph out and see how it looks on paper. That way, you can experiment with different shades, crosshatching, or colors to make sure your final graph is clear and easily readable to the viewer.

 

TIP 6. Don’t shy away from data density: packing in a lot of data on one chart or in one display. You can add lots of data to a single chart if it is done well.

Sparklines are small, dense charts embedded into a spreadsheet or larger visualization. They are often used to show stock market data and sports statistics.

Stock Market Example Using Sparklines Sparklines for Baseball Statistics
sparkline1 AL_1967_3-768x295

A classic example of successful data density is this visualization of Napoleon’s 1812 Russian Campaign.  It was created by Charles Joseph Minard.  It shows the march on Moscow by French troops (beige route), where the thickness of the band represents the number of troops.  The retreat from Moscow (the black route) also measures troop size by the width of the black band.  It was clearly a disastrous campaign, with only a small fraction of the men surviving the entire journey!  In total, the map visualizes in two dimensions six types of data: the number of Napoleon’s troops; distance; temperature; the latitude and longitude; direction of travel; and troop location relative to specific dates.

Minard

 

 

 

 

 

 

 

 

TIP 7.  Keep the end goal in mind:  Strive for easy comprehension with a clear point of view. Show your data honestly by showing the whole picture, without distorting your scales or “cherry-picking” your data points.