Microsoft Office Tutorials and References
In Depth Information
Using a Workaround to Display a Time-Scale Axis
In the original chart, a time appeared in Column I, and a formula in Column L simply copied this time so
that it would be adjacent to the customer count in Column M. In step 1, the transformation formula is
applied to Column L.
Follow these steps to create a chart that appears to have a time-based axis:
1. In cell L2, enter the following formula to translate the time to a date:
Because each hour will represent a single year, the years argument of the DATE
function is =HOUR(I2)+2000 . This returns values from 2010 through 2013. The other
arguments in the date function are 1 and 1 to return January 1 of the year. Outside the date
function, the minute of the time cell is scaled up to show a value from 1 to 365, using
MINUTE(I2)/60*36 4. The entire formula is rounded to the nearest integer because Excel
would normally ignore any time values.
2. Select cell L2. Double-click the fill handle to copy this formula down to all the data
points. The result of this formula ranges from January 1, 2010, which represents the
customer who walked in at 10 a.m., to 12/25/2015, which represents the customer who
walked in at 3:57 p.m.
3. Select cells L1:M303.
4. From the Insert tab, select Charts, Line, Line with Markers.
5. On the Layout tab, select Legend, None. (After studying Software Quality Metrics
(SQM) data for Excel 2007, surely Microsoft realizes that 500 million people instantly
turn off the legend in every chart that has a single data series.)
6. Right-click the labels along the horizontal axis and select Format Axis to display the
Format Axis dialog box, where you make the following selections:
In the Axis Type section, select Date Axis.
For Major Unit, select Fixed, 1 Years.
For Minor Unit, select Fixed, 1 Days.
For Base Unit, select Fixed, Days.
Click Close to close the Format Axis dialog.
7. Return to the transformed dates in Column L. Select L2:L303.
8. Press Ctrl+1 to display the Format Cells dialog. On the Number tab, select the
Custom category. A custom number format of yy would display 10 for 2010 and 15 for
2015. Instead, use a custom number format of yy”:00” . This causes Excel to display
10:00 for 2010 and 15:00 for 2015, which is fairly sneaky, eh?
As you see in Figure 3.20, the chart now allocates one-sixth of the horizontal axis to each
hour. This is an improvement in accuracy over either of the charts in Figure 3.19. The
additional chart in Figure 3.20 uses a similar methodology to show the wait time for each
customer who enters the bank. If my bank offered 12-minute wait times, I would be finding
a new bank.