How to create Ultimate Excel Gantt Chart for Project Management (with Smart Dependency Engine)

welcome to this tutorial on how to create the ultimate excel gantt chart for project management a gantt chart is arguably the most widely used project planning tool and excel allows you to quickly set up a simple gantt chart but you can also build a really outstanding version with many advanced and interactive features making this a professional project planning and management tool let's take a quick look at the final excel template that we're going to build today it allows you to set up an advanced and workday-focused project schedule consisting of project stages with task and milestone items setting up a schedule for these items is as easy as defining the number of required workdays and then either entering an independent start or end date manually or making use of the core feature of this template a fully implemented dependency engine with this dependency engine you can make one item dependent on another one by creating a connection through an intuitive id system the columns next to the referenced id instantly display all the fundamental information about the referenced item and what makes this feature so incredibly powerful there are four possible dependency connection types to choose from which are either a forward scheduling finish to start or start to start dependency or a backward scheduling start to finish or finish to finish dependency notice how both the stage bar and the project time span instantly adapt to the changes made the way this dependency engine is built enables you to set up consecutive dependencies with different dependency connection types in a matter of seconds any new task or milestone item you add below a stage is instantly assigned to that stage via an automated stage id system the same way you can quickly create a new stage with its own items notice how the whole area and the item names are automatically formatted and structured based on the selection you make what is really amazing with this dependency engine you are not limited to creating dependencies within a stage you can also make stages dependent on other stages for a forward-oriented stage dependency for example you only have to make the initial item in your stage dependent on another stage then a set of standardized formulas allows you to make the stage update based on its items and finally you can make the other items consecutively dependent on that initial item define the number of workdays and make sure that the milestone ends together with the preceding task after that it will only take seconds to add another consecutive stage that will have the same forward scheduling dependencies with the stage start date tied to the other stage and thus making the time span grow into the future whenever the number of required workdays is increased in case you want to flip this into a backward scheduled stage dependency with the stage and date tied to the preceding stage you can simply reverse the dependency logic by rearranging the linked ids via drag and drop and updating the dependency connection types in the respective dropdown selections and now the more the number of workdays is increased the earlier the stage has to start you see this dependency engine enables you to quickly set up and modify a fully dynamic project schedule and always ensures that a change somewhere in the schedule will make all the dependent items instantly update in addition you might have noticed that every stage automatically gets colored with one color group per stage and different color shades for the stage the tasks and the milestone symbol this is made possible through an automated coloring engine that dynamically creates and applies color codes in the background depending on which color mode is currently selected whenever the color mode project structure is selected the colors are automatically assigned based on the stage id and the type of item the available color palette covers 8 different color groups that are applied in an infinite loop to ensure that you can add an unlimited amount of stages the team roles color mode allows you to visualize the project based on the respective role that the project team members that are responsible for an item got assigned right now only the project manager c miller is available here but once we open the settings area we get access to an incredibly powerful project role and team management system this system allows you to add an unlimited amount of team members to your project and group them into up to eight roles which can be based on responsibilities department or whatever makes sense in your specific case to add new roles to the project you only need to enter the role name and provide a shortcode after that you can add new team members and assign any of the roles that you have just defined on the left the assignment of roles is automatically tracked in the count column of the role type section and once you have added your team members to the list you can jump back to the gantt worksheet and assign any of the defined team members to your tasks and milestones the respective role is then automatically displayed and visualized with its own individual color in the gantt chart as this coloring is purely based on the project roles and not on individual team members a team member can easily be replaced by another team member of the same role without any visual disruption eventually in issues color mode every item that gets marked with an issue symbol is highlighted in a warning red color and of course you have always the option to deactivate all the coloring by switching into the default color mode once you have set up your project schedule you might want to keep track of the adjustments that you make to that schedule over time you can document the date of the latest update to the actual project plan and then in order to preserve a snapshot of that plan open the base plan section taking a snapshot is as easy as copying over the start and end date column as values and then documenting the snapshot date and then when time passes by and you make changes to your actual plan like for example postponing the project start you now have the actual plan and the original base plan side by side and you can easily visualize the comparison of both by changing the display mode to the plan versus space comparison that mode instantly reveals shifted dates and change durations while making sure that the timeline dates perfectly adjust to display both schedules at the same time of course this template also allows you to keep track of the stage task and milestone completion once you set show progress to yes the percentage completed is beautifully visualized as a filled bar for the stages and tasks and as a finish flag symbol for completed milestones you can even make the completion of milestones conditionally based on the completion of one or multiple tasks so that the milestone is automatically completed not before the linked task has reached the full 100 percent in addition a standardized formula allows you to automatically update the stage progress based on its items eventually this completion tracking feature is perfectly complemented by the date highlighting option which allows you to either dynamically highlight today and make overdue items before that line instantly catch your eyes or alternatively you can statically highlight any of the dates that are visible in the timeline and the selectable timeline dates are immediately updated in the drop down list whenever you scroll the whole gantt chart area to the right or left which might be especially helpful for projects of longer duration the whole worksheet is just an amazing tool to set up fully dynamic and easy to manage protect schedules it is created with a focus on lightweight design outstanding user experience and fast performance we gonna build the whole ultimate excel gantt chart template with all these amazing features step by step the final template file is available for download on excelfind.com link for that is in the description and now without further ado let's get right into it as the initial step we need to set up the general design for the gantt and settings worksheet for the gantt worksheet we start by setting up the header rows for our input and gantt chart area we select these two rows and decrease the general text style to size 10 make it bold and align to the center and middle after that we select the range b13 to w13 and give the cells a green fill as this is the row where we're going to put all the single column headers into in the row above we then create section names starting with these first columns as the section for ids then the next six columns for the item details then column k to t is where the actual planning will happen while the two following columns will be used to preserve a snapshot of the actual plan at a certain point of time so we're going to call this base plan for both these planning sections we want to be able to document the dates of an update or the saving of a snapshot and that's why we make use of these two additional rows here make the font for the input field a bit smaller and just insert a placeholder date value for now same for the base plan section and eventually let's add some light gray borders to the section cells for the single column headers we set the general font color to white and increase the rows height as we need a bit more space here we start with naming the first columns here as id then sid for stage id and rid for role id all of these columns in the id section will be automatically calculated so let's color them according to the legend that i have prepared up there and give these cells a lighter green fill then the columns for the item type and description are the first actual input columns this one will be a special column that only contains the indicator colors so let's give that one an even brighter green fill the next one is the column for the project roles which will be automatically computed based on the team member selected in this column and then as the final column in this section we make use of the unicode icons that i have prepared up there and this one will be the one used to highlight issues that you have with any of your items in the actual plan section the most crucial columns are the last two ones as these will be the calculated start and end date for every single item these two columns will contain the most crucial formulas in this worksheet and the start and end date calculation will always be based on two types of input the first one is the number of required workdays for an item and the second one is some sort of date it can be either a manually entered independent start date or an independent end date or and this is where the main power of this template will come from a dependency connection to another item such a dependency connection will require a link to the other item's id based on which then some crucial information will be extracted in these two columns and then we have one column to select the type of dependency connection and want to potentially add a lag by which you want the item to be shifted forward or backward for the best possible user experience we group these state-related input columns together and make them collapsible and finally let's color the snapshot columns that will be used to preserve the start and end date and make these two columns collapsible too the last column will be used for the proquest tracking so let's put a percentage symbol in there now this is the perfect time to quickly adjust the size of the columns to match the expected size of input and right after that we can start creating the timeline area for the timeline we will create one dark blue row right here and right above that we gonna select the cells across these three rows set the fill to a light blue make the column really tight and then merge these three cells together set this option to rotate text up text alignment to the top and the font size to 8. and now we can just select the whole column and transfer this specific formatting to as many subsequent columns as we wish using the autofill function and that's already it for the basic design of the header and timeline section here wonderful for a cleaner design let's hide the default grid lines and add a way better looking custom formatting instead initially we want to visually separate the main sections so let's hold the ctrl key and select the respective sections including the header rows and the number of content rows below with all these selected we go to the home tab open the border menu and click on more borders this will open this format cells window in which we can now select a border color like this mid light gray and then specify that we want to add a left and right border the overall spacing in this sheet here is aimed to perfectly fit into the open window with the actual planning section fully expanded and the base plan section collapsed so let's do that and then let's select the whole content area and add some horizontal row-wise borders with an even lighter gray perfect the basic timeline design and size is already defined so we now know the ideal position for adding a top right settings button that will let us jump straight to the settings worksheet for the button we merge these six cells set the text alignment to center and middle add a white border around them and label it settings after that we right click on it and click on link which opens this window as we want to create an internal link inside of the workbook we go to place in this document and select the settings worksheet we can even define a target cell so let's choose the cell in the settings sheet where we probably gonna place the button for jumping back to the gantt sheet later and eventually i recommend to also define the screen tip which is the text displayed when hovering over the button the addition of this link to our button has automatically changed the text format here so let's fix this and make the text bold not underlined and white now with the current setup only the text itself works as a link but not the full merged cell area but we can easily change that by activating the wrap text option and now these merged cells perfectly function like we expect a button to do we are redirected to cell x2 on the settings worksheet and gonna recreate a similar button that leads back to the gantt worksheet so we add the same white borders call it gantt add a link to the gantt worksheet cell bl2 then add a screen tip and apply the required formatting and now this works like a charm we're just going to increase the size a little bit and there we go the initial input section that we're going to create in this settings worksheet will be for some basic project details we add some grey borders around that section and adjust the column size so that we have two small columns on each side acting as padding and two large columns for creating some input fields let's also hide the gridlines on the whole worksheet for a cleaner look and then create multiple input fields for the project title the project manager and potentially some additional information like for example project priority we select all of these labels make them bold decrease the font size and also add some indent and a light gray border and then these cells are the actual input fields which we highlight by adding a light green fill and a similar font size and indent formatting let's enter some example values here the first two are simple text fields and the project priority field is a good opportunity to quickly demonstrate how to set up a drop down list as one form of data validation for a controlled list of input values beautiful for now we will reference the project title and project manager directly in the gantt worksheet so let's replace this title with the input value from the settings worksheet and then let's jump over there add a green label and an absolute cell reference to the project manager input field just like the project manager we also want to display the project time span which includes the project start and end date as well as the project duration for now we only gonna enter an example date value for the project start date and some placeholder values for the project end date and duration but later in this tutorial we're going to transform this into a dynamically computed time span for now we only need the project start date as we need it to set up the timeline values which basically means that we reference it as the initial date in the timeline and of course make sure it has a clean date formatting and then for the subsequent date in the timeline we make use of the workday function as we want to entirely focus on actual workdays the workday function returns a date which is a specified number of work days after a given start date so for the start date we simply reference the initial date in the timeline and as we just want to get the next workday here we enter 1 for the number of days and then copy over the date formatting we can then fill all the subsequent cells in the timeline here with the exact same formula as we have used relative cell references that looks pretty good then in the dark blue row below we want to display the initial weekday letter of the respective date for that we make use of the text function which is perfectly suited to apply a specified text pattern to a date so let's reference the date in the cells above and specify the pattern as ddd which returns the initial three weekday letters as we are only interested in the first letter we're going to wrap this formula with the left function to cut off the first letter from the left let's just decrease the font size and add this formula to all the subsequent cells and that perfectly demonstrates that the timeline dates really only cover the default work days which are monday to friday amazing as the final step of this initial setup i want to dynamically structure the timeline and chart area based on weeks so i need to create a visual separation between the weeks that still is correctly displayed even if the timeline is shifted to the left or right for that let's create a dynamic name reference first that dynamically references the date cells in the timeline we select the initial timeline date cell go to the formulas tab and click on define name the name of this reference will be simply date we set the scope to this worksheet only so this name reference will only be valid in the scanned worksheet and then we click into the reference down below which currently is an absolute cell reference with a dollar sign in front of both the column and the row by removing this dollar sign in front of the column letter this name will now dynamically reference the date cell in whatever column it is called from while the row 10 is fixed let me demonstrate that when we reference the name date from this cell it references the date cell in the same column and when we jump over here and reference the name date from this cell it references the date cell of this column in a similar manner we can also create a name that always references the next state in the timeline all you have to know is the reference you define in the formula below will always be relative to the cell that you had selected at the moment when you have opened the define name window that means if we now not only remove the dollar sign in front of the column letter but also change the column letter from x to y this name now always references the date cell in the next column a quick test shows date references the same column date while next date references the next column state we can now define a simple logical test we check if the weekday of date is greater than the weekday of next date with the simple workday focus setup that we have in our timeline this expression should always be true or when multiplied with 1 it should be 1 for all the cells for which the date in the timeline is a friday and once we add that formula to all the cells in this row this proves to be true so now we can make use of this expression to dynamically structure the timeline and gantt area using a conditional formatting rule we select the option use formula to determine which cells to format and pass the formula that we have just created to identify all the cells for which we want to have a light gray right border that's all we need we confirm and as you see not only does this beautifully add a weak base structuring to this area but it also works no matter what a start date is and it would even work if you decided to define custom weekend days or to consider holidays in your gantt chart now let's focus on the basic item details before we get into setting these up let's just make this row here really small to use it as a separator between the header and content and in addition let's also set the font size for the whole input area to 10. one of the most crucial input columns is the type selection as this will decide whether the item is a stage a task or a milestone as we want the user to be able to select from these options let's create a drop down list by opening the data validation window selecting list and then manually entering the three values s for stage t for task and m for milestone that way we can now quickly select any of these potential type values and to make this drop down selection available in each row we use the auto fill handle and simply drag it down let's set the text alignment to center and now we are ready to enter some example items here so we quickly set up one stage that is called planning with a few task items and a final milestone and then a second stage called implementation with a similar inner stage structure as we need to use the values in the type column in some follow-up calculations let's make that easy to reference by creating a dynamic name reference called type we set the scope to this worksheet only and this time unlike for the date name reference we gonna remove the dollar sign in front of the row number in order to make the row dynamically change to whatever row this type name is referenced in any formula we're going to use this value in will now be much easier to build and to understand let's use this to add a row-based visual structure to the gantt worksheet that dynamically adjusts based on the item type selected in each row at first we select the whole gantt range and add a new conditional formatting rule that is applied in every cell for which the type value in its row equals s that means we want to particularly highlight every stage row by setting the font style to bold and adding a mid-gray border at the top and bottom you see this is instantly applied and beautifully highlights every row for which we select stage as the type to even further emphasize the fact that all the tasks and milestones are sub-items of the stage we also want their names automatically indented for that we create an additional conditional formatting rule for this specific column this time we want to automatically format all cells for which the type is either t or m and to create the indented format we need to go to the number tab jump straight to the custom category and here comes a pretty cool trick because you can represent a text value in a cell by using the add symbol and then we can simply add some space characters in front of that to create the indention as you see this works beautifully and now we have a clear visualization of the hierarchical structure of our items on top of all that a powerful way to visualize the difference between stages and their sub items is the indicator color column we can quickly set up the default color indicators by adding two conditional formatting rules to this column one that's applied whenever type equals s in that case we want to have that darker gray as the fill color let's just make this a bit tighter and then we add another rule that's applied whenever the type is t or m and that one will add a lighter gray fill color beautiful for an even cleaner formatting you can remove the border formatting for this particular column and as the last part of the basic item details we also want to set up another really simple column the column that allows you to highlight issues here the input selection will be pretty simple we're going to set the text alignment to center and then create a drop down list that contains this one symbol that way you don't have to type in anything manually here you can easily select this symbol for any item that has an issue and once that issue is solved just use the delete key to remove it again simple as that now these were the basic item details for which we only had to set up either static drop down selection or simple input fields for the role and team member column however we gonna set up a really advanced smart and scalable role and team management system that will allow us to manage up to eight project roles and an unlimited amount of team members in the settings area so let's jump over to the settings worksheet and create a new section for the project role type management which will require a total of 5 columns let's change the header to a different color for example this beautiful green and add the same gray border lines that we already used for the project detail section then we adjust the size of these columns to have a right and left padding and a bit more space for the input columns since we want to create a list in here we need column headers for which we can simply copy over one of these labels and change the text the first column will be for the role name and i prefer the text to be aligned to the middle and center then we can just duplicate this using the autofill function and change the other headers to code which is the column to put in a shortcode that represents the role and the hash symbol for the column in which we will be automatically counting the number of team members assigned to this specific role the role and code column are simple text input columns so let's give them a light green background and as the count column will be a calculated column we just set the background to a light gray i guess it's time to add some example roles in here the number of input rows is intentionally limited to eight for two good reasons first of all the key idea of these roles is to group your single team members into functional groups in order to remove complexity and get a better overview in case you have a large team and the second reason is for a good visualization of different roles with different colors it is required to have a limited size of the color palette in order to keep them somehow distinguishable the great advantage of this approach is because we will do the coloring on this roll type level with each row able to have an unlimited number of team members assigned we could potentially add an unlimited number of team members to the whole project that's the reason why the project team management section will contain a much larger list the initial input column will be for the team member name the second column for the role assignment and then the third calculated column for automatically displaying the role shortcode so as i said we could potentially make this list as large as we want but for now i'm going to make it roughly 30 rows so that it still fits onto the screen eventually to close the section at the bottom let's add these gray border lines the name column just requires simple text input but for the assigned role column we now want to grab all the defined roles from the other section and make them available in a drop down list the important thing here is we want this drop down selection to always display only exactly those values that are defined but not the empty cell values to create such a dynamic range reference that grows and shrinks based on the number of non-empty cells in a range we can make use of the offset function for those of you who are not familiar with this powerful function i quickly demonstrated here the offset function always returns a range that starts at a starting reference then we could add a row or column offset which we don't need in our case but we need to define the height dynamically by counting the number of non-empty items in the maximum possible range and for that we can use the count a function as you see this perfectly returns a dynamic array with exactly all those non-empty values and once i add another entry the array grows accordingly and when i remove it the array shrinks again we can now use this formula directly to define a data validation drop-down list so let's open the data validation window select list and then for the source we use the offset function with a starting reference then skipping the next two arguments and then defining the height as the number of non-empty items in that range as you see exactly those five roles defined are now selectable here in every row when we remove an entry the drop-down selection adjusts accordingly which is simply amazing eventually for the code column here we want to automatically display the respective shortcode based on the selected role for such a task excel offers a wide range of lookup functions but the go to formula that i prefer and recommend is the good old index match for those of you unfamiliar with this formula the index function requires you to select a range from which you want a value to be returned and then to allocate a specific value in this one column range the second required input is a row number and in order to get the correct row number we can use the match function which simply looks up a lookup value in our case that is the assigned role in a lookup array which is the roll column here and it has to be an exact match so the match function returns the correct row number for the index function which then returns the correct short code simple as that now let's see what happens once we add this formula to the row below we get an n a error as no roll is selected here now as this doesn't look too pretty let's catch this arrow by wrapping the formula with the if and a function which allows us to return an alternative value in case this arrow occurs now we can add it to all the other rows and also add some additional team members you see the correct code instantly appears once we assign one of the defined roles that is amazing the final column that we have not taken care of so far is the column to automatically count the number of team members assigned to each role and for this one we first check if the roll cell is non-empty because otherwise there's obviously nothing to count and in case it is non-empty we use the countif function to count the number of values in that role range that equals the role defined in this list otherwise we simply return an empty text string we add this formula to all rows and instantly have an overview about the role distribution whenever we now add a team member and assign a role this count is updated correctly which is great eventually to give both these lists a more list-like appearance i like to add some white separating horizontal border lines between the cells that looks pretty good and now we are ready to jump straight back to the gantt worksheet where we're going to make use of these smart lists over here we now want to be able to select a team member via a dynamic drop down list and then automatically display the according role right next to it let's add this team member drop down list by applying the same exact technique that we have used just a minute before so again to create a dynamic range reference we enter the offset function with the first cell in the name column of the team member section then we skip the next two arguments and then use the count a function to count the number of non-empty cells in the whole team member name column now we can easily assign the defined team members to these tasks and milestone items and in order to automatically display their respective role we only need to look up the selected name in the project team member list and return the role shortcode for this let's first set up a dynamic name reference for the team member just as we did before for the type call it team member scope is this worksheet and to make it dynamic we gonna remove the dollar sign again and then to look up the respective row we define an index match formula that is wrapped in an if n a function the return array is the shortcode column of the project team member list and to get the correct row we apply the match function to look up the team member in this name column as an exact match in case this returns an n a error let's just return an empty string again and once we add this formula to all the rows using autofill we see that this perfectly works let's just decrease the font size and change the color to a gray tone and also add some indent we can even increase the size of this column a little bit and now this is an almost perfect implementation of this team member and role assignment there's only one last thing that i want to add to this feature to make it fully complete for this i jump back to the settings worksheet and i see at first we need to delete one column here to make the gantt button come back to its original place as we obviously have added one additional column in the process okay now the thing i want to add is a user-friendly way of removing team members from the project which might happen from time to time removing a team member like wylin here is no problem but what if the team member you deleted here is currently assigned to an item in the gantt worksheet well you see the role is empty because the lookup results in an n a error but the name is still written in the cell so i think it is very helpful to visually indicate that this team member is no longer part of the team and the item potentially has to be reassigned so my idea here is to add a conditional formatting rule that checks if a match function lookup of that name in the name list returns an n a error using the sna function and if that is the case we change the font color to a warning red exactly like this so now when we add this team member back to the list there is no warning and once this team member is removed again we get the indication that we might need to reassign this item amazing now that we have set up the full item details section let's start implementing the basic manual planning with independent start or end dates the most crucial columns for the actual plan will be these two calculated columns for the start and end date because all the date related visualization in the chart area will be based on these two columns the reason why the start and end date need to always be calculated is that we're going to have different input options as previously mentioned the calculation of the start and end date will always be based on two inputs one input is the number of required workdays and the second input is either an independent start date an independent end date or a dynamic start or end date that is dependent on another item of these two calculated start and end date columns one will always be defined directly from the date section and then the other one will be calculated based on that and the defined number of workdays before we start setting this up let's make sure that all the date columns including the base plan columns have the same clean date format i prefer this custom date format and for the workday column we make sure that the content is aligned to the center let's start with setting up the simplest of all options which is providing an independent start date and the number of required workdays in order to make these cell values easy to reference in other formulas we define dynamic name references for the independent start date always keep in mind to remove the dollar sign in front of the row number and then we do the same for the work days but this time we not only make it dynamic but we also want to make sure that this name reference will always have a default value of 1 in case the cell is empty we can easily do that by returning the maximum of the cell value and 1. this has two big advantages first of all it is guaranteed that an item will become instantly visible once either a start or end date is provided even before the number of workdays is entered and second we don't need to actively enter a workday number for milestones as we will need to reference the calculated plan start and plan n for the gantt chart visualization let's also add dynamic name references for these i explicitly call them plan start and plan end as these will be the ones that will always represent the actual plan in one constant place all right the formula for the plan start in this scenario is pretty simple we first check if the independent start date is non-empty and if that is the case we return the independent start date otherwise an empty string so this is the date directly taken from the input section while the plan end date has to be calculated based on the plan start and the work days in a similar manner we also check if int start is non-empty because only then the plan start is also not empty and then we're gonna make use of the workday function put in the plan start as start date and add the work days minus one so in this example nine workdays to this plant start the reason why we have reduced this number by one is that the planned start date is already one of these 10 work days so the plan end date is only nine workdays later the alternative value in case int start is empty is again simply an empty string let's do a quick check if the calculation is correct by looking at the timeline as you see the 10th to the 21st of may are exactly a 10 workday time span so it works correctly when we take a closer look at this formula there are two crucial things we always have to keep in mind first every time we calculate one of these plan dates by adding or subtracting the workdays from the other date it is crucial that we gonna reference the other calculated plan date and not the input date like int start in this case directly because otherwise we gonna end up creating circular references at some point and the second aspect to consider as both these formulas will get a lot more complex over time and also going to reuse certain calculations it is advisable to transform all sub-calculations in here into what i call named calculations what that means is that instead of entering these sub-calculations directly into the formula itself we gonna create names for them in the name manager and only have to reference the name in the bigger formula for the same exact result that not only makes the bigger formulas easier to read and the is reusable but also allows us to manage and potentially adjust each sub-calculation at one central place in the name manager now let's use this to manually set up the schedule for the tasks and milestone of this stage as you see as soon as we have entered the independent start date the plan start and plan and are instantly displayed as the workday's value is set to 1 by default and for the milestone we only need to enter the independent start date and can leave the workday cell empty because we want the milestone to start and end on one particular date perfect with this example schedule set up we can now create the default visualization in the chart area right below the timeline we have all the information prepared that we need to decide if an item is within its plan in a given cell because we now know what the plan start is what the plan end is and what a respective date in the timeline is these name references work correctly from every cell in this chart area no matter which one you take to figure out if a cell should display something or not all we need to do is check if the date is within the planned time span so if the date is greater equal to plan start and smaller equal the plan end this formula returns either true or false and for demonstration purposes let's display that as either a 1 or a 0 by multiplying it with 1. we can use the autofill handle to add this formula to the whole row and as you see this displays a 1 in each cell belonging to the defined plan time span when we add this to all the rows we can verify it works perfectly for all the defined items amazing for this formula again it makes sense to transform it into a named calculation so let's copy it and create a new name that is called item in plan set the scope to this worksheet and paste the formula it is simple as that now we can replace this formula and have one short expression that works in every cell of this gantt chart area and that is all we need to implement the default visualization one big goal of this template is to have the stages the tasks and milestones all differently visualized and while the stages and tasks can be perfectly visualized using a cell color fill without any written cell content we want the milestone to display an actual milestone symbol for that purpose i have prepared two unicode icons this one for an open and this one for a completed milestone and for now this symbol for the open milestone has to be somehow written into the cell so let's copy over this unicode icon and start building the milestone visualization as an in-cell formula in the formula we only have to check two conditions that have to be true first the type has to be a milestone and the item has to be in plan in this cell if that's the case we just print this milestone symbol otherwise an empty text string let's add this formula to the full range we instantly see the milestone symbol is printed on the correct day and in addition we're going to align the cell content to the middle and center and slightly increase the font size perfect for the stages and tasks on the other hand we want to create gantt bars by filling the respective cell backgrounds with some color and for that we can directly jump into the conditional formatting rule manager and create two new rules let's create a default rule for the stages just like for the milestones this formula checks if the type is a stage and if the item is in plan in the respective cell if both these conditions are true we want to add this darker gray background fill color to the cell let's apply this rule and you can see the stage is now perfectly visualized as a dark grey bar so let's move this rule down in the rule order which simply means that this rule is now applied after the two other rules and then we duplicate this rule to create a similar rule with a lighter gray for the tasks once we apply this rule both the stages and tasks are now beautifully visualized the only thing left now is we also want a similar gray coloring for the milestone symbol so let's jump back to the conditional formatting rules manager duplicate one of these two rules then change the type condition to milestone and this time we don't want to control the cell fill so we clear that first instead we jump to the font tab and change the font color to this darker gray i decided to make this milestone symbol color match the stage color to better stand out and once applied now all these type visualizations are in perfect harmony beautiful now before we close this conditional rules formatting manager i need to show you two concepts in here that are crucial in terms of worksheet efficiency at first always add a check in this stop if through checkbox if a rule and all the following rules are mutually exclusive what that means is in this case if the stage coloring rule here is applied because its conditions are true then the two following rules don't have to be checked because they cannot be true at the same time that will save you a lot of computational resources especially with a larger number of rules in here and the second concept somehow builds upon this stop if true feature and is even more crucial when we take a look at these default coloring rules we see that each of these does the same item in plan check that is super inefficient as we do the same condition check over and over again and to avoid this we can add one additional rule that does this item in plan check once at the beginning and only if the cell passes this test we continue with looking at the following rules that's why i call this a stopper rule because what we do in this new rule we gonna check the opposite of the condition that we want to be true so in this case we check if not item and plan we don't need any formatting here just click ok place it right on top of these coloring rules and then activate that stop if true checkbox so now if the item is not in plan in a cell the following rule conditions won't be checked because it is not required and this stopper rule now allows us to simplify the coloring rules by removing the item in plan condition and that leaves us with the type check here and now this whole setup is super efficient and we are perfectly prepared for scaling this with some additional coloring rules later great now that we have set up the default visualization let's take a look at the other manual scheduling option which is instead of entering an independent start date and doing a forward scheduling entering an independent end date and doing a backward scheduling let's reproduce the same exact time span for this stage by entering the end date that is calculated in the plan and cell at the moment which is the 21st of may at the moment the plan start and plan and formulas are only able to work with the independent start date input and that's why they now show nothing but an empty cell and the visualization is also gone but we're gonna change that in a second first we're gonna create a dynamic name reference for the end and date and then jump straight into these formulas this time we start by modifying the plan and formula at first instead of returning an empty string if ind start is empty we now continue with the second if statement that checks if end end is non-empty because if that is the case we want to directly get the value of end end and only otherwise we're going to return an empty text string that works like a charm so let's also update the formula in the other rows of that column and as you see this time the plan start has to be calculated backwards based on the plan end so let's add the same logic here first checking if end end is non-empty and if that is the case use the workday function put in the plan end as the start date of that calculation and then add the negative number of workdays so we multiply the workdays by -1 and since the end date is already one of these 10 workdays we have to add one again to make it only go back nine workdays and otherwise if int end is empty we just return the empty text string we now have successfully reproduced the exact same time span that we had before so again let's take that sub calculation and create a new named calculation that we call plan start calculation let's update the other rows in the plan start column and test if it works in the other rows just as fine and as you see we can easily recreate these time spans by providing only independent end dates that's amazing we have intentionally implemented both these formulas for the plan and and plan start using a hierarchical logic that means these formulas are written in a way that the int start value will always override the end and value so once we enter an int start value the in-end value will just be ignored that is really important as it makes sure that there is no confusion which value is taken if multiple input dates are provided at once however at the moment this logical hierarchy is not instantly obvious when only looking at the in start and end end columns so what we do to make this super intuitive is we're gonna add a conditional formatting rule to the end and column that simply checks if int start is non-empty because if that is true then the end end value is basically inactive not used and we indicate that by giving it a mid-gray font color there we go now when i enter an in-start date the respective end end date is automatically grayed out and i instantly know which one of both is currently active let's overwrite the stage and task items with the original in start dates leave the milestone defined via the end and date and then we quickly set up a plan for the second stage with the stage and the first task using a forward scheduling and the other two tasks being backward scheduled here you can perfectly see how the bar now grows backwards as soon as we set the number of workdays to a number that is bigger than 1 same for the next task and for the milestone again it is enough to only provide a date great now it's the perfect time to show you how you can quickly automate the stage calculations and visualization based on its items as we have no intention to override the calculated plan start and plan and formulas the clean approach here is to work with some simple formulas in the input section for that i have prepared two standardized placeholder formulas that are super easy to use all you need to do is copy this formula into the end start cell of a stage and then replace this placeholder with the planned start range of the items in that stage and in the same manner we can dynamically calculate the number of workdays using this placeholder formula which references the plan start and then calculates the net workdays between this plan start and the maximum of these items plan and dates we are now free to change any of these tasks or milestone schedules the stage timespan will always be automatically updated that is amazing but i think the usage of formulas in these input cells somehow makes it necessary to highlight an input cell whenever a formula is used so for that let's add a new conditional formatting rule to that int start column that checks if the cell contains a formula let's make the cell reference fully relative and the formatting i think that makes sense in this case is this strong blue font color because this is a color that we are commonly familiar with from links for example from websites or documents and in a way these formulas link to other cells let's also include the workdays column into this rule and that makes these formula inputs clearly distinguishable from regular input once we have used such an auto stage formula in one stage we can simply copy it over to another stage since the range reference is relative it just perfectly covers all these included items directly in case you have a different number of items in that other stage all you need to do is adjust this referenced range with your mouse and you are good to go and the same for the autostage workday formula what is really amazing about this general setup we can now collapse the date input columns and have the work days as key information still visible and now whenever we adjust the number of required workdays for a task there is no need to manually update the stage workdays as they are updated automatically as this autostage workday's calculation is not simply adding up all the workdays of its items but actually computing the maximum time span of all items it is fully able to handle whenever items overlap within that time span eventually while for the auto stage workdays it makes sense to display the actual number i think for the auto stage int start value we can make it even more obvious that this is not a real in-start date by adding an additional rule that only focuses on this column and is also limited to stage rows only so whenever a cell is in a stage row and is a formula instead of displaying the date itself a really cool trick is to just override it with a text value so no matter what the date is it will display the text auto let's set the text alignment to center and i think that is a beautiful and intuitive setup this manual planning with independent start or end dates is pretty helpful but it is nothing compared to what we're gonna build now because now we're gonna add the core feature of this template which is an intuitive and super powerful dependency engine that allows you to make your project plan fully dynamic we're gonna implement all four possible types of dependency connections that you can have between items so this step will transform the scan chart from a good looking project visualization tool into a professional project planning tool this right here is the section that will allow you to quickly create all kinds of dependency connections between items we gonna build up the whole dependency logic based on the second task p2 now instead of relying on one of these manually defined independent dates we gonna make that task dependent on task p1 so let's clear all these inputs for the subsequent items remove the independent end dates and since we want to make this task p2 dependent on the task p1 we're also going to remove the independent start date for this one the workdays can just stay in there as we're going to use them in the same way as we did before let's also temporarily move the timeline one week to the right so that not only the forward forward-looking dependencies but also the backward-looking dependencies are instantly visualized once they are implemented the key idea for making an item dependent on another item is that you will have to link to the other item's id this obviously requires us to create a reliable id system in advance and for that we start by defining a dynamic name reference for the cells in this column call it id set a scope and as usual remove the dollar sign in front of the row number as we don't want to manually enter any ids we somehow need them to be automatically created and self-sustaining in case we insert or delete a certain row that means we want to have an id system in which each id dynamically represents the position of its item's row in the whole list of items as a unique number the straightforward solution of simply entering and autofilling a sequence of numbers directly is not sufficient in that case because as soon as we insert a row and use the fill down option we get duplicates in here and would need to manually update all the subsequent ids again in the same manner setting the first id to 1 and then for all subsequent ids just referencing the previous id and incrementing it by 1 is also not fully working as the id below the inserted row unfortunately keeps referencing the same id in addition both of these very simple solutions would get absolutely destroyed as soon as you delete the initial row so we have to create something that is way more robust and self-sustaining the key for achieving that is to give each cell all the information about the previous values in its column we can easily create a named range that we call brief call range short for previous column range and define it as a range starting from the header cell and ending at the cell right above at the moment this is a fixed range but removing the dollar sign in front of the last cell's row gonna make this a range that will dynamically grow and shrink depending on the cell from which it is referenced furthermore as we might make use of this in other columns as well let's also remove the dollar signs in front of the column's letter whenever you have defined a name that refers to a range or cell you can easily check what it is currently referring to by opening the name manager selecting the defined name and clicking into the formula below you see with this cell selected it refers to the exact range that we need the reason why we included the header in here is because that ensures that even if we insert a row on top of the initial row it will become part of that range and not be excluded now we can use that to enter the formula that will dynamically create the correct id for each row in this formula we first need to check if this is the initial row in the list of items that's the case whenever the row of the cell itself equals the row of the header plus two two because we have the separator row in between so the initial row will always be two cells below the header cell if that is the case the id has to be one and for all the subsequent cells for which that is not the case we simply compute the maximum of the previous column range and increment it by one simple as that let's drag the autofill handle down to add it to all rows in this column and that looks good so far but the actual power of this becomes visible once we insert a row and fill the values down you see all the id values get perfectly updated to correctly represent their new position in the list even if i would insert a row above the first row and use the fill up option it is also correctly updated so that gives us an automated and robust id system that is not instantly destroyed whenever we forget to manually update it now we can jump over to the dependencies section and make task p2 linked to the id of task p1 this link should always be created by actually referencing the respective id cell in order to make sure that the linked id number is correctly updated in case the other item's id changes due to an inserted or deleted row to help you instantly see if the linked id is an actual cell reference and not just a fixed number typed in we make use of the conditional formatting rule that gives the cell content a blue font color in case it contains a formula we increase the range by adding a comma and selecting this column and now this reference id is colored blue which tells us it links to another cell a super powerful advantage of referencing the other item's id is that it couldn't be easier to make this task dependent on a different item because you can simply click into the cell and drag the reference to another id okay now that we have found a way to create a robust link to another item's id let's make this cell value dynamically referenceable as dot id where the d stands for dependent and the initial thing that we want to do with this id is to extract some fundamental information about the other item the first information we are interested in is the position of the other item so is it positioned above or below this item's row this information can be really helpful as an indication for either a forward scheduling dependency or a backward scheduling dependency and to indicate that visually we're going to make use of these two unicode arrow icons the formula for this is quite straightforward at first we check if dot id is non-empty and if that is the case we only have to check if d.id is smaller than this item's id which means it's positioned above this item and we insert a placeholder for arrow up and alternatively in case d id is greater than this item's id the other item is obviously positioned below this item so here we want to display the arrow down and as fallback option we just return an empty text string let's jump to the unicode icons and copy over the arrow up paste it to replace the placeholder and then we do the same for the arrow down perfect let's also decrease the font size set the text alignment to center and adjust the size of the column and that gives us a beautiful indicator for the position of the linked item in the next column we want to automatically display the type and name of the linked item this is a classic look up problem so we can use the match function to look up did in the id column to get the correct row number and then use the index function to return the respective value from either this or this column as we're going to need the row number of the other item at multiple occasions let's just start with the match part and make it a named calculation for the lookup value we pass d.id then for the lookup array we can pass the whole column b as an absolute reference with dollar signs and we need to have an exact match this returns 16 in this example which indeed is the row number of the linked item so let's copy that formula and transform it into a named calculation and we call d.row perfect for extracting the other item's type we use the index function to take a look at the column e and return the value from the row calculated in d row that seems to work correctly let's change the id to reference the milestone that also works like a charm so we can save this calculation as d.type and for the linked item's name it's basically the same approach let's call it d.name paste the formula we just used and replace column e with column f now we have both these information about the other item available as a named calculation let's also decrease the font size a bit and change the font color of all these information to this dark ray to show these are secondary lookup information and now we can create a concatenated string that contains d type in brackets followed by d name amazing but once we add this to some rows that have no did reference this will result in an n a error so in order to catch this and potential other arrows let's wrap this expression in an if arrow function one additional aspect to consider is that the other item's name could potentially be way longer like this for example so the extracted text in the d item column would extend into the subsequent columns and to avoid it i recommend to adjust this formula instead of just directly printing the full name we first gonna check if the length of this name exceeds a certain threshold for example 7 characters and in case it really exceeds that threshold we're going to apply the left function to extract the first threshold number of characters so in that case 7 followed by some dots and otherwise we can simply print the full name so now this long name is cut off at exactly that threshold and once we increase the column size a little bit everything now perfectly fits in and as soon as that threshold is not longer exceeded the dots disappear correctly and we have the full name displayed perfect let's do a final test for the whole d item output by changing the referenced id once again and the information we get are exactly those that we expect to see great let's move on to the heart of the dependency engine which is the selection of the type of dependency connection we want to have in the column d.con we add a drop down list that lets us select one of four different connection types finish to start start to start start to finish and finish to finish the first letter always refers to the other item and the second letter to this item so finish to start means that this item starts the next day after the other item has finished so the plan start date of this item is determined by the plan and date of the other item and as this makes the plan start tied to another date an increasing number of required workdays will make this task grow forward start to start means that this item starts the same day the other item starts so this item's plan start is tied to the other items plans start which also means it grows forward with an increasing number of required workdays start to finish now means that this item has to finish right before the other item starts so this time the plan end date of this item is tied to the other item's planned start date which means an increase in required workdays makes this item grow backwards and eventually finish to finish means that this item finishes the same day the other item finishes so this item's plan end date is tied to the other item's plan and date and with an increasing number of required workdays it also grows backwards a great way to provide some basic information and instructions for an input column like this is to select the header cell then open the data validation window without adding an actual data validation what we are interested in instead is the input message tab which allows us to enter an informative message that is displayed whenever this cell is selected i'll just enter some basic information about the four dependency connection types and now this information is available here whenever i need it the column next to this will allow us to define a lag by which we want this item to be shifted to the right in case we have a positive value or to the left with a negative value let's set the text alignment to center for both these columns adjust the column size and create a dynamic name reference for both these columns we call the first one d.con and make the row relative and then we do the same for d.lag with this we now have all the values and information available that we need to make this dependency logic part of the plan start and plan and formula i hope you remember the hierarchical logic that i talked about earlier which basically was that both the plan start and plan and formula always start by taking a look at the int dot start value and only if that cell is empty they consider the int and value and only if that cell is also empty they will now consider the dependency section and do the date calculations based on what is in here so let's start with the plan start formula and replace this empty string with a completely new expression what we need to do first is to check if d.id is non-empty otherwise there is nothing that could be calculated here and in case it is non-empty then we can take a look at what connection type is selected for the plan start formula we are only interested in those dependency connection types that will make the start date tied to the other item the first one of these is the classic finish to start dependency in that case you want to do the respective calculation for which we simply enter a placeholder for now and alternatively if the selected dependency connection type is start to start we want to do the respective start to start calculation in case it is one of the other two dependency connection types then we know that the plan and date will be tied to the other item state and we need to do the backward calculation based on the plan and date and the number of required work days and guess what this is something that we have already defined as the plan start calculation what a great coincidence now we only have to close these brackets for the last two if statements then provide a fallback value in case the id is empty and eventually close that bracket as well for now with finish to start or start to start selected here the respective placeholder appears correctly and once we select one of the other two types we get this value arrow because right now the formula assumes that the plan and date is already available which is not the case yet to catch these arrows in general for this huge formula let's wrap it inside an if error statement just to make sure it will always display an empty string in such a case for the plan and formula we're gonna implement the same exact pattern instead of returning an empty string here we first go to check if d.id is non-empty and if that is the case we now check if d.con equals one of the other two dependency connection types which are start to finish and alternatively finish to finish otherwise in case it is one of the other two values we need to calculate the plan end based on the plan start and the number of required workdays which is already covered in the plan and calculation let's close both these if statements enter a fallback value in case d.id is empty and we can also directly wrap the whole formula in an if arrow function to catch potential errors the basic logic of when to do which calculation is now implemented when switching between different values of decon the placeholders perfectly show us which of the plans start or plan and is the one that is calculated based on the other item this allows us to proceed with replacing these placeholder calculations with some actual calculations and for the beginning we're going to remove this lag value here to avoid any confusion for the finish to start calculation we need to set the plan start date to the workday after the plan and date of the other item so we want this date to be one workday after this one plus a potential lag the workday function is the one that allows us to do exactly this calculation we only need to somehow grab this value but as we already know the row of the other item we can simply return this value using the index function by entering column t as the return array and d row as the row with this we now have a start date defined in the workday function and only need to specify the number of workdays we want to add to this date as for this finish to start dependency connection we are looking for the next workday plus a potential lag we're going to enter 1 plus d lag and close the workday statement at first glance it seems to perfectly work the plan start is correctly calculated and thus also the plan end can be calculated based on the plan start and the work days when we add a leg to this dependency connection the second task is shifted to the right or to the left accordingly and the most important aspect when we change the duration of the first task the plan dates of the second task are updated correctly so that the second task always starts right after the first one has finished perfect for the start to start calculation we can now simply copy over that formula as we only need to do some tiny adjustments for this one we want both items to start together so this time instead of grabbing the other item's plan and date we now need to grab the other item's planned start date from column s and for the day's argument we're going to remove the one because by default it now is not the next but the same day that we want to have and we only want to shift it by a potentially given lag when we now make this a start to start dependency connection both items perfectly start together changing the other items duration in this example has no impact but changing the other item's independent start date makes both perfectly move together for a better maintainability and less complexity in this huge formula let's transform both these expressions into named calculations the first one will be called fs calculation and the second one accordingly ss calculation and that way we have now transformed this huge amount of calculation logic and functionality into a relatively small and understandable set of commands let's jump right over to the plan and formula because here we're now going to create similar expressions for these two dependency connection types for the start to finish calculation we now want to have the plan and date of this item one day before the planned start of the other item plus a potential lag of course that means we're gonna make use of the workday function again and as the date to start with we're gonna grab the other items plan start by applying the index function to column s with d row as the returned row and since we want to calculate the work day right before that date we add -1 this time plus the potential value in d lag let's close that statement hit enter and change the dependency connection to start to finish and it works like a charm when we change the start date of the other item both items perfectly stay tied together through this connection and also adding a positive or a negative lag works just like it should eventually for the finish to finish dependency connection type let's copy over this expression and you probably already know the adjustments we're gonna make we're gonna remove the -1 and grab the other item's date from column t instead of column s as we need the plan and date that way both items now will finish together on the same day but potentially shifted by a given lag this also works as it should now both plan and dates are dynamically tied together adding a lag value also produces the wanted result and now we can transform both these new expressions in the plan and formula into two named calculations the first one called sf calculation and the other one accordingly ff calculation let's insert these named calculations instead of the full formulas and do a final checkup if everything still works correctly that looks pretty good in this whole dependency logic there is only one thing that we haven't considered so far and that is what if no dependency connection type is selected so the cell here is empty with the given setup that will result in a circular reference which is where we get this warning and the reason for this circular reference is both the plan start and plan and formulas try to calculate their dates based on each other as neither of them is able to find one of their two relevant dependency connection types in there so the plan start calculation and the plan and calculation are executed at the same time the good news are we can easily avoid this by setting a default value in case d.con is empty in my opinion finish to start is the most intuitive choice here and the quickest way to make this the default value is by saying apply the finish to start calculation for the plan start either if decon equals fs or d.con equals nothing so an empty string that way finish to start is always the default choice in case no different value is selected we now have successfully built the whole dependency logic so we can decrease the height of this formula bar up here again update all the cells in the plant start and plan and column with the newly built formulas do the same for the d item columns and since finish to start is the default value in the dependency calculation i suggest to just paste it as an actual value for all cells by default and then make these columns behind the did column invisible as long as no did is referenced we can achieve that with one simple conditional formatting rule this rule will check if did is empty and in that case sets the font color to white that way nothing is visible as long as the id is empty and both the d-item and the d-con value magically appear once we put a reference into the d-id column in my opinion that makes the user experience way better eventually let's remember the logical hierarchy for which input date to consider that we have built into both the plan start and plan and formula with these updated versions of both formulas the dependency functionality has the last place in this hierarchy which means it is overwritten by both the end and date and in start date just like the end end date is overwritten by the end start date for the dependency connection this means it becomes inactive if at least one of the in start or end date is non-empty so let's add the same graying out conditional formatting to the dependency section as we already did for the independent end date column this rule checks if at least one of the instart or in date is non-empty in case that is true we set the font color to this mid-gray and we can see as this rule is currently executed as the top rule it makes the decon values appear again so let's just move it below this rule here that ensures the font color is white if the id is empty we click apply again and a problem is solved great let's do some example planning with the dependency engine for all the items that we have set up so far we gonna make this task dependent on the preceding task with a simple finish to start dependency and see how the task has one workday by default and as soon as we enter a number higher than that it grows to the right in a similar manner we could now make the milestone dependent on that last task in that stage by referencing id4 but in cases like that where we simply want to have consecutive item dependencies there's a beautiful shortcut you can use which is the autofill function simply select the dot id of the preceding item and then drag this auto fill handle down and since this is a relative reference in there this auto filled reference now links to the next id which is 4. for milestones i generally prefer a finish to finish dependency as a milestone is often logically tied to the successful completion of one or multiple tasks for the second stage we can start by copying over the autostage formulas because we already know the amount of items in that stage in case that stage had a different number of items we could easily just click into the formula and adjust the referenced range to cover all of the items and the same for the auto stage workday formula using this stage i want to demonstrate how you can easily make a stage dependent on another stage for example with a finish to start dependency connection all you have to do is to determine which one is the initial item in the second stage then link this item to the first stage and make the subsequent items directly or indirectly dependent on the initial item for consecutive dependencies within the stage we only have to link the second item to the first one and then we can use the autofill function to create consecutive dependencies for the last two items let's change the milestones dependency connection type to finish to finish and enter the number of required workdays for the tasks see how easy that was both these stages are now tied together and every change that impacts the first stages plan and date like for example an increased duration or change in dependency structure within that stage will make the second stage automatically adjust its schedule accordingly now that was a classic forward scheduling dependency connection between stages because we have made the start of the second stage dependent on the other stage we can also create a backward scheduling dependency for these stages by making the plan and date of the second stage dependent on the other stage for example as a finish to finish dependency that would mean the stage and date is fixed and every increase in duration of this stage would make the stage grow backwards to achieve that we have to reverse the dependency logic in that second stage initially we determine a final item in that stage in this case this would most likely be the milestone then link this final item to the other stage and make all the other items of that stage directly or indirectly dependent on that one so we update these dependencies accordingly by clicking into the did cell of the milestone and dragging it to the id of the first stage now this is connected to the first stage with the finish to finish dependency then we make that last task dependent on that milestone with the finish to finish dependency and for the upper two tasks we can use the autofill function again and only have to update the dependency connection type to start to finish for both of them whenever we increase the duration of these items the whole stage now grows backwards and we need to start earlier of course once the plan end date of the first stage is moved to the right into the future that will now move both the planned start and plan and of the second stage back to the right again for the sake of completeness let's also transform this back into his stage finish to start dependency connection by updating the initial item's did to link to the first stage again make the subsequent items consecutively dependent on that initial one and change all the dependency connection types for the tasks back to finish to start as you see even these fundamental changes in the dependency structure of that schedule can be done within a few seconds that's amazing let's also take a quick look at how to extend the stage with additional items let's add two additional tasks and one milestone to the second stage the first thing you should do in case you have applied the auto stage formulas click into them and adjust the referenced range to also cover these new items same for the workdays to enable the stage to cover the full stage time span for simple consecutive relationships we use the autofill handle then change the milestone dependency connection to finish to finish and enter the number of required workdays for the tasks be aware that this is just a simple example you are totally free to always transform this into a way more complex dependency structure for example we can make this task i4 start together with the initial tasks simply by updating the linked id and switching to a start to start dependency connection now a change in duration of these two tasks not necessarily has an impact on the overall stage duration as these tasks run in parallel now or let's say we want both of these milestones to finish at the same day and figure out what does that implicate for the required start date of the last two tasks let's make this milestone dependent on the other one and these two tasks now dependent on that second milestone with a finish to finish and for this one a start to finish dependency given this setup once we increase the number of workdays here we now just have to start earlier and now this even has an impact on the stage time span all these examples are only a small fraction of all the possible dependency structures you can build so the opportunities with this dependency engine feature are basically endless as quick as we had added these three items within seconds we can also delete them you see the ids on the left perfectly update the reference ranges of the auto stage formulas also do not require a manual update and as we have just deleted a few of these content rows and now only have 16 left at the moment we can easily add new ones anytime just by selecting the last row and using the auto fill feature as soon as we have more potential rows in this gantt chart than fitting on the screen we would need to scroll down in order to see all of them but instead of applying the scrolling to the whole worksheet i recommend to scroll to the top once then select the initial row of the gantt area go to the view tab and click on freeze panes to freeze the header including the separator row now only the gantt area below the header is moved when we scroll and that way it is way more user friendly before we continue let's use this additional space for adding two more stages to this gantt chart we call this one rollout prep and make it dependent on the second stage with a finish to finish dependency connection so this stage is affected by changes in both the second and the first stage and then we add another stage called roll out that will just start right after the rollout prep stage has finished it is simply amazing how quickly the setup of a fully dynamic project plan can be done with this feature as it's incredibly intuitive fast and easy to use and another amazing thing is whenever you're done setting up the dependency structure for your project plan you can just collapse all these columns with one click and that gives you just a clean and dense overview of the most crucial information about the scheduled plan and you still have full control over the workload so adjusting the number of required workdays for an item can be done even in this dense view and anytime you want to make a change in the underlying dependency structure simply reopen the section and you are good to go right at the moment all these stages perfectly summarize the duration and time span of their respective tasks and milestones but now we want to implement a feature that goes one level above that and dynamically keeps track of and visualizes the whole project time span for that let's start by collapsing this extended planning section as we need to focus on the plan start and plan and column given the values in these two columns we're gonna compute the project time span and duration in workdays for which we have already prepared these placeholders up there the project time span is nothing else but the minimum and maximum of these dates in here but we not only gonna display these date values up there but also going to visualize the time span in the timeline section let's create a named calculation called project start that dynamically determines the minimum start date of any item in our project the range that we pass for this starts right at the header row and in order to give this project some space to grow we go down to row 1000 and then we set up a similar named calculation for the project and which is calculated as the maximum of the plan and column for the project duration in workdays we're going to create an additional named calculation that is based on the net workdays function and calculates the work days between the project start and project end that enables us to replace this manual start date up here with the dynamically calculated project start then replace this placeholder with the project end let's align this one to the center and the project end to the left and then for the project duration we're going to insert a concatenated expression with an opening bracket then the dynamically calculated project duration and a closing bracket it is important to mention here that this project duration in workdays is based on the project time span and not necessarily equal to the actual workload especially if you have tasks and stages that run in parallel we can now make use of these information to beautifully visualize this project time span in the timeline area let's make use of this row that is right on top of the current timeline and add a conditional formatting rule to these cells we want to format all cells for which the date in the timeline is greater equal the project start and smaller equal the project end all the cells for which this condition is true shall get this neat green background fill that seems to work pretty good but it still doesn't look perfect because the row still has this standard height and that is a bit over the top so we need to make this row much tighter let's remove this temporary unicode icon description and set a row height to 6 pixels let's add a similar conditional formatting rule to the actual timeline date rows the formula for this rule is exactly the same but for this one we just make the background a bit darker by changing the fill to this slightly darker gray tone both these conditional formattings in combination result in a pretty refined visualization of the project time span with any change in the schedule may it be a change in task durations or a change in a dependency structure this visualization smoothly updates to always capture the full project time span and in addition this feature becomes even more valuable whenever we have a really large project because once we scroll down and the upper stages are not visible anymore we still see the overall project time span in relation to the currently visible items and that is pretty amazing however now that this project time span is dynamically computed there are still two weak points that we have to take care of the first weak point becomes apparent when we take a closer look at this initial timeline date cell because that one is referencing this dynamic project start date and that makes us run into a lot of errors in case no dates are provided in a planned start column because now the project start date value is just 0. to make sure this doesn't happen let's make the timeline start date more robust than a simple reference to the project start date and create a perfectly tailored name calculation called timeline plan start we set the scope to this worksheet and what we want to do in this calculation is we first check if project start equals zero because then we want to use today as the fallback value for the timeline start and in order to make sure that it always shows the beginning of the week of today we simply subtract the weekday number of today with an encoding that returns 0 for monday up until 6 for sunday that way in case today is a wednesday for example this expression subtracts 2 and returns to monday of the week and only in case project start is not zero then we return this project start value minus its weekday number with the same encoding then let's replace this original formula in this first timeline date cell with the defined named calculation at the moment this project start date is the 10th of may which is a monday so this is the first date displayed in the timeline when we change this project start date to the 7th of may which is a friday you can see that the timeline start calculation makes sure that the timeline starts on the monday of that week and last but not least in case no dates are provided at all for the project items the timeline uses the week of today as the fallback value to start from so that doesn't result in an error anymore and we have successfully eliminated that weak point the second weak point becomes apparent whenever we use the auto stage formulas to dynamically calculate the stage time span but the dates for the items of that stage are not available yet that will temporarily make this int start value and thus the planned start and plan and values of that stage 0 which by itself would not be a problem if it wasn't taken into account when calculating the project start date which is now incorrectly zero as well and that causes the timeline to use the fallback value as the starting date for actually no good reason but no worries this issue can be solved with a tiny adjustment in the plan start formula this initial if statement makes sure that the value in the in-start cell is only taken into account in case the cell is not empty now we need to change that into making sure that this value is not only non-empty but that it is also greater than zero this tiny adjustment fully eliminates the second weak point the only visible impact that we will have is that the autostage workday function now temporarily shows an error but this is actually a good way of telling us that we need to define a schedule for at least one of the items in that stage before any stage calculation can be done here let's update the plan start formula for all the rows we can perfectly see how the project start date and the timeline show the correct values now so we can eventually redefine the dependencies for the items in the rollout prep stage amazing at this state of the template we have successfully implemented the whole planning logic this allows us to now focus on the visualization aspect of the template and set up an incredibly powerful auto coloring engine that will let us switch between four different and fully automated color modes let's start by hiding this planning section and setting up the drop down menu for the color mode selection we merge these 6 cells set the text alignment to the middle set the font style to size 10 and bold and add some gray borders the label of this drop-down selection will have the text color by and right below is where we want to put the actual drop down menu so let's also merge the 6 cells align it to the middle set the size to 9 this time and the background fill to this light color to add a drop down selection we jump straight to the data tab and open the data validation window allow a list of values and statically define the list of possible color modes as default project structure team roles and issues now any of these color modes can be selected with one click and in order to make the selected value easily usable in other formulas we can define a static cell reference up here and call this color by the gray coloring that we currently have in place for the indicator colors and the gantt chart area is what our default color mode looks like it's clean and simple and the rules to define this coloring are simply based on the type of item however now that we want to implement way more complex and fully automated color modes we need to approach this differently and introduce a system that lets us represent different coloring logics but at the same time keeps the formulas in the conditional formatting rules pretty short easy to understand and easy to scale that's why we gonna build a system of numeric color codes with a color code generating formula that will cover the whole logic and the numeric color codes returned by this formula for each row will then be used to create conditional formatting rules for both the color indicator section and a chart area we make these color codes referencable by setting up a dynamic name reference for these cells for now this color code name simply references the cell content but later we will just insert the final formula directly into the formula fields down here to make this a named calculation as we want all the coloring including the default colors be based on color codes let's take a quick look at how these default indicator colorings are currently created well first let's move this one up and set the stop if true check mark for an increased efficiency you see for this default coloring we have two rules in place that simply check what the type of item is at the same time we have three different rules for the chart area as unlike for the indicator coloring the task and milestone items require a different visualization for our new color code system we will move these type checks over to the color code formula and let the result be represented by some numeric codes as we have three different rules here for the default coloring we obviously gonna need three different default color code numbers here for generating the default color codes we start by making sure that the type value in row is non-empty and then we use the switch statement to return different numbers based on which item type is selected we keep it simple and return a 1 in case it's a stage for tasks we're going to return a 2 and for milestones it will be a 3. otherwise in case type is empty we simply return a -1 let's add this formula to all the rows and that gives us a numeric encoding based on which item type is selected let's open the conditional formatting manager for the indicator color section and change the two coloring rules for this one we replace this logical test with color code equals one and for this one these two conditions will be replaced with color code equals two or three the indicator coloring still works as before since the logic behind it hasn't changed at all so let's do the same for the chart area we select the chart range open the conditional formatting manager and replace this original logical test with a color code check for stages it's one again for tasks two and for the milestones simply three and again it keeps working fine based on these color codes since the final version of this color code generating formula will be huge and we also gonna reuse this expression for the default color codes let's copy and transform it into a named calculation called color code default now in the general color code formula we can reference this expression simply by its name that makes even more sense since for this whole formula we now want to consider the selected color mode and build up the respective formula pattern to act based on the color mode selected in order to find out what the currently selected color mode is we make use of the switch function enter this color by reference as the expression to look at and in case the selected mode is project structure we want to execute all the respective calculations for which we enter a placeholder value for now in this switch statement we can now just use the color code default calculation as the default return value then if team rules is selected as the color mode we want to do a different color code calculation and eventually in case issues is selected another calculation will be done let's update all the other cells with this adjusted formula now when we select a different color mode all the color code cells display the respective placeholder value for now let's build a color code generating expression for the project structure mode first for this mode we're going to make use of eight main colors from our color palette and we want each stage with all its items to have a new individual main color similar to the default color mode we're also going to need different color codes within a stage in order to represent the different types of items with different shades of the same color as we need to differentiate between stages let's start by introducing an automated stage id system in this column this will automatically assign the same id to all the items of one stage so a 1 to all the items of the first stage a 2 for the second stage and so on to make this stage id easy to reference let's add a dynamic name reference called stage id and then start building the formula at first we need to make sure that type is non-empty because a non-defined item cannot belong to any stage in case a type value is available we do the same check we already did for the main id in order to figure out if the item is the first one in the whole list so in case the row of this cell is two rows below the header cell we know this has to be the first stage and return a one for all subsequent items we then want to continue with the same stage id until we reach another stage item for which we then increment the stage id number by 1. that means if type equals s we compute the maximum id number in the previous column range and then just add 1 since it's a new stage if type is not equal as the item is either a task or a milestone so in that case we just return the maximum id number from the previous column range let's add this formula to all the rows and you can see how it perfectly creates consecutive stage ids while all the empty rows get a -1 let's center these values and decrease their font size then we gonna test the functionality by making the second stage a task instead you see the stage id perfectly adapts to the new situation by including all consecutive items into the first stage while making sure that the originally third stage has now assigned the stage id2 and of course this also works the other way around when we make this task in the second stage a stage this now becomes stage number three while the subsequent stage has now the stage id4 now that we have the stage id defined let's jump right into the color code formula to replace this placeholder right here with some color code generating expressions the first thing that we gonna make sure is in case the stage id is -1 the color code should also be -1 which means no color will be displayed at all however in case we have an actual stage id available we gonna build a three-digit color code that we gonna concatenate as a text first and then transform into a number later for this project structure mode the first digit for the main color is determined by the stage id so we use the stage id and concatenate it with a text string that will be returned from a switch expression based on the selected item type we either gonna add an o1 for a stage item for a task item it will be o2 and for a milestone item o3 after that this concatenated text string will then be transformed into a number by wrapping it in the number value function and eventually we add a closing bracket for this if statement after adding this formula to all the rows we now have a clean numeric encoding for the project structure that beautifully represents both the stage and the item type within each stage but there is one limitation that we haven't considered yet because we have a limited amount of main colors in our color palette more precisely we only want to use these eight main colors and their different shades but at the same time we also want to be able to create an unlimited amount of stages that are all beautifully colored to be differentiable from the stages immediately around them so the logical solution for this project structure mode is to reuse the color palette over and over again so that after using all these eight main colors for the first eight stages stage number nine will just get the same colors as the first stage then stage number 10 the same as the second stage and so on for the color code that means instead of using the stage id directly we're going to use the so-called modulo operation which returns the remainder from dividing the stage id by a divisor this divisor in our case has to be 8 so this function will basically partitionate the stage id into a part that is divisible by 8 and a remainder that is not divisible by 8 which will then be returned so in case the stage id is 1 it will return 1 and if it's 9 it will take out 8 because that's divisible by 8 and then return the remainder which is also 1. the only special case that doesn't work in our favor is whenever the stage id is fully divisible by 8 because then it returns the remainder of 0. and in that specific case we actually want to have an 8 as the first digit of the color code so let's expand this by an if statement that takes care of the special case perfect let's update all the rows and now let's artificially increase the stage id here to show you the effect of this little adjustment you see the color codes for all these stages are correctly generated and as soon as stage 8 is passed it starts all over again with the initial one in the color code for stage 9 and continues like this infinitely for all the subsequent stages great let's open the conditional formatting manager for the indicator color section and create two additional rules for the first group of color codes that have a one as their first digit for that we can simply duplicate these two rules that we already have in place for the default coloring and simply adjust the color codes that these rules are looking for for the stage items of the first stage the code is 101 the main color for this first stage in general will be this blue and since it's a stage item let's choose this stronger main shade of the blue in a similar manner we adjust the formula for the task and milestone items by changing this to 102 and 103 and this time selecting this lighter shade of this blue color once applied you can already see how we now have some indicator colors displayed let's not forget to set a check mark and yeah there we have these beautiful color indicators for the first stage of course we need to set up the respective rules in the chart area as well so let's open the conditional formatting manager for d cells and just duplicate these three different rules that we have here as we have this powerful stopper rule in place that checks if an item is in plan within a given cell we only need to move these new coloring rules right below that stopper rule and then we can simply focus on the right choice of color and color codes the procedure now is pretty similar we adjust this color code to 101 and change the fill to this strong blue then we adjust this rule to look for color code 102 and change the fill to this light blue and eventually we have the separate rule for the milestone coloring set the color code to 103 and this time we change the font color to the strong blue and that's how easy it is to set up these corresponding coloring rules for one color code family switching back to the default color mode now and then back again perfectly demonstrates that all the items are still in place the only reason why the other stages are not visible yet is that we haven't set up the conditional formatting rules corresponding to their color codes for now let's outsource this expression into an own named calculation that we call color code project structure and then replace this expression in the main formula using that defined name before we set up the conditional formatting rules for the remaining color code families here we first going to implement the formula for the team roles color code generation that's because we want to make the team roles and project structure color mode use the same conditional formatting rules and thus also partly the same color codes in order to make the whole implementation more lightweight and less redundant for the team rolls color codes that means we will make use of the same color palette of 8 main colors but in this context the 8 main colors will represent these 8 team roles that can be defined in the settings worksheet as we have the team role shortcodes available in this column we can easily implement a corresponding role id encoding by looking up the role shortcode in the settings worksheet for that let's start by defining a dynamic name reference for this role column and also dynamic name reference for the role id column and then we use the match formula to look up the role in this array make sure to make this an absolute range reference with dollar signs and eventually enter a 0 to look for exact matches this statement will simply return the team role's position in the settings team rules list and we can directly use this position as the role id as you see if this roll cell is empty this will result in an n a error so let's add an if and a statement to catch that arrow and return a -1 instead then we can add this formula to the whole column and that gives us a simple but effective encoding of the assigned role let's just decrease the font size to 8 and align it to the center and a quick visual check tells us yes it seems like each role is adequately represented by its position in the settings worksheet role type section i still want to make one additional modification to this formula and you might agree or not agree with me on this one in my opinion it makes sense to limit the team member assignment to tasks and milestones only because these are always connected to real actions while stages tend to be more abstract concepts mainly used to better structure the whole project and to group items together so my preferred approach at least for this color related visualization here is to only return a role id for tasks and milestones while for stages we simply going to return a -1 with this modification you can still assign a team member to a stage if you like but it won't be considered by the auto coloring engine okay let's use this role id to replace this placeholder with some meaningful computations the first thing we do here is checking if the role id equals -1 you have to know this -1 can have multiple reasons either there is no item defined in the respective row at all or there is an item that has no team member assigned or it is a stage item which means it will be -1 by default for all these cases we want to apply the default coloring as fallback option because this ensures that even if there will be no team role related coloring the item is still visualized and in case we have an actual role id available then we build a three digit color code just like we did for the project structure mode the number value function again helps us to transform a concatenated text string into a numeric value and within this function we concatenate the role id as the initial digit and then use the switch statement to determine the last two digits based on the item type as there's no need to visualize the hierarchical differences between a stage and its items we don't need to use different shades of a color this time and thus we simply use the strong shade version of each main color for both the tasks and the milestones that means in case the type is a task we generate a color code ending on o1 these are the ending digits that we used for the stage items in the project structure mode which means we can make use of the exact same conditional formatting rules for the indicator color and gantt char bar visualization and for the milestones we have to introduce a new color code ending o4 as so far we have no color code ending that corresponds to both a strong color shade for the indicator color and a strong font color for the gantt chart area let's close the statement update all the color code cells in this column and take a quick look at what we got now the initial thing we can notice is that all items that have no team member and role assigned are visualized with the default gray coloring so that is working correctly then when looking at the visible three digit color codes 101 which corresponds to the tasks assigned to the project management role is the only one for which we actually have defined conditional formatting rules so far it is also obvious that with the team roles color mode all these three digit color codes will only end on either o1 or o4 so let's include this o4 ending into the conditional formatting rules that we already have in place as we want to have a strong color shade here we're going to make this rule an or statement that also considers 104 and for the gantt chart area we just include this color code into the rule that we have already set up for the milestones in particular so let's make this an or statement that also includes color code 104 with this setup now all project management items are automatically visualized in this corresponding strong blue and to show you that this doesn't have any negative impact on the functionality of the project structure mode we quickly switch over here and it still works perfectly fine the only thing left for both modes is now the setup of the conditional formatting rules for all the remaining three digit color codes that have two to eight as their starting digit before we do that we gonna clean up this main color code formula by putting the team roles calculation into a separate named calculation we call it color code team roles just paste it click ok and replace this expression with the respective name after updating the formula for all the rows we are now ready to set up the conditional formatting rules for all the remaining three digit color codes that we have in here fortunately that process will be pretty straight forward for the indicator column we gonna duplicate these two specific rules seven times and then stack them on top of each other then we leave the first set of these two rules as they are and jump straight to the second set for which we now gonna adjust the color codes and color formatting this will be for the color codes 201 and 204 and the color of choice here is this red tone and in the same manner for the color codes 202 and 203 we want to have this light shade version of that red color to be displayed we continue this process until we have covered all the color codes up to the code starting with an 8. for each new pair of rules we gonna select a new main color from which we picked the strong and light shade versions i'm going to speed up the process here a little bit to save you some time once this process is finished you can see how the indicator column beautifully displays all the required colors no matter what the stage number is and when we switch to the team roles color mode all assigned roles now have the corresponding indicator color displayed of course we also have to go through this process for the chart area so let's select this range open the conditional formatting rule manager and start replicating this set of rules until we have eight of these sets stacked on top of each other i know this takes a little while but it will be well worth the effort once we have eight sets let's jump straight to the second set start with the first rule change the initial digit to 2 and then select the corresponding color it will always be the strong shade version of the respective color for the o1 ending then for the o2 ending it will be the light shade version and the strong shade font color version for the o3 and o4 ending of the color code once we apply these updated rules we can already see in the background that the second stage now became fully visible again in this beautiful colorized design and that should give us enough motivation to do this six more times for the remaining color codes the only important thing you have to make sure here is that the color families used for these rules are consistent between the indicator column and the chart area great at this point we have completed the most difficult part of this auto coloring engine it has been some effort but the result we can now look at is well worth it in project structure mode the whole project structure is now beautifully visualized with different color families for each stage any update to the project structure is immediately reflected in both the indicator and the chart section and with this lightweight design that reuses the color palette over and over again every stage is colored differently than the stages that are immediately around it and you can add as many stages as you want that is really amazing then for the team rolls color mode we were able to reuse the same exact conditional formatting rules but with a completely different logic that represents each assigned role with its own individual color let's see what happens when we change the role of ylin from finance to marketing this update is immediately reflected with a new color for the marketing role as we have the default coloring in place as a fallback option for all the items that have no team member assigned each item is visible at any time and once we decide to assign a team member for example here it will simply update the color with a smooth transition amazing that means by now we are able to choose between the simple and less colorful default color mode the project structure color mode or the team roles color mode with one single click eventually let's implement the last color mode that will allow you to highlight items for which an issue has arisen since the corresponding formula will be based on this issue column let's make the cells dynamically referenceable as issue then copy that unicode issue symbol and start replacing this placeholder with a formula we're going to use an if statement to instantly check if issue equals the symbol and if that's the case we want to generate one of two potential color codes as for the other two advanced color modes we have only used one to eight as the initial digit we now simply create two color codes that are 901 and 902 901 will be used whenever the type is stage or task and as we need a separate rule for the font formatting of milestone symbols we create a separate code 902 for these it is simple as that and of course we still want to display all the other items without issues so again we just use the color code default calculation as the fallback option we close the statement update all the rows and the only thing left to do is adding the corresponding conditional formatting rules for the indicator column we only need one simple rule that we can use for both color codes so in case the color code equals 901 or 902 we want to have a warning red fill here and then only for the chart area we need to make the distinction between the item types so we duplicate these two rules here the first one will be for the color code 901 and simply fill the cell with this warning red while the second rule is meant to be for the milestone symbols so color code 902 and a warning red font style perfect now whenever we switch between the default and the issues color mode the red color smoothly covers the underlying default colors and that is simply beautiful finally we only need to take care of the main color code formula at first we transform this expression into a named calculation called color code issues replace this part accordingly quickly test if all the color modes still work as required that looks fine and now instead of having this color code formula written in each individual cell of this column and dynamically reference each of these cells with the color code name we can simply take this formula and put it directly in here to make this a straight named calculation for that however i recommend to remove all the line breaks and spacing otherwise it will be difficult to read in the name manager and then we simply copy and paste it right here so the cell content in column a is no longer needed and we can remove it resize this column to get back the original worksheet design minimize this formula bar and finally go through all the color modes one more time simply beautiful let's move on to the next amazing feature that will help you to take a snapshot of your actual plan save it as the base plan and then compare it to the changes that you make to your actual plan over time the crucial idea of this feature is that once you have built up your schedule with all the dependencies you can document the date of the recent changes you made then jump over to the base plan section by unhiding these two columns and just copy over the actual plan as plain values let's decrease the font size for this and change the font color to this dark gray and once you have taken that snapshot you should also document the date up there so that you always know when the snapshot was taken now when time goes by and you decide to adjust your actual plan like for example postponing the project start by one week and this decision has been made on the 7th of may you now have both the original base plan and the actual plan side by side and you also know exactly how much time has passed between these two versions of the plan through the day documentation at the top our goal now is to implement an option that allows us to visually display and compare both the base plan and the actual plan in the chart area let's start by adding another drop down menu that we call display and that will have two list items to select from the first option is simply called plan and refers to only showing the actual plan while the second item will be called plan versus base which means we want to have both plans visualized in the chart area at the same time let's select plan for now because that is the default option that is already in place here then we create a static name for these cells and call it simply display and of course we also gonna add some dynamic name references for both the date columns of the base plan so for the base start as always we're going to remove the dollar sign here and of course the base end based on this base start and base end we now want to create a named calculation that does exactly the same for the base plan like the item in plan calculation already does for the actual plan to remind you the item in plan calculation simply checks for every cell in the chart area if the date value in the timeline in the respective column is in between the plan start and plan and date and then returns either true or false and for the base plan we can set up a similar named calculation so let's copy this create a new name call it item in base set the scope to this worksheet paste the formula and now we simply overwrite this with base start and base end whenever we select the plan was a space option up here we now want to use this item in base calculation to additionally display the original base plan in here for the milestone symbols that we currently generate with the formula from within the cells this means here we now have to display an additional milestone symbol basically one week earlier let's adjust this formula to do exactly that instead of simply checking if type equals m and item in plan we now want to do this type check initially and after that we add a second if statement to figure out if the item isn't planned in the current cell if that is the case we gonna return this milestone symbol independently from the current display mode because we want the actual plan to always be displayed in both modes the special aspect to consider now is in case we are in plan versus base mode we only want to display an additional symbol for the base plan if it is not on the same day as the one from the actual plan so it makes sense to only consider the selected display mode if item in plan returns false in that case we only want to print a milestone symbol for the base plan if the plan was as bait mode is selected and the item in base calculation returns true otherwise we simply return an empty text string just like we do in case the type does not equal m perfect let's add this formula to all the cells in the chart area and you see we now have the base plan milestones displayed in addition to the actual planned milestones and we can control whether to hide or display them via this drop down selection up there right at the moment these milestone symbols of the base plan have the default dark blue font color but we're going to change that into a font color that is less standing out let's open the conditional formatting manager and create a new rule that will directly target these symbols only to only be applied to the base plan milestone symbols the formula for this rule has to test multiple conditions which are display equals plan was a space item in base has to be true and of course in case the base and actual plan milestone are on the same date we don't want this rule to be applied and just apply the actual plan coloring that means we have to put the item in plan into a not statement to reverse it and eventually of course type has to be m for the formatting we choose the super light gray which is even lighter than the default coloring ray of the actual plan so no matter which color mode you have selected these two should always be distinguishable once we confirm that that looks pretty amazing from a visual standpoint it is instantly clear which of both is the active actual plan and which one is the snapshot base plan for visualizing the stages and tasks of the base plan we will only need to create one conditional formatting rule let's select the range again open the conditional formatting manager here we can see we should move this milestone rule a bit down right on top of the actual plan stopper rule then we can just duplicate it and adjust this formula for the stages and tasks of the base plan for these we also need to make sure that the item in base calculation returns true but unlike for the milestones we want the stages and tasks of the base plan overlay the actual plan stages and taskbars if necessary you will see how that can be done in the formatting options in a second for the formula though this means we don't have to make sure that item in plan is false so we can just throw that part out and eventually we gonna change this last condition into an or statement that checks if type is either stage or task for the formatting we change the font color back to automatic go to the fill tab and the reason why we can have both the base plan and the actual plan visualized in the same cell is that we're going to use pattern fills for the base plan that means if a cell in the chart area is part of both the base plan and the actual plan the pattern fill of the base plan will simply overlay the full cell fill of the actual plan as the color for this pattern fill i recommend to use this dark grey because that allows us to use this not so heavy point pattern that says 25 gray and it will be still perfectly visible let's click ok and there we have the beautiful but still subtle visualization of the base plan compared against the actual plan even for the darker colors you can easily identify the part where both the base plan and the actual plan overlay as it is either darker or brighter than the rest of the item depending on the specific color palette that you use i recommend to play around with the pattern style a bit in order to make these overlay parts easy to differentiate from the rest with the drop down selection up here we now have full control over the visibility of this base plan which allows us to keep the complexity in the chart area low and only display the base plan when we really need it for a seamless user experience i also want to make sure that the timeline will automatically adjust its initial date to perfectly show the full extent of both the actual and the base plan right now this initial timeline start date is only based on the actual plan for that reason the base plan which starts on the 7th of may is not fully visible here in order to change that let's start by adding a named calculation that computes the project start date according to the base plan which is simply the minimum value within the base start column using the start date of the base plan we can then set up another calculation similar to the timeline plan start calculation we're going to call this timeline base start and all this does is taking the raw project base start and calculating the start of the respective week by subtracting its weekday number which will be encoded as a 0 for monday and a 6 for sunday so this will return the monday of the week of the base plan start now that we have both the timeline plan start and the timeline based out defined we can now make the initial timeline date dependent on the display mode in case display equals plan was a space then we have to make sure that the timeline base start is greater than zero if that is true we're going to return the earlier of both the timeline base start and the timeline plan start that makes sure that no matter if the base plan is ahead or behind the actual plan the timeline will always optimally adjust in case we haven't taken a snapshot yet which means the timeline base start is not greater than zero the timeline plans that will be directly used even if the plan was a space mode is active and in case the selected display mode is not planned versus space then the timeline plan start will be used just like before once we hit enter you can instantly see how the timeline perfectly adjusts to show the full base plan the base plan starts on the 7th of may and the timeline starts right at the beginning of that particular week and once we change the display mode back to plan the timeline also adjusts accordingly and that way you don't have to actively manipulate the timeline view in order to have everything visible in case the base plan section is empty it directly uses the timeline plan start and in case the actual plan is not behind by the hat so starting earlier than the base plan this case is also perfectly covered this base plan visualization also works fully independent from the color modes so the only thing left here is to convert this timeline start formula into an own named calculation called timeline start let's replace this whole formula with the clean and simple timeline start calculation reference and that is it for this feature as the next step let's add an intuitive and partly automated way of tracking the progress and visualizing it in a good looking and easy to interpret way for this we have this one column left at the end of the input section and we begin with changing the numbers format to percentage that way we can enter the percentage of completion for each item and it automatically gets the percentage symbol assigned even during typing every percentage of completion that we have entered should then be reflected in the gantt chart accordingly given that we have activated the proquest visualization to activate and deactivate the proquest visualization let's add another drop down menu with the label show progress and yes or no as the selectable options to easily access the selected value in other formulas let's call this cell show progress and of course we also need this percentage completed value accessible as well so we create a dynamic name reference called percentage complete taking this percentage complete value we then want to visualize the corresponding number of completed workdays in the chart area so for this stage it would be 60 of 10 workdays which means we need to highlight the initial six workdays as completed to figure out for each cell in the chart area if it is part of this completed time span or not we need to set up a name calculation that is similar to the item and plan calculation so let's copy that formula create a new name that is called item in complete set the scope as usual and paste the formula as we want the proquest bar to start from the beginning of an item we can leave the first condition that makes sure that the date in that column is greater equal to plan start what we have to modify is the second condition because instead of just taking the plan end as the upper limit we want to dynamically compute the end of the completed time span starting from the planned start and then adding the equivalent number of completed workdays the function that is perfect for this is the workday function because it allows us to enter the plan start and then add the number of completed workdays to compute the number of completed workdays we simply multiply the percentage complete and the number of required workdays and since the plan started is already one of the days in the time span we have to subtract one from this product when looking at this first stage item this named calculation we have just set up should now return true for all these six highlighted cells so we can use it to set up conditional formatting rules for the progress visualization the first rule will be targeting the stage and task items and we want to highlight a cell as completed workday whenever we have selected yes in the show proquest drop-down selection and the column date is within the time span of completed workdays so item incomplete has to be true and since this rule is for stage and task items the type has to either equal s or t if all these conditions are met we want the cell to be filled with this dark blue color in the conditional formatting manager it is now crucial that this rule is placed somewhere on top of the other actual plan coloring rules because that makes sure that this dark blue progress fill will always cover and not be covered by all the subsequent rules for an increased efficiency don't forget to set a check mark at the stop if true option and then we click ok to take a look at the beautiful result as you can see the completed work days are now correctly displayed for the stage and task items in accordance with the given percentage values it is important to mention that the cell can only be either fully filled or not filled that means for a work day to be displayed as completed the full workday equivalent has to be completed by then for this task for example the second day will only be displayed as completed when we have surpassed the 67 threshold in a similar manner the percentage complete value has to be at least 50 for this task before any progress is visible now that we have taken care of the stage and task items let's focus on the progress visualization for the milestones which will be really special first we're going to create a conditional formatting rule for the coloring and for that we can simply duplicate the progress rule that we have just set up move it down and then adjust this last condition to type equals m for the formatting we don't want to have a cell fill but we want to set the font color to the same dark blue that's it so now as soon as that percentage value is set to 100 it's now colored as completed which is great but to make this even greater let's make the milestone symbol also transform into this finish flag icon to emphasize that we have successfully crossed the finish line so let's copy it and modify the symbol generating formula as we want to have the progress only visualize for the actual plan and not the base plan this is the only place we're going to do any modifications and instead of directly printing this milestone symbol we now gonna ask if show progress is set to yes and if maybe this milestone is already completed so if item incomplete if that is the case we print that beautiful flag symbol otherwise just a standard milestone symbol let's close that if statement and add this updated formula to the whole gantt chart range and there is the beautiful finish flag for this completed milestone amazing when we delete that 100 the symbol perfectly transforms back to the regular milestone symbol and putting it back in there will just make it re-transform of course this milestone transformation works for every single milestone and the whole progress visualization can be turned on and off up there so it is safe to say that the fundamental proquest tracking feature is completed by now and that gives us the great opportunity to take a closer look at how we can partly automate the progress tracking itself what makes most sense to automate here is the proquest tracking of the stages because for most use cases it makes sense to have the progress of the stage item linearly reflect the progress over all items or at least the tasks covered in that stage for that purpose i have prepared another auto stage placeholder formula up there this formula basically just computes the sum product of the workdays and the progress of all items belonging to this stage and then divides that by the sum of total work days over all items at this stage once applied the stage progress now automatically updates every time we change the progress value for these tasks once all the items in the stage are completed the stage is completed as well and that just saves you a lot of manual work to highlight the fact that the stage progress is linked to other cells we're going to use the same blue font color formatting that we already have set up for multiple other columns like the workdays column for example here we have that respective conditional formatting rule let's add this column to the relevant range by typing in a comma and selecting the column values perfect and now we instantly see okay this stage progress is automatically calculated and linking to other cells let's add this autostage progress formula to the other stages as well and keep in mind if you have a different number of items in a stage you have to adjust the reference ranges in that formula to make it work correctly in our example case here where all the stages have the same number of items we were able to automate the progress tracking of all these stages within a few seconds regarding the milestone items here there is one thing that you should be aware of with this auto stage progress formula you're going to create references to the workday and proquest cells directly and not to their dynamic name references which also means an empty workday cell like we have here for the milestone item will be counted as zero and not as one and thus not impact the calculated stage progress at least not until we put an actual one in there in my personal opinion that actually is a good thing because milestones are something that should not be measured in workdays and they are often tied to the completion of tasks anyway so i will leave the workday cells empty in case you want your milestones to contribute linearly to the stage progress all you need to do is just enter a one in that workday cell since i just mentioned that the completion of milestones might be tied to the completion of tasks let me quickly show you how that can be done correctly let's say that we want this milestone to be set to one hundred percent only in case that this task here is fully completed for that we can type in the formula one times that progress value equals one this whole expression will only return one or in other words one hundred percent in case the task completion is exactly one hundred percent in all other cases this will always return zero once we hit enter we see that the milestone progress is now highlighted in blue telling us that this links to another cell and with the task being at one hundred percent completion the milestone is completed as well but as soon as we only take away one percent here the milestone completion is set to zero percent of course this concept can also be extended to multiple tasks for example to make the milestone completion dependent on the completion of all task items in the stage we can check if the sum of these values equals the number of task items and now whenever any of these tasks is not completed anymore the milestone completion is immediately set back to zero i think this concept alone is something that makes this proquest tracking and visualization feature even more powerful at this point we have achieved a huge milestone as all the formulas and conditional formatting rules for visualizing stages tasks and milestones in any possible form are now completed the whole implementation is already pretty lightweight efficient and reactive yet there's still some improvement potential which you might recognize when we turn on and off the progress visualization the progress bars and flags appear and disappear pretty fast but there is still a tiny tiny lag of a few milliseconds which we can decrease a lot with a powerful trick and that trick i'm going to reveal to you right now one big reason why at the moment it takes a bit longer for this worksheet to update when we for example turn on the proquest visualization is that the visualization of the milestone symbols is a two-step process the first step in this process is to print the actual milestone symbols into the cell based on this in-cell formula and then in a second step we apply the conditional formatting rules to give them their respective color it is not possible to entirely eliminate this first step as the cells still need to have some content to display these unicode icons yet we are able to simplify the cell content by making the icon definition part of the respective conditional formatting rules to do that let's copy this default milestone icon open the conditional formatting manager and scroll straight to the bottom to start with the first milestone coloring rule at the moment this rule only defines the color for this milestone icon but when we go to the number tab and select the custom category we can apply the same trick we previously used for the auto stage start date instead of defining a number pattern we just paste in the milestone icon here at the moment the sample section shows nothing because to make this work we need to have some numeric value written into the cell which we will do in a second but for now let's click ok and we instantly see that this milestone symbol is now the default number formatting applied by this rule let's quickly adjust the formatting for all the other milestone rules in the same way this also includes the issues milestone rule and the one to display the base plan milestones only for the proquest milestone rule we gonna need to use the flag icon so let's copy this one reopen the conditional formatting rule manager and adjust the number format for this rule accordingly and that's it at the moment the milestone symbols are still generated by this formula in the cells but since the conditional formatting rules that we have built alongside this formula basically have inherited the full logic of when to display which symbol we can now simply remove this formula from all the cells and replace it with a random numeric value like for example zero as i told you before the cells have to contain some numeric value otherwise the number format of the conditional formatting rules cannot be applied to anything so we add 0 to all the cells in the chart area then we set the default number format for all these cells to an empty string to hide these zeros in all non-milestone positions and what's left are the milestone symbols that are purely generated from these conditional formatting rules for some reason when we generate the symbols that way they seem to require a bit more space so let's slightly decrease the font size to make them visible and there we go everything works just like before but now the cell content is as simple as one random number that means no additional calculation has to happen in these cells and the impact of this reduction of required calculations can be noticed immediately when we now turn this show progress option off and on the visual updates in the chart area happen almost immediately without the lag that we had before so it seems this optimization had a significant impact on the worksheet performance and efficiency great to perfectly complement the proquest tracking feature we now gonna introduce a feature that lets you either statically highlight a particular day visible in the timeline or dynamically highlight the current day to put the project progress into perspective for this date highlighting feature we want to have a drop-down selection that has all the dates in the timeline and in addition an option for today available so let's create a new drop down selection that has the label highlight then we give this drop down cell the static reference name highlight and change the background fill to a light green this time unlike for the other drop down selections up there we want this list to not contain fixed static values but to reference our timeline range in order to make it dynamically update and always display exactly those states that are visible as these date cells spread over multiple rows we get this warning alert because you can only reference either a single row or a single column but no worries we can manually adjust this to only reference the cells in row 10 as this will make all the values accessible just as well let's click ok and take a look at a dropdown selection we get as you see the full range of dates visible in the timeline from the 10th of may to the 12th of july is now selectable here let's select the 7th of june and set the number format to our preferred date style so far the drop down list lets us select a particular static date that won't change once selected however we also want to have an item in that list that dynamically represents the current date and the limitation of this data validation source definition is we cannot compose a list by combining a range reference and a manually entered value so the only option we have is to make the additional item part of that reference range at the moment we are referencing these items in row 10 so why not make use of this empty cell right here to add one additional value to the list let's just expand this list to include column w and then enter a placeholder text value for the dynamic date highlighting when we now open the drop down list today is listed as the primary item at the top of the list and that makes totally sense from a user experience perspective because most of the time we just want to dynamically highlight today and only occasionally we might want to highlight a particular static date in the chart okay to highlight the selected date in the chart area we gonna create a new conditional formatting rule that is able to work with both the dynamic and the static highlighting options we use an if statement to check if highlight equals today because we cannot use that text value directly to highlight the current date in that case we need to compare the date in the timeline with the today function which returns an actual date alternatively highlight has to be one of the static dates in which case we can directly compare the date and the highlight value while the static dates from the drop-down selection will always be one of the visible work day dates from the timeline the dynamic date of today might be on a non-working day like sunday and in such a case we want to highlight the next working day the next working day can be easily found by applying the workday function passing today minus one regular day as the start date and then adding one workday in case today is workday then this expression will have no impact as it will just jump back and forth to the same exact date but in case it is a non-working day like sunday it would jump back to saturday and then forward to the next workday which is probably a monday let's take a look at the formatting that we want to use to highlight a date we want all these cells that are in the same column as the selected date to have a dark blue right border with the dotted style because that will give us a continuous line that is positioned right at the end of the selected date then for the timeline date and weekday section up there we're going to add a similar conditional formatting rule that has the same formula so it will also be applied to the same selected date but exclusively to these upper cells the design we choose here is a dark blue fill and a bold and green font style let's adjust the brightness of this green tone for an improved contrast and after confirming we see that the selected date is now beautifully highlighted in the timeline section while the respective dotted line is positioned right at the end of that highlighted day with this setup we can now switch between any of the visible timeline dates or use the dynamic today option to always highlight the current day whenever the timeline values update for example with us activating the base plan comparison not only does the highlighted date correctly move with the timeline but also the dropdown selection is updated correctly to now offer us the newly visible timeline dates i have intentionally chosen this exact design for the date highlighting feature because it perfectly fits into the overall color and design theme it is visually not too dominant and the most important point is it perfectly complements the progress visualization through the matching dark blue color and the end of the date highlighting with that dotted line this design allows us to instantly see how many workdays we are behind with each item when compared to the selected date let's assume today is the 8th of june then we instantly recognize that everything behind that blue line that doesn't have the same dark blue color is still open but should be done by the end of the day in case today would be one day later we instantly see that we are a few work days behind with that stage long story short it is just an incredibly clean effective and easy to interpret visualization feature as the final step i want to show you how to add some easy to use scroll buttons to dynamically move the timeline to the right and left this will be especially helpful if you have a larger project that doesn't fully fit the screen at once the way scrolling for this timeline works is actually pretty simple to scroll to the right we only need to add a number of workdays to this initial timeline start date and as we want to control this number of added workdays using scroll buttons we need to put that value somewhere in the worksheet let's just use this cell right here put a zero in here for now give the cell a name reference called scroll increment and then add that value to the timeline start date via the workday function to now intuitively control this number let's go to the developer tab which you can activate in the excel settings in case it is not visible and then we insert the form control element called scroll bar put it right here and adjust its size so that only these scroll buttons are available here by focusing on the scroll buttons only the scrolling process is much more controllable and the element can just be placed at the top right corner while not requiring too much space to make these buttons now control the scroll increment number we simply have to link it to this cell then we set the maximum value to 50 let the incremental change at 1 for now and remove the 3d shading for a cleaner design that is all we need to make this timeline scroll to the right and left using our mouse only great you can perfectly see how the scroll increment number increases and decreases continuously as long as we keep the respective button pressed to increase the scroll speed we could either modify the formula in the initial timeline date cell or alternatively just right click and format these buttons to increase the value for the incremental change to 5 for example which basically makes the scrolling 5 times faster amazing eventually i recommend to hide the content of these two cells quickest way to do that is just setting the font color to white and at this point we have finally completed the creation process of this incredible ultimate excel gantt chart template what a great result to help you make the most out of this template let me give you some additional advice on the best way to add manage and even filter items in this template let's take a look at this four stage gantt chart the most common way of adding new items is just adding them at the bottom and if you know that you want to add multiple items make sure that you have one empty row available that is part of the gantt chart because then you can just easily select the whole row and use the auto fill function to add as many new rows to the scan chart as you want all the conditional formatting rules and drop down menus are automatically available for these new rows so you can instantly start adding new items in case you have accidentally used the last row and thus no free row is available in a scan chart just use the autofill function to create one additional row then delete all the values from the input columns and after that you can use it to add as many new rows as you wish in case you want to insert an item between other items for example let's assume we want to insert another task for this implementation stage i recommend to make use of the quick access toolbar up there this is the section in your excel application that by default includes these save undo and redo commands and most of you might be used to having this positioned right above the ribbon but especially for this template i prefer to have that quick access toolbar below that ribbon to make some helpful custom commands available with one click i have already added these four commands used to fill down fill up insert rows and filter and i can easily add additional ones that might be useful for this use case like deleting sheet rows simply by selecting it on the left side here clicking on add and potentially changing the order of these commands this is how you can quickly customize this toolbar to include your preferred commands and in our case improve the user experience significantly because now we no longer have to right click here in order to insert or delete a row but instead we can simply press this command to insert a row then use the fill down command to autofill the new row with formulas and values from above so that we only have to rename the task and potentially assign it to a different team member in case we don't want to have a consecutive dependency we can adjust this dependency id but in our case we rather change the milestone to be dependent on this inserted task instead so we drag this reference down to 10 and that's it in case we want to delete this task again we only have to select the row and use this delete sheet row command from the quick access toolbar and of course now we have to relink the milestone to be dependent on another task again in addition the way we have set up this gantt chart offers a lot of exciting dimensions to filter the items by for a quick and lightweight filtering i recommend to make use of the native filter function that you can easily include in your quick access toolbar to filter the gantt chart by a specific column just select the respective column including the column header then press the filter command which adds this filter arrow to the column header and allows you to do your filter selection for example we can easily create a high level view of the project by displaying stages only that gives us a compressed view of the full schedule and the general project progress removing the filter is as easy as pressing the filter command one more time since we have defined a stage id in an own column we can use the filter command to only display selected stages including their task and milestone items that allows us to have multiple stages that are actually placed far apart from each other in a condensed view another great idea is to filter by the assigned team role which allows you to break down the project not simply based on single team members but instead based on areas of responsibility which also points out the distribution of tasks between team members of the same role two other really interesting columns are these date columns because when applying a filter to one of these you can choose from a huge selection of specific date filters just to show you one of them let's filter down to all items starting after the 26th of may works like a charm and that is it for this tutorial if you want to download this ultimate excel gantt chart template it is available on excelfind.com the link for that is in the description i hope you enjoyed this tutorial and i'd be curious to know what additional features you would love to see in this gantt chart template let me know in the comments any constructive feedback and of course a thumbs up is appreciated and that being said i wish you a beautiful day and see you next time cheers

Channel: The Office Lab

Views: 116,015

Rating: 4.9680614 out of 5

Keywords: excel gantt chart, excel project plan, excel project management, how to create gantt chart in excel, excel project, excel gantt chart template, excel gantt chart download, excel gantt chart tutorial, ultimate excel gantt chart, excel project management tutorial, conditional formatting, drop down list excel, the office lab, excel template download, excel free course, excel dynamic project schedule, excel advanced formulas, how to create excel template, excel business

Id: OizqFlMtZLQ

Length: 198min 0sec (11880 seconds)

Published: Fri Aug 27 2021