Section 12.3. Charts

12.3. Charts

A chart is often the best way to present the meaning in a table of related numbers. Trends and relationships stand out in a chart. Numbers are abstract, but in a well-designed chart conclusions are obvious.

Excel's Chart tool gives you the ability to represent data with up to three dimensions easily. Pivot charts or combining charts with controls can make your display interact with users and allow you to handle more than three dimensions.

There are a lot of chart styles to choose from in Excel, and this can lead to confusion. In general it is best to use the simplest representation that captures the meaning in the data. Just because a feature or chart element is available does not mean you have to use it. In most cases a simple line chart is the best choice. In Figure 12-1 the same information is displayed on two charts.

In the top chart I used several of Excel's charting options to create a complex and messy view of a simple list of numbers. All of the meaning is in the bottom chart. If a feature does not serve a purpose in the chart, it is best to leave it out.

When presenting two-dimensional data, charts with depth or contour can be confusing. This kind of chart can become incomprehensible if printed. A plain surface chart has as much meaning and is easier to understand.

The more complex the data the more important it is to make the visuals simple. For example: I want to show how trading volume and day of the week relate to the probability that a certain stock will go up or down. First, I assemble the data in Figure 12-2.

Figure 12-1. Two views of the same information

Figure 12-2. Stock performance by day of week and volume

I used Excel's QUARTILE function to assign each trading day to a volume quartile. In the table, the columns tell which quartile the day's trading volume falls in. The volumes in quartile one (Q1) are relatively low, while quartile four's volumes are the highest.

The values in the table are the number of trading days that were up (close was higher than previous day's close) divided by the total number of days for that day of week and quartile. The Wednesday Q1 number is 0.48. This means that 48% of Wednesdays in Q1 were up days.

Just looking at the data you can see that something of interest is going on. But a chart explains it better. Figure 12-3 shows the data on a 3-D column chart.

Figure 12-3. 3-D column chart

This is better than looking at the numbers, but is still not clear. The columns for Q3 and Q4 are largely hidden, and it takes a real effort to find the meaning. The plain surface chart in Figure 12-4 does a better job.

Figure 12-4. Surface chart of the same information

Can you see it now? Q2 has the best performance, especially on Mondays and Wednesdays. This chart is a better way to show the meaning in the data because it is simple and gets right to the point. It respects the limitation of a two-dimensional display and uses shade for the third dimension. The 3-D column chart tries to show all three dimensions using perspective and becomes a work of art instead of a communication tool.