Roadmap Template in Excel (Table of Contents)
Explanation of Roadmap Template in Excel
Roadmap template is helpful to visualize or track the events or changes that occur over periods of time. A project roadmap template outlines each task with start date & due date of it. Usually, a project event is represented graphically, an overview of the project’s events, goals and deliverables are presented on a timeline or Gantt chart in excel. Road map template helps out to visualize, plan and track the company projects in a better way, and it helps out to calculate the budget based on the timeline for each event and compare their actual cost or planned investment for each phase of the project. Gantt chart is most commonly used in the roadmap template, where it provides a comprehensive overview of each phase in the project.
The project roadmap should contain the following:
- Important milestones
- Key events, timeline
- Duration of each events & deliverables
How to Create a Roadmap Template in Excel?
Roadmap Template in Excel is very simple and easy. Let’s understand how to create a Roadmap Template in Excel with some examples.
Example #1
Initially, we need to enter the data table where it consists of three columns, i.e. Key event or milestone, start date & time taken or duration of a task (No of days).
The table contains a list of each task or milestone of the project from beginning to end with a time frame for each event. Including Key Events & Decisions, Start Date of that event & No of days to complete it or duration for each event.
Go to the Insert tab and select 3-D Stacked Bar Chart.
Now, you can observe a blank chart area appears, next to table range.
On the chart area, right-click, under that select the option of “select data”, select data source dialog box window appears.
In the Select Data Source dialog box window, select Add option in the legend entries (Series).
Now, we need to enter each column data set of table range, edit series window dialog box appears, in that, initially select the start date column range i.e. in that series name enter start date column title name cell i.e. “B3”, Later in the series values, enter the start date range i.e. by selecting from cells “B4” to “B17” and click on OK, now start date with data bars appear in the chart area (Blue Color).
Click on OK, again Select Data Source window appears, in that you can observe Date Series added.
In the select data source dialog box window, select add option in the legend entries (Series) again, you need to select the “No of days” column range i.e. in that series name enter “No of days” column title name cell i.e. “C3”, Later in the series values, enter the “No of days” range i.e. by selecting from cells “C4” to “C17” and click on ok, Now “No of days” with data bars appear in chart area (Orange color).
Once you click ok, again, Select Data Source window appears.
Now the Axis label range needs to be added, Axis label popup appears.
To enter a data in axis label range text box, select a key events range, i.e. by selecting from cells “A4” to “A17” and once click on OK the chart will be shown as below.
Now, all the tabular data is represented on the chart, you can resize the chart area based on your preference by dragging towards right & left. Currently, some issues in chart i.e. Key events are in reverse order: It can be rectified by right click on key events, Select Format Axis.
Format axis window appears, in that checkmark the Categories in reverse order.
Once the categories in reverse order is selected, key events appear in the correct order based on the start date.
Now, we need to remove blue data bars, which represents Start Date by clicking on it & Select Format Data Series.
Format data series window appears, in that select the option of no fill & no line, so that the Blue data bars disappear & you select those date range in blue data bars & delete it.
Now, we need to set date range, to get rid of the huge gap in the date range, it can be rectified by selecting date series by clicking on Format Axis.
Now, we need to set the minimum & maximum bounds range, the minimum range is set by selecting the date of the first event and converting the start date format to number format, and check out for the number & copy-paste it to the minimum text box.
Convert Date into Number Format.
Copy the start date number format of first event & last event, and paste it in minimum & maximum under axis options in bonds section, once you update the date in the minimum text box, you can see the change in date series range i.e. from 1/1/2006 instead of 7/24/1998
Note: Maximum bond, you can set as 42300 for better appearance.
The orange data bars & value in it can be formatted as per your choice, Here I changed the data bar color from orange to blue color & converted from box bar to cylinder bar and font format of the numeric value in it from black to white color for a better appearance.
Go to the Design Tab. Click on Quick Layout.
Now, click on Quick Layout tab and select the chart as shown below.
In the layout 7 design, we can add the Title at top of a chart as “Roadmap for Drug Discovery and Development Process”, vertical axis title as “Key Events” and No of days.
Start date data range can be added prior to Key Events of the chart to know the start date of each event or milestone.
Here data series are linked to the chart, once you update the date or event automatically chart gets updated.
Things to Remember About Roadmap Template in Excel
- Roadmap template helps in reduction of the amount of time spent in each phase of a project with updated planning & insights to projects.
- Roadmap template is most commonly used by product managers of pharma & IT companies.
Recommended Articles
This is a guide to Roadmap Template in Excel. Here we discuss How to Create Roadmap template in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
