Microsoft Office Tutorials and References
In Depth Information
Plotting Data by Numeric Year
Transforming the 1800s
dates to 1900s dates and
clever formatting allows
Excel to plot this data
with a date axis.
One solution is to transform the dates to dates in the valid range of dates that Excel can
recognize. You can use a date format with two years and a good title on the chart to explain
that the dates are from the 1800s. However, keep in mind that this solution fails when you
are trying to display more than 100 years of data points.
To create the chart in Figure 3.17, follow these steps:
1. Insert a blank Column B to hold the transformed dates.
2. Enter the formula =DATE(100+RIGHT(A4,4),LEFT(A4,2),MID(A4,4,2)) in cell B4. This
formula converts the 1836 date to a 1936 date.
3. Select cell B4. Press Ctrl+1 to open the Format Cells dialog. Select the date format
3/14/01 from the Date category on the Number tab. This formats the 1936 date as
6/15/36. Later, you will add a title to indicate that the dates in this column are from
4. Double-click the fill handle in cell B4 to copy the formula down to all cells.
5. Select the range B3:C17.
6. From the Insert tab, select Charts, Line, 2-D Line, Line.
7. From the Layout tab, select Legend, No Legend.
8. Right-click the vertical axis along the left side of the chart and select Format Axis from
the context menu.
9. In the Format Axis dialog that appears, on the Axis Options page, select the Fixed
option button next to Minimum and enter a fixed value of 20 .
10. Without closing the Format Axis dialog, click the dates in the horizontal axis in the
chart. Excel automatically switches to formatting the horizontal axis, and the settings in
the Format Axis dialog redraw to show the settings for the horizontal axis. In the Axis
Type section, select Date Axis. Click Close to close the dialog box.
11. From the Layout tab, select Chart Title, Centered Overlay Title.
12. Click the State Count title. Type the new title Westward Expansion<enter>During
1845-1875 Added 13<enter>New States to the Union . Click outside the title to exit
Text Edit mode.