TECH-005 - Create a quick and simple Time Line (Gantt Chart) in Excel
TECH-005 - Create a quick and simple Time Line (Gantt Chart) in Excel
YOU CAN DOWNLOAD ALL MY FILES FOR FREE AT http://www.theexcelchallenge.com
Learn how to create a quick and simple Time Line (Gantt Chart) in Excel.
In this video you will use the MIN formula, the MAX formula, the IF formula, the AND formula, as well as ‘Greater Than’ and ‘Less Than’ conditions to create the Time Line (Gantt Chart) in your spreadsheet. You will also use Conditional Formatting to enhance the looks of your Time Line.
I hope this tool is something you can use in your daily routine so you get more with less effort.
If you have a challenge with Excel that you need help with, whether it is at work, at home, or at school, please write it in the comments below. I try to resolve common Excel challenges from our viewers and subscribers.
Thank you for watching.
Check out some of our previous videos in the links below:
* TECH-004 - Calculate total days between dates in Excel
• TECH-004 Calculate total days between…
* TECH-003 - Display images in comments
• TECH-003 - Insert images in comments …
* TECH-002 - Find intersecting values in a table in Excel
• TECH-002 - Find a value in intersecti…
Content
1.42 -> [Music]
6.14 -> hello everyone and welcome to the Excel
8.73 -> challenge if you want to learn how to
11.58 -> create a quick and simple timeline that
13.95 -> you can use in Excel and you can adjust
16.44 -> your task iterations and your start date
20.31 -> and end date and that sort of stuff
22.73 -> please stay tuned okay the first thing
27 -> I'm going to do is fill some general
28.74 -> information for project three green
33.68 -> project duration project start date and
40.43 -> project and be the name of the break is
45.719 -> going to be kitchen renovation I do top
51.239 -> now at this point the duration of the
53.07 -> prayer I do know we want to start on May
55.649 -> 1st 2017 just a so formatting here
65.539 -> the next thing I want to do is start
67.43 -> plumbing my timeline here so I'm gonna
70.34 -> enter a task ID a task description a
76.78 -> task duration start date and an end date
86.829 -> for this example I'm going to use a
89.119 -> total of 10 tasks and let's use some
96.229 -> borders here
102.439 -> now I can start entering the tasks for
105.63 -> the project
106.56 -> so a first task is going to be
108.509 -> demolition which is going to take four
113.61 -> days to complete the next task is very
116.81 -> install new tiles which is going to take
120.75 -> two days
127.41 -> and flying now delivering the customer
132.93 -> it's gonna take a day the start date for
138.28 -> my first task is gonna be made first
140.62 -> like we said before now the end date
144.01 -> should be equals to the start date plus
148.24 -> the duration of the task in this case
151.84 -> the Malaysian is going to last for days
154.45 -> therefore the end date is gonna be May
156.82 -> 5th for this project I'm going to assume
160.57 -> that I can only start a new task a day
163.57 -> after I finish the previous task that
166.45 -> being said the start date for new tiles
169.27 -> should be equals the end date for
172.48 -> demolition plus one day therefore May
176.02 -> 6th to get the end date of new tiles I'm
181 -> going to use the same formula that I
182.32 -> used above and I'm gonna fill down by
184.48 -> using control D so the end date for
188.14 -> install new tiles its May 8th since it's
190.42 -> gonna take two days to complete the task
192.28 -> I'm going to fill down this formulas all
197.049 -> the way to my final task I can see here
202.65 -> my starter is made first and my end date
206.29 -> is May 28th now that I have the list of
209.98 -> tasks the duration of each and the start
213.25 -> and end date for each the next thing I
215.86 -> want to do is that timeline next to my
220.36 -> first table I'm gonna start with my
222.549 -> start date which is May 1st I don't
228.73 -> wanna come to be this wide so I'm gonna
231.7 -> choose a vertical alignment
238.44 -> ninety degrees and do some formatting
246.69 -> and then I'm gonna draw this column till
251.98 -> the end of the month
252.91 -> I now have May first until May 31st the
259.6 -> last thing I'm going to do here is the
261.04 -> calculations let's start with project
263.83 -> duration in order to calculate the
265.81 -> project duration I need the project
267.22 -> start date and the project end date I'm
269.71 -> gonna delete this value here my sterday
273.73 -> will be the minimum value in the start
277.12 -> date wrench enter my end date is gonna
282.16 -> be the maximum value in my end date
285.34 -> wrench enter the pre iteration is going
289.72 -> to be the difference of the two end date
292.75 -> - start date enter my project duration
297.88 -> is going to be 27 days next thing I
300.73 -> wanna do is compare each column to the
304.87 -> start and end date of each task I wanna
307.57 -> start with me first I want to compare
311.05 -> May first and see if it's between the
313.63 -> start date and the end date for that I'm
316.21 -> going to use the if formula and within
319.57 -> if I'm gonna have a logical test that
322.21 -> includes the end formula which has two
325.69 -> portions number one is May first greater
331.57 -> than or equals to my start date does the
338.35 -> first half comma and is May first less
344.86 -> than or equals to my end date close
351.97 -> parenthesis if these two are true
355.24 -> I want Excel to drop an X in here if
360.01 -> it's not true I wanted like so I'm gonna
362.32 -> use quotation mark edition mark close
364.93 -> parenthesis and enter
366.68 -> and yes in fact may first falls between
370.06 -> my start date and mandate since I'm
373.37 -> going to be copying this formula to the
375.139 -> right I wanna lock my column D and I
380.57 -> want to lock my column E and since I'm
385.009 -> gonna be dragging this formula down I
386.78 -> wanna lock my row 5 and my row 5 enter
393.55 -> when I copy this formula down and to the
397.82 -> right
400.66 -> Excel does the trick so now I have a
404 -> timeline the last thing I'd want to do
406.94 -> is change this XS for a nice formatting
409.94 -> so what I'm going to do is select this
413.419 -> range I'm gonna use some conditional
415.82 -> formatting where I'm gonna say if the
418.19 -> cells are equal to X I wanna have a
426.13 -> green fill and I'm gonna pick the same
430.49 -> font color so I don't see an X but
433.789 -> instead a green square now I have a time
438.08 -> line so now I can play with the date in
440.63 -> my project for example let's say my
443.449 -> tiles are delayed and instead of taking
445.88 -> 2 days it's actually gonna take 4 days
448.18 -> let's see what happens to the timeline
450.409 -> oh the whole thing got delayed
453.47 -> now my prey duration is 29 days and my
456.02 -> parade end date is 31 days and let's say
459.949 -> that installing new cabinetry snuck away
463.76 -> three days is only gonna be one day
465.289 -> let's see what happens to the timeline
467.62 -> it'll just automatically time for the
471.74 -> bonus trick let's say your project is
474.199 -> delayed by two months so your new start
477.26 -> date is no longer May 1st
479.24 -> but July 1st let's see what happens to
482.3 -> the timeline
483.02 -> oops we lost it main reason is because
485.93 -> on the top we have the amount of main in
489.139 -> order to fix this we're going to say
493 -> this start date is always going to equal
496.3 -> my start date in this cell
501.04 -> the value in this cell is always going
503.72 -> to be equals my start date plus one day
508.31 -> and I'm going to copy this formula to
511.31 -> the right by filling it with ctrl R my
518.36 -> time line is back regardless of the
521.09 -> start date so let's say my new date is
523.64 -> actually Todd was first the dates on the
528.56 -> top changed as well
530.21 -> let's try one more time let's say my new
534.17 -> start date is October the first when I
536.99 -> hit enter the dates on the top change to
540.38 -> the month of October my new project
543.08 -> start date is October the first my new
545.24 -> project end date is October 28th my
547.67 -> project duration is 27 days and I can
550.34 -> continue playing with the timeline that
551.93 -> way I want so let's say 5 3 3 any
558.5 -> updates automatically that's it for
561.23 -> today if you have a chance that you can
563.21 -> help with whether it's at work at home
565.28 -> or at school don't forget to put in the
567.35 -> comments below I try to resolve the most
569.72 -> common challenges of our viewers and
571.67 -> subscribers thanks for watching and I'll
573.89 -> see you in our next video
Source: https://www.youtube.com/watch?v=00s5efQWrgU