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
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.
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)
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)
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)

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?

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

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.

Do you want to change the order the bars/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

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.

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.