Excel - Creating a Timeline

January 2017



Learn how to create timelines under Excel!

Creating a basic timeline

The general principle


We will use a graph where the x-axis will be graduated in years.
  • Labels identifying the event will be entered one by one.
  • The vertical axis is graduated from 0 to 10, to set the location of each label, in order to avoid any overlapping.
  • We will also hide the gridlines .

Procedure



We wish to indicate milestones in the life of Victor Hugo ( series 1), and the publication of his great works (series 2).
The first step is to compile a table of data:
  • In column A enter the year,
  • In column B the scale chosen for the biographical events
  • In column C the chosen literary work .
  • In column E contain the titles of labels.
  • Series 1 and 2 have different colors.


Creating the timeline

  • We select the A1:C9 range and click on Insert/Graph/Column/ 2D histogram .
  • Graph containing 3 bar of is obtained, as Excel assumes that the numbers in A are the first bar.
  • Right-click on a bar, choose "Select Data" and "Delete" the "Victor Hugo" series and change the x-axis by selecting A2:A9
  • There are 2 bars left and the dates are displayed horizontally.
  • Right-click on the bar and Format the data set to your convenience.

Remove the gridlines

  • Right-click on the first bar, select Add Data Labels, click on a label: all are selected, click again: only the label marked as 9 is selected, type the = sign and click in E2:
  • In the formula bar you can read =Sheet1!$E$2, validate; the label dislays "naissance".
  • Continue for three other labels. Repeat the procedure for the second series.
  • Click Format the data labels. Fill-in the first series with a pink color and the second series with a pale green color.
  • We shall notice the overlapping of the 1853 and 1859 labels, to remedy this, simply change the values ??in columns B and C, for example put 2 in C7 and 1 in C8.


Creating an improve timline


If you want the x-axis elements to be proportional to the dates , you will need to use a table with 84 lines, instead of 9.
There will always be eight dates with events and 65 without date.
The procedure and implementation are exactly the same .
It will thus comply with the time scale and provide a better visualization of the evolution of Victor Hugo.

Related


Published by deri58. Latest update on September 6, 2013 at 07:05 AM by deri58.
This document, titled "Excel - Creating a Timeline," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).