Microsoft Office Tutorials and References
In Depth Information
Plotting Data by Numeric Year
13. Click the title once. You should have a solid selection rectangle around the title. On the
Home tab, click the Decrease Font Size button. Click the Left Align button.
14. Carefully click the border of the title. Drag it so the title appears in the top-left corner
of the chart.
15. Select the dates in B4:B17. Press Ctrl+1 to access the Format Cells dialog. On the
Number tab, click the Custom category. Type the custom number format ‘yy . This
changes the values shown along the horizontal axis from m/d/yy format to show a
twodigit year preceded by an apostrophe.
The result is the chart shown in Figure 3.17. The reader may believe that the chart is
showing dates in the 1800s, but Excel is actually showing dates in the 1900s.
Using Date-Based Axis with Dates Before 1900 Spanning More Than 100 Years
Microsoft Excel 2010 doesn’t do well with large datasets that span 100+ years. Although I
managed to create a date-based axis covering 630 years with 10 data points, a dataset
covering 102 years and 40 points cannot display a date-based axis.
However, as Figure 3.18 shows, it is possible to create this chart. To do so, you must
transform the date axis into a scale that shows months, hide the axis, and then add your own axis
using text boxes. These steps are not for the faint of heart.
First, you need to transform the dates from the 1800s to the 1900s. Next, you will
transform the dates spanning 172 years into a range where each month in real time is
represented by a single day. This results in a time span of 6 years. You then need to use care to
completely hide the labels along the horizontal axis and replace them with text boxes
showing the centuries. Lastly, you add a new data series to draw vertical lines at the change of
To create the chart in Figure 3.18, follow these steps:
1. Insert new Columns B and C.
2. In cell B4, enter the formula =DATE(113+RIGHT(A4,4),LEFT(A4,2),MID(A4,4,2)) . This
transforms the dates from 1787 to a valid Excel date in 1900. Format this cell with a
short date format.
3. In cell C4, type the formula =(YEAR(B4)-1899)*12+MONTH(B4) to calculate a number
of months. Format this cell as a short date. This formula now reduces 172 years into
172x12 into 2,064 days, where each day represents 1 month of real time.
4. Select cells B4:C4 and double-click the fill handle to copy the formula down to your
range of data. The dates in Column B span 1900 to 2072. The dates in Column C span
1900 to 1907. Although the relative position of the data points is correct, you have to
hide the axis labels that Excel draws in for the horizontal axis. Therefore, it would be
helpful to draw in vertical lines to show where the axis switches from the 1700s to the
1800s. Then draw another line to show where the axis switches from the 1800s to the