If you Google (or search with any of your favourite search engines) the term “Road Map” you will be inundated with helpful videos and web pages teaching you how to create your very own Road Map.  This is great if you have the time and inclination but sometimes you just want one that is already made so that you can actually get on with the task at hand and create a road map.

So that is exactly why I have created this post.

Recently I found myself in exactly this situation and created a road map in Excel.  I have indeed borrowed ideas from across the web – there’s no point reinventing the proverbial wheel, is there – and ended up with a very simple Excel workbook (file) with one table and a chart.  I, of course, added a few cheeky formulas in order to make the chart render nicely from the entered data in the table but apart from that the rest of it is just a little formatting.

So please feel free to download a copy of my Excel Road Map and use it as you wish.

Kirkpatrick Consult Limtied - Dynamic Road Map - Excel Template
Kirkpatrick Consult Limtied - Dynamic Road Map - Excel Template - 02
Kirkpatrick Consult Limtied - Dynamic Road Map - Excel Template - 03

Hints and Tips

  1. Pressing the following keys: “Alt” then “A” then “R” and finally “A” will refresh all of the data on the worksheet and so update the chart.
  2. The TODAY row in the table has formulas in the Date and Progress columns.  Be careful not to write over these by mistake.
    1. The Date formula is just =Today() and so returns today’s date.
    2. The Progress formula is more complicated but just shows the percentage of the day that has passed by comparing midnight with the current time.
  3. The Date column has some validation in it and expects a date that is larger than 01/01/1900.
  4. The Progress column has some validation in it too and expects an integer (whole number) between 0 (zero) and 100 inclusive.
  5. The Milestone column is free text but you probably want to keep this a short as possible so that the chart is still readable.
  6. It does matter what order the table is in (you can sort it easily enough using the column sort/filter button) and should be sorted in date order in order to look good.
  7. There is a hidden column that uses the RANK function to change the position of the Milestone titles in the chart.
    1. The TODAY label will always be at the top.
    2. Each odd number ranked Milestone will be above the timeline (x-axis).
    3. Each even number ranked Milestone will be below the timeline (x-axis).
    4. The distance away from the axis is determined by the last digit of the rank so repeat every 10 milestones.
  8. The MonthYear labels on the x-axis (at the bottom) will automatically update.
  9. If the TODAY row in the table moves the light blue marker in the chart will be on the wrong milestone.  You will have to change this manually.
    1. Click on the blue dot so that only it has the focus (four dots in a square around it) and not all dots and then right click and change the Fill colour.
  10. If you get stuck and need some pointers, please feel free to reach out.  (Use the contact page).