Microsoft Office Tutorials and References
In Depth Information
Using a Workaround to Display a Time-Scale Axis
Data in Columns I:M record the number of people in the bank every time someone enters
or leaves. This data is definitely not spaced equally. Only a few customers arrive in the
10:00 hour, while many customers enter the bank during the lunch hour.
The top chart in Figure 3.19 plots the number of customers on a text-based axis. Because
each customer arrival or departure merits a new point, the one hour from noon until 1 p.m.
takes up 41 percent of the horizontal width of the chart. In reality, this 1-hour period
merits only 16 percent of the chart. This sounds like a perfect use for a time-series axis, right?
Read on for the answer.
The bottom chart is an identical chart where the axis is converted to show the data on a
date-based axis. This is a complete disaster. In a date-based axis, all time information is
discarded. The entire set of 300 points is plotted in a single vertical line.
Excel cannot show a
time-series axis that
The solution to this problem involves converting the hours to a different time scale (similar
to the 1800s date example in the preceding section). For example, perhaps each hour could
be represented by a single year. Using numbers from a 24-hour clock, the 10:00 hour could
be represented by 2010 and the 3:00 hour could be represented by 2015.
In this example, you manipulate the labels along the vertical axis using a clever custom
number format. A few new settings on the Format Axis dialog ensure that an axis label
appears every hour.