There's a reason for thinking about data display
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.
Quoting Edward Tufte in the Introduction of The Visual Display of Quantitative Information
Data graphics visually display measured quatities by means of the comined use of points, lines, a coordinate system, numbers, symbols, words, shading, and color ... Modern data graphics can do much more than simply substitute for small statistical tables. At their best, graphics are instruments for reasoning about quantitative information. Often the most effective way to describe, explore and summarize a set of numbers - even a very large set - is to look at pictures of those numbers. Furthermore, of all methods for analyzing and communicating statistical information, well-designed graphics are usually the simplest and at the same time the most powerful.
Types of charts
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.
Use a column chart or a bar chart to show comparisons
Column charts compare values across categories.
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.
Use a line chart or an area chart to show trends or change over a period of time
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.
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.
Area charts are a variant of line charts
Area charts emphasize differences between several sets of data over a period of time.
Start a chart by selecting the data (the source data) you want to chart. When doing so, keep the following points in mind:
- Excel will use the column and row headings as the axis titles and legend (you can select which is which)
- if your chart includes a total row, you most likely will not include this in the charted data. Just include the individual data series
- in some charts, you can chart only one series. That means you can select only one set of data to chart.
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
- go to the worksheet where the data is located and select the appropriate data array
- NOTE: you usually do not want to select "Totaled" cells, columns, or rows
Select the Insert ribbon to see your chart options.
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.
To work on making the chart look and display as you wish it to, right click to reveal the chart editing options.
You have many formatting options, depending on the part of the chart that you select. Chart areas include:
- Chart area
- Plot area
- Data series
- Chart title
- Category axis title
- Value axis title
- and more depending on what options were selected in creating the chart.
You can also do more specific editing by right clicking and selecting the data to edit.
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.
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.
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.
You will need to change the series to a different chart type.
And then work to format two axes to depict two data sets on the same chart ...
... which will allow you to have two separate scales on a single grid.