INLS161-001 Fall 2022

Tools for Information Literacy




Using Excel to create charts

Types of charts

Spreadsheet data can be converted into many different ways of displaying data in charts.
But one needs to know what type of display is best suited for the underlying data.

Charts are a useful way to illustrate data shown in worksheets. Excel has several different types of charts to select from depending on the sort of information you are using and the effect you wish to convey in the graphic.

back to top

to show comparisons

Column charts compare values across categories.

[column chart types]

The same is true in Excel for Mac

[column chart types]

bar charts are essentially the same thing, but oriented on the horizontal axis. Excel asserts they are the best chart type for comparing multiple values.

The same is true in Excel for Mac

[bar chart types]

Another way to consider charting parts to a whole

back to top

Line charts compare continuous data over time against a common scale and are ideal for showing trends.

[line chart types]

The same is true in Excel for Mac

[line and pie chart types]

Area charts are a variant of line charts

Area charts emphasize differences between several sets of data over a period of time.

[area chart types]

Scatter charts

Scatter charts compare pairs of values, depicting them as sets of X and Y coordinates.

[scatter chart types]

back to top

percentages

Pie charts display percentages

But column charts do the same and in a more revealing fashion. Use a pie chart to show the relationship or proportion or parts to a whole, only when you want your chart to be metaphoric.

[pie chart types]

To quote Edward Tufte in The Visual Display of Quantitative Information, p. 178

... the only worse design than a pie chart is several of them ...
Given their low data-density and failure to order numbers along a visual dimension,
pie charts should never be used.

back to top

Creating Charts

Start a chart by selecting the data (the source data) you want to chart. When doing so, keep the following points in mind:

Charting Tips

One can create the chart on a new worksheet or within the current worksheet, depending on the data is to be displayed.

Change colored data series to patterns and shades of gray if you plan to use the chart in a print document.

Using the Chart tool

Select the data array to be charted

Select the Insert ribbon to see your chart options.

[Excel 2007+ insert chart ribbon]

The same is true in Excel for Mac

[Excel 2013 insert chart ribbon]

The tool will place the selected chart type on the same worksheet. You may wish to change this by moving the chart location to its own sheet.

[Excel 2007+ chart tools ribbon] [move chart dialog box] [an Excel chart on its own worksheet]

To work on making the chart look and display as you wish it to, right click to reveal the chart editing options.

[right click to reveal chart editing options]

You have many formatting options, depending on the part of the chart that you select. Chart areas include:

[format chart area dialog box]

The same is true in Excel for Mac

[format chart area dialog box] [format axis dialog box] [format plot area dialog box] [format legend dialog box]

The same is true in Excel for Mac

[format legend dialog box]

You can also do more specific editing by right clicking and selecting the data to edit.

[select data dialog box]

The same is true in Excel for Mac

[select data dialog box] [edit data dialog box]

back to top

Chart Formatting Things to know...

... when fine-tuning your chart

When you put your cursor over an area of the chart, a tooltip appears. The tooltips provides chart information. Each of these areas can be edited/formatted. If you put the cursor over data point in chart, the tooltip will tell you the value of the data point.

[add info dialog box]

You may wish to add things to the chart that do not exist in the underlying data array.

You can add arrows, free-floating text, etc., using the drawing toolbar, but make sure the chart is selected before you add the arrow, for example, or it won't be put on the chart.

[adding objects to the chart]

You can chart data on two axes to compare two values.

If you want to plot two values on the same grid, you can choose to have two differing axes if you use two differing data types.

[two data sets in columns]

You will need to change the series to a different chart type.

[change chart type] [two data sets in rows and columns]

And then work to format two axes to depict two data sets on the same chart ...

[format axis dialog box]

... which will allow you to have two separate scales on a single grid.

[data on two axes]

But you are not limited to the ribbon

You can make your own charts using the tools available. Jon Peltier offers a suggestion or you can follow Microsoft's advice.

back to top

Copyright © R.E. Bergquist 2014- | Last Updated on | Powered by w3.css