Excel Project Timeline - Step by step instructions to make your own Project Timeline in Excel 2010

Excel Project Timeline - Step by step instructions to make your own Project Timeline in Excel 2010

The Launch Excel Timeline Template has been purchased by hundreds of professionals just like you. You can get your own copy by visiting - http://www.launchexcel.com/timeline-videos/

Advanced Excel

Do you want to become an Excel expert? Learn how to use Excel more powerfully than ever before. Take a look at the Excel Expert course by Mynda Treacy:

Go to this link - http://www.launchexcel.com/moth-excel-expert

This video is a 24 miunte in-depth explanation showing how I created a project timeline in Excel 2010.

I take you step-by-step through most of the detail, and give helpful tips along the way.

Also read my article which has additional information: "How to create a Project Timeline Template today in 10 simple steps using Excel 2010" http://bit.ly/xlv0Oz

Enjoy!
Victor

P.S. Here's the purchase link again:
http://www.launchexcel.com/timeline-videos/

---

Closed Caption:

how to create a project timeline
template in 10 simple steps using Excel
2010
just a quick note for you if you're
watching this on youtube
I'd advise you to come over to my
website and check out my article because
you can download the template from there
and also you'll see my written
instructions
I step-by-step instructions for how to
create this timeline so let's start with
the end in mind
this means if you're doing this from
scratch without any of my step-by-step
instructions
I'd recommend you get a blank piece of
paper and start sketching out the
timeline for example like this
you got the axis and then you might have
the duration bars up here some more
tasks and phases and duration bar a
couple more there and maybe the
completion percentage and green or just
just get it out like this doesn't have
to be pretty as you can see mine is
pretty messy but then just get a rough
idea of what it is that you're looking
for and maybe just a red line like so
that's today line show where we are
today
so rough sketch like that
but since i've already created the
timeline and you can download it at my
website on the the article link
let me show it to you having this in
mind helps you as you progress through
my steps for example you'll see that we
have tasks and faces along this side and
got groups of one two three four groups
so we got four phases to work on and
we've got our chart legend up here and
we've got day line that moves with the
date
so keep that picture in mind as we work
through these steps
step to create your project events table
now in my template i plan to put the
scatter chart here and then below that
we have the data set
so let me just scroll down you can have
a look at that in more detail and row 30
I've got the table headings just above
I've just recorded what the different
parts of the table do in the chart
and i suggest you check out my article
for a full explanation of each of the
columns and the contents of them
step 3 insert the XY scatter chart so we
have here this space where the charts
going to go
let me get rid of this text box and
let's now create the XY scatter chart to
visualize the project event data
we're going to do is select cell before
we go to the insert menu
the charts group i'm going to click on
this scatter chart
- select scatter with only markers
now reposition this now what helps is
holding down the Alt key as you drag and
reposition because that gives you the
ability to snap to cells
that's a good way to accurately position
I've got this chart in the range be four
- k 22k 26
I will
right click the empty shot
i will click on select data
and now I'll add a data series by
clicking on the Add button
for the series name i'm going to select
e30 which is duration
for the series X values I all select c33
down here to see 46
series y values i'm going to select h 33
to a ch-46
and now click OK and ok again
scroll up and we should see
the simple scatter chart with the
default formatting
let's format the event markers to green
diamonds
click on add data . to select the series
right click on the data .
and select format series click on the
marker options and select the built-in
market type
let's choose to stick with a diamond and
will increase this to size 10 and now we
go to the market Phil we select the
solid fill we wanted to be a green now
closed that
so now we've created a scatter chart
with one data series called the duration
what we should do at this point is
change this text to say double click
that
let's change that to a project timeline
step 4 clean up the chart formatting on
your timeline scatter chart in Excel
2010 which is what I'm using here a nice
way to do this is to use the chart tools
menu which comes up when you click on a
chart to appear chart tools
i'm going to click on layout the first
thing I'll do is turn off you why taxes
taxes
the primary vertical axis I'll click
none
second thing i'll do is to go to the
horizontal guide lines here are the
primary horizontal guidelines
i'm also going to turn them off because
we don't even the third thing i'm going
to do is to turn off the legend
we're going to add our own quite special
text boxes in step 10
well at least in my opinion I think
they're quite special so we're going to
turn off the default legend
and then finally I'm going to choose to
display data labels and position and
left of the data points
right now we come to adding the event
labeled doing this step
it's quite tricky and time consuming
because you need to select each and
every label in the data series and then
we label it
let me show you how to do the first few
and step by step
then I'll was through the rest so you
click on the event Babel that selects
the whole data series so effectively
selects all the data labels for the data
series and make sure you don't click on
the actual green marker because that
selects the data points you want to
select the labels now click on it again
and you'll see that just select the one
label press equals and that starts to
type a formula in the formula bar Pia
now click on in this case it would be d
33 so phase one that's our first event
you'll see US enter this formula equals
timeline !
the 33 which is the external reference
to this worksheet
press ENTER and you'll see it's changed
that to phase one which is what contents
of cell d 33 was i'll do that again for
this data label to click on that or
press equals and then i'll go down to
the 34 which task 1 - 1 placenta and you
watch this data label change
ok so let me do that for the rest of
these data labels and I'll just speed
this up a little bit
yeah
right so now I've done all the adding
labels
I'm going to freeze panes just a row 28
that's so that i can keep the timeline
in view all the time
way to do this is to go to the View menu
go to windows and the window group
freeze panes and freeze panes
you'll see this new black lines been
inserted if i scroll the scroll bar
you'll see this bottom half of the
window moves and not the top part
so this project timeline can stay and do
all the time
just a quick note then on adding or
changing the event tables
it's you took me quite a long time just
to do this handful of tasks if you
wanted to have more tasks or if you just
don't want to fiddle around with edits
in each level you might be able to use
macros or vba to automate this and i'll
touch on this briefly in my next article
which you'll be able to find when it
comes online at www.hsn.com /a timelines
- in - accel
- bonus
in step 5 will add graduation bars to
show how long each of them takes and
also blue drop lines down to the axis
way to do this is using X aero bars and
yr bars but first let me just remind you
which ones which
so this one is the y axis and this one
is the x-axis and what we're looking to
do is add gray duration bars this way
for the two phases in the tasks
and like so each task then we'd like to
add blue drop downs from the mile
markers event markers down to the axis
and this way as well
so the first thing we'll do is we'll
select the chart
just like the background there in the
chart tools menu click on layout and in
the current selection group will need to
select the series duration
what we're going to do now is look at
the analysis group and the error bars
will click more error by options which
will bring out the aero bars dialog box
want to make sure first we've got the
extra bar selected so let's go
select those first and it should say
horizontal aero bars here first thing
we'll do is to display them in just a
plus direction which is to the right and
we'll have no cap
we don't want an extra line sticking up
and here we will select custom and when
we specify the values we are going to
specify the range
e 33 for the positive so II 33 down to
be 46 which is the duration and we don't
care about the negative ones because
we're not showing the negative x aero
bars
click OK so you'll see if I just move
out the way we'll see the arrow bars
come out and now we want to do is do
some formatting for them to make them
gray
so like color one solid lines will
select the color gray will go to the
line style
let's change the width to something like
four points
and more clothes that i actually don't
close it yet
we'll leave it open but what i'll do is
i'll move this out of the way and you
can see that we've added the great
robots so that shows how long event
takes the next step will do is to go
back up here and select the Y aero bars
you should notice it says the vertical
error bars
what we'll do here is change the
direction - - which just does from the
marker down to the axis will select the
InStyle no cap
we don't want an extra line . the end
and he will be a percentage
let's do a hundred percent and what
you'll see there is it has added these
drop lines to the axis
and now let's do a bit more formatting
will go line color
I'll choose a blue color let's choose
this blue and i'll change the line style
let's make this two points so half with
the great ones and it's do a - type that
select - is like so
i will close that
there we are we have the duration bars
and the drop lines
step 6 is to add data series for
completion and what I mean by that is a
green line that shows approximately how
much of the phase is complete
that's ninety five percent hundred
percent complete maybe nine percent
complete and saying with phase two could
be 280 simply task one almost done for
us to just started and tossed three
phase 3 not started and face for not
started
so we will add a data series
select chart area right click on it and
select data next wheel
add a new data series will call that
completion which is in cell G 30 will
select the X values at cells
see the teeth c33 to see 46
and the y values will select @ h 33 to
age 46 which is the height
click OK and click OK you'll see that
it's added those red markers
step 7 is to format the new data series
so we will go to the chart layout
and select series completion
that's format selection
what we want to do first is change the
shape of the markers so if marker
options will go built-in will change the
type to a diamond
and change up to size 10
Marcus fill the solid fill and the green
color which is the same as the same
green as the other data series
now we're close that and with this data
series still selected I'm going to go to
remove the data labels up here
press and none
right that looks better
so now we have a new data series and
what we need to do now is add the the
actual completion percentages so to do
that I'm going to go up here
series completion make sure that
selected go to the error bars then you
will go more Arab options now with the
vertical error bars
I want to make sure there are none so i
will put a fixed value of zero and with
the ex aero bars which I can get here to
cause serious completion xterra bars
you'll see it says horizontal bars
i'm going to change the aero bars -
Plus no cap the custom value which is
going to be positive values so X error
bars
going to be the completion days that's
g33 to g-46
it's okay
and now we're going to format the color
we're going to go solid line change the
color to a green and i'll change the
vine style 27 points
and click clothes now you'll see we've
got we got the data series completion
and we've got one more thing I need to
do actually let's go back and select
this case i can see a little little
outlines on there which I don't want
let's go to select the chart area layout
will select the series completion form
at the selection forgot to change the
outline for the marker
mark a line color this is it so no line
and that's fine so close that now that
little line should disappeared
ok let's put up that finishes step 8
in step 9 we're going to add a line that
will show where we are today
make a big red line like that the way to
do this is to right click the chart area
gonna select data now let's add a new
series series name is going to call
today
the series X values i am going to select
see
31 to 32 c 31 to 32
and series y values i'm going to select
h 31 h 30 -
click OK
and ok again you'll see it added these
two green markers
first I'm going to do is change the
label so select clicking that once to
select both labels
click on the top one again we will
select just the top one
i will type in today enter
let's change the label let's - lets make
that bold by clicking bold
now i'm going to select the other label
actually stretched just that one
selected press Delete to get rid of it
now click on this one again
and it should bring up when I
double-click it will bring up these
options and what I want is to format the
label option so that they're above
let's do that it's closed there and now
if I click on the marker
I'm gonna go to layout select
and make sure I've got the series today
selected first thing to do is a terrible
eyes so aero bars and I go
moreira options with a vertical aero
bars
I want - with no cap and percentage
I'll fix at one hundred percent so you
see it's dropped down to Texas
now I'll change the line color as well
what a solid line want to make it red
change the line style has changed this
23 points wide that looks better now
we've got the vertical bars done
let's get rid of the horizontal error
bars I go select the series today x
terrible ours
it will show the horizontal there are
bars I'll just go fixed value and zero
so that's all good
make sure there's no cap close that
now if I click away the only thing
that's left is DeMarcus i'll click on
there
double click
I show do mark options none
yeah
come on
I should've done that for this as well
so none
close
we have a red today line and the last
thing I'm going to do is show you how
these formulas linked so there's a
former here in cell see that to the so
see that you want to go to both . b 2
which is the date here and to make this
dynamic you change it to today
enter and as soon as the date changes
the gay bar will move with it and step
10 we're going to add the chart legend
so i'll click on the time line chart
well and head up to the insert menu
we all click on text box
you notice when i come back here the
curse is different
I'll draw out a
a box like so then type in legend
now i am going to right-click on the
border
I'm going to fall out the object I'm now
going to change the background fill so
it's going to be a solid fill with a
gray or close that and now in here i'm
going to change the font color to white
i'm going to make it bold I'm going to
align it to Center and two middle and
that's looking a bit better now but the
last step is to hit the formula bar
click in their type equals and I want to
link that to sell be 30 which is
duration press Enter
and analysis duration whatever I change
self be 32
it will read up here so the next step to
do is to copy this and create another
one for the completion
so i'm going to copy and paste with
ctrl-c ctrl-v reposition this about
there
it doesn't have to be exact because what
I'm going to do now is press ctrl and
click on the other textbooks and now
when i go to the drawing tools menu and
click format
I can a range and our line
the center's like so now let's just
select the second text box
I'll now
right click the border format object
i'll change the solid fill color to the
green that matches our completion bars
and then i'll head over to the formula
bar and press equals and link that to
sell g30 which says completion
and there we have it that's our project
timeline with a two-day line chart
legends and
and duration and completion bars alright
so what now
well I suggest you download the template
if you haven't already done so i have a
play with it
create your own timelines
and have a check of the next article
I've got lined up which is going to
enhance this template so have a look at
my website check it out
in the meantime have fun playing and
learning and i'll see ya