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 used

project

planning tool and Excel allows you to quickly set up a simple Gantt chart, but you can also

create

a truly outstanding version with many advanced and interactive features making this a professional planning and

management

tool. of projects. Let's take a quick look at the final Excel template we're going to create today. Allows you to set up an advanced, 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 work days required and then manually entering a separate start or end date or making use of this template's main feature, a

dependency

engine

. fully implemented with this.

Dependency

engine

can make one element depend on another by creating a connection through an intuitive identification system. The columns next to the referenced ID instantly show all the critical information about the referenced item and what makes this feature so incredibly powerful. There are four possible dependencies. connection types to choose from, which are a direct end-to-start or start-to-start forward scheduling

dependency

or a start-to-finish or end-to-end backward scheduling dependency. Watch as both the stage bar and project time period instantly adapt to changes made along the way. This built dependency engine allows you to configure consecutive dependencies with different dependency connection types in a matter of seconds.

Any new tasks or milestone items you add below a stage are instantly assigned to that stage through an automated stage identification system in the same way you do. Quickly create a new stage with your own elements. Notice how the entire area and element names are automatically formatted and structured based on the selection you make. Which is really amazing with this dependency engine. You are not limited to creating dependencies within a stage. You can also make stages depend on other stages for a forward-facing stage dependency, for example you just have to make the initial element in your stage depend on another stage, then a set of standardized formulas allows you to perform the update of the stage based on its elements and finally you can make the other elements consecutively depend on that initial element, define the number of working days and ensure that the milestone finishes together with the previous task.

After that it will only take a few seconds to add another consecutive stage which will have the same direct scheduling dependencies with the stage start date linked to the other stage and thus making the time span grow in the future each time. which increases the number of working days required, in case you want to convert this to a backward scheduled stage dependency with the stage and date linked to the previous stage. you can simply reverse the dependency logic by rearranging the linked identifiers via drag and drop and updating the dependency connection types in the respective dropdown selections and now the more the number of working days increases, the earlier the stage should start.

You see, this dependency engine allows you to quickly configure and modify a completely dynamic project schedule and always guarantees that a change somewhere in the schedule will cause all dependent elements to be updated instantly. Also, you may have noticed that each stage is automatically colored with one color group per stage and a different color. shadows for the stage, tasks and milestone symbol, this is made possible by an automated coloring engine that dynamically creates and applies color codes to the background depending on the currently selected color mode, each time the color mode is selected. project structure color, colors are automatically assigned Based on the stage id and element type, the available color palette covers 8 different color groups that are applied in an infinite loop to ensure you can add an unlimited amount of stages.

The team roles color mode allows you to view the project based on the respective role that has been assigned to the project team members who are responsible for an item at this time only project manager c miller is available here, but once Once we open the settings area we get access to an incredibly powerful project role and team management system that this system enables. You can add an unlimited number of team members to your project and group them into up to eight roles that can be based on department of responsibilities 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, then you can add new team members and assign any of the roles you just defined on the left, the role assignment is automatically tracked in the count column of the role type section and once Once you have added your team members to the list, you can return 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 displayed with its own individual color on the Gantt chart, as this color is based exclusively on the project roles and not on individual team members, a team member can easily be replaced by another team member with the same role without any visual interruption.

Eventually, in problem color mode, each item that is marked with a problem symbol is highlighted in a warning red color and of course, you always have the option to turn off all colors by switching to color mode predetermined. Once you've set up your project schedule, you may want to track the adjustments you make to that schedule over time. You can document the last update date of the actual project plan and then, to retain a snapshot of that plan, open the base plan section. Taking a snapshot is as easy as copying the start and end date column as values ​​and then documenting the snapshot date and then the time. happens and you make changes to your actual plan, such as postponing the start of the project.

You now have the actual plan and the original base plan side by side and can easily visualize the comparison of both by changing the display mode to plan versus space. Comparing that way instantly reveals changed dates and change durations while making sure the timeline dates fit perfectly to display both timelines at the same time, of course this template also allows you to keep track of Stage Task and Milestone Completion Once you set Show Progress itself, the percentage completed is beautifully displayed as a filled bar for stages and tasks and as a completion flag symbol for completed milestones.

You can even conditionally complete milestones based on the completion of one or more tasks so that the milestone completes automatically. not before the linked task has reached 100 percent. Additionally, a standardized formula allows you to automatically update stage progress based on its elements. Eventually, this completion tracking feature is perfectly complemented by the date highlight option which allows you to dynamically highlight today and have items due before that line instantly grab your attention or alternatively you can statically highlight any of the dates that are visible on the timeline and the selectable timeline dates are immediately updated in the drop-down list each time you scroll across the entire Gantt chart area to the right. or left, which could be especially useful for longer projects.

The Complete Worksheet is simply an amazing tool for setting up completely dynamic and easy-to-manage protection programs. It was created with a focus on lightweight design, great user experience, and fast performance. Let's build the complete Excel Gantt Chart Template with all these amazing features step by step. The final template file is available for download at

excel

find.com. The link is in the description and now, without further ado, let's go directly to it as the initial step we need. we configure the general layout for the Gantt worksheet and the Gantt worksheet settings. We start by setting up the header rows for our entry and the Gantt chart area.

We select these two rows and reduce the overall text style to size 10, bold it, and align it. in the center and in the middle, then we select the range b13 to w13 and give the cells a green fill, since this is the row where we are going to place all the headings of a single column. In the row above, we then create section names that start with these. the first columns as the section for identifiers, then the next six columns for the item details, then column k to t is where the actual planning will be done, while the next two columns will be used to preserve a snapshot of the actual plan on a certain moment. so we're going to call this base plan for both planning sections, we want to be able to document the dates of an update or a snapshot save and that's why we use these two extra rows here to create the source for the input field a little bit smaller and just insert a placeholder date value for now the 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 color of overall blank font and increase the height of the rows Since we need a little more space here, we start by naming the first columns here as id, then sid for the scenario id and rid for the role id.

All these columns in the id section will be calculated automatically, so let's color them according to the legend that I have prepared 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 will be a special column that only contains the indicator colors, so let's give that an even brighter green fill. next is the column for project roles which will be calculated automatically based on the team member selected in this column and then as the final column in this section we use the Unicode icons that I have prepared there and this will be the one used for Highlight the problems you have with any of your elements in the actual plan section.

The most important columns are the last two, as they will be the calculated start and end date for each item. These two columns will contain the largest amount. The crucial formulas in this worksheet and the calculation of the start and end date will always be based on two types of input: the first is the number of business days required for an item and the second is some type of date; can be a separate entry entered manually. start date or a separate end date or and this is where the main power of this template will come from a dependency connection to another element, said dependency connection will require a link to the id of the other element based on which crucial information will be extracted in these two columns and then we have a column to select the type of dependency connection and we want to potentially add a delay by which you want the element to move forward or backward for the best possible user experience, we group these input columns related to the state 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 let's make these two columns collapsible as well.

The last column will be used for request tracking, so let's put a percentage symbol there now. is the perfect time to quickly adjust the size of the columns to match the expected input size and right after that we can start creating the timeline area for the timeline, we will create a dark blue row right here and just above we will select the the cells in these three rows set the padding to light blue, make the column very tight and then merge these three cells, set this option to rotate the text up, text alignment towards the top and the font size to 8. and now we can simply select the entire column and transfer this specific formatting to as many subsequent columns as we want using theautocomplete and that's it for the basic layout of the header and timeline section.

Here, wonderful for a cleaner layout, let's hide the default grid lines and add a better way. Instead of looking for a custom format, we initially want to visually separate the main sections, so let's hold down the Ctrl key and select the respective sections, including the header rows and the number of content rows below, with all of these selected, let's go to the home tab we 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 medium light gray and then specify that we want to add a left border and right.

The overall spacing on this sheet here is intended to fit perfectly. in the open window with the actual planning section fully expanded and the base plan section collapsed, so let's do that and then select the entire content area and add some horizontal borders in rows with an even lighter gray, perfect, the layout and The size of the basic timeline is already defined, so now we know the ideal position to add a settings button at the top right that will allow us to jump directly to the settings worksheet for the button. We merge these six cells. We set the text alignment to the center and middle.

We added a white border around them and labeled. configuration, then we right click on it and click on the link that opens this window, since we want to create an internal link within the workbook that we are going to place in this document and we select the configuration worksheet, we can even define a destination cell, so choose the cell in the settings sheet where we will probably place the button to return to the Gantt sheet later and eventually I recommend also defining the screen tip, which is the text that is displayed when hovering the cursor over the button, and add this link to our button. has automatically changed the formatting of the text here, so let's fix this and make the text bold, not underlined and blank.

Now with the current settings only the text itself works as a link, but not the entire merged cell area, but we can easily change that by turning on the setting. text option and now these merged cells work perfectly as we expect a button to do, we are redirected to cell x2 in the settings worksheet and we are going to recreate a similar button that takes us back to the Gantt worksheet , so add the same white borders, call it

gantt

, add a link to cell bl2 of the Gantt worksheet, then add a screen tip and apply the required formatting and now this works like a charm, let's just increase a little the size and that's it.

The initial input section we are going to create in this configuration worksheet will be for some basic project details, we add some gray borders around that section and adjust the column size so that we have two small columns on each side that act as padding and two large columns to create some input fields, let's also hide the grid lines throughout the worksheet for a cleaner look and then create multiple input fields for the project title, project manager, and potentially some For additional information, such as project priority, we select all of these tags. bold, decrease the font size and also add some indentation and a light gray border and then these cells are the actual input fields that we highlight by adding a light green padding and a similar font size and indentation format.

Let's enter some example values ​​here 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 a form of data validation for a controlled list of input values. For now we will refer to the project title and the project manager. directly in the Gantt worksheet, so let's replace this title with the input value from the configuration worksheet and then jump 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 show the time span of the project which includes the start and end date of the project as well as the duration of the project, for now we will just enter an example date value for the start date of the project and some placeholder values ​​for the end date and duration of the project, but later in this tutorial we will transform this into a dynamically calculated time span, for now we only need the start date of the project, since we need it to set the timeline values, which basically means we reference it as the starting date on the timeline and of course we make sure that's the case. has a clean date format and then for the next date on the timeline we use the business day function as we want to focus entirely on the actual business days the business day function returns a date which is a number specific number of business days after a given start date, so for the start date we simply reference the start date in the timeline and since we only want to get the next business day here we enter 1 for the number of days and then we copy the date format, then we can fill all subsequent cells in the timeline. here with exactly the same formula we have used relative cell references which looks pretty good, then in the dark blue row below we want to display the initial letter of the day of the week of the respective date for that we make use of the text function which is perfectly suited to applying a specific 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 letters of the day of the week, since we're only interested on the first letter, let's wrap this formula with the left function to cut the first letter from the left, let's just decrease the font size and add this formula to all subsequent cells and that perfectly shows that the dates of the line of time actually only cover the default workdays, which are Monday through Friday, amazing as a final step.

From this initial setup I want to dynamically structure the timeline and chart area based on the weeks, so I need to create a visual separation between the weeks that still displays correctly even if the timeline is shifted left or to the right, for that we create a dynamic. first the name reference which 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 just be date, we set the scope only for this worksheet, so this name reference will only be valid in the scanned worksheet and then we click on the reference below which is currently an absolute cell reference with a dollar sign in front of 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 any column it is called from while row 10 is fixed.

Let me demonstrate that when we reference the date name of this cell, it references the date cell in the same column and when we jump here and reference the date name of this cell it references the date cell of this column similarly We can also create a name that always references the next state on the timeline all you have to know is that the reference you define in the following formula will always be relative to the cell you had selected at the time you opened the window name definition, that means that 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 refers to the date cell in the following column a quick test shows the date refers to the same date of the column while the next date refers to the state of the next column now we can define a simple logical test that checks if the day of the week of the date is greater than the day of the week of the next date with the simple workday focus setup we have on our timeline, this expression should always be true or when multiplied by 1 it should be 1 for all cells whose date on the line time is 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 formatting rule conditional, we select the option use formula to determine which cells to format and pass the formula we just created to identify all the cells for which we want to have a light gray right border, that is all we need, we confirm it and as you can see , this not only wonderfully adds a weak base structure to this area, but it also works regardless of the start date. es and would even work if you decided to define custom weekend days or consider holidays in your Gantt chart.

Now let's focus on the basic details of the item before we start setting them up. Let's make this row here very small to use as a separator between the header and the content and also set the font size for the entire input area to 10. One of the most important input columns is the type selection, as this will decide if the item is a stage, a task or a milestone. we want the user to be able to select from these options, let's create a dropdown list by opening the data validation window selection 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 possible type values ​​and to make this dropdown selection available in each row we use the autocomplete handler and simply drag it down, let's set the text alignment to center and now we are ready to enter some example elements here to quickly set up we upload a stage called planning with some task items and a final milestone and then a second stage called implementation with a similar internal stage structure as we need to use the values ​​in the type column in some tracking calculations .

Let's make it easy. to reference by creating a dynamic name reference called type, we set the scope only for this worksheet and this time, unlike the date name reference, we will remove the dollar sign in front of the row number to make the row dynamically switch to any row. This type name is referenced in any formula in which we will use this value. Now it will be much easier to build and 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 entire Gantt range and add a new conditional formatting rule that is applied in each cell for which the type value in its row is equal to s, which means that we want to particularly highlight each row of stage setting the font style.

By bolding and adding a medium gray border at the top and bottom you will see this instantly applied and beautifully highlights each row for which we selected stage as type to further emphasize the fact that all tasks and milestones are sub-items de At the stage we also want their names to be auto-indented, for that we create an additional conditional formatting rule for this specific column. This time we want to automatically format all the cells whose type is t or m and to create the indented format we must go. to the numbers tab, jump directly to the custom category and here comes a cool trick because you can represent a text value in a cell using the add symbol and then we can simply add some space characters in front of that to create the indentation like You see, this works very well and now we have a clear visualization of the hierarchical structure of our elements.

Additionally, a powerful way to visualize the difference between stages and their sub-elements is the indicator's color column. We can quickly set the default color. flags by adding two conditional formatting rules to this column, one that applies whenever the type is equal to s, in that case we want to have that darker gray as the fill color, let's make this a little stricter and then add another rule that applies whenever the type is t or m and that will add alighter gray fill color, beautiful for even cleaner formatting, you can remove the border formatting for this particular column and as the last part of the basic element details, we also want to set up another really simple column, the column that you allows highlighting issues here, the input selection will be quite simple, we will set the text alignment to the center and then create a dropdown list containing this symbol, that way you won't have to type anything manually Here you can easily select this symbol for any element that you have a problem and once the problem is resolved, simply use the Delete key to delete it again.

Simply put, these were the basic details of the element for which we only had to configure the static drop. down selection or simple input fields for roles and team members column; However, we will set up a truly advanced, intelligent and scalable team and role management system that will allow us to manage up to eight project roles and an unlimited number of team members in the setup. area, so let's jump to the configuration worksheet and create a new section for project role type management that 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 are already used for the project details section, then we adjust the size of these columns to have a right and left padding and a little more space for the input columns, since we want to create a list here, we need column headers for which we can simply copy. on one of these labels and change the text, the first column will be for the role name and I prefer the text to be middle and center aligned, then we can duplicate it using autocomplete and change the other headers to the code that is the column to put in a shortcode that represents the role and the pound symbol for the column in which we will automatically count 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 since the count column will be a calculated column, we simply set the background to light gray. I guess it's time to add some example roles here. The number of entry rows is intentionally limited to eight for two good reasons, first. The key idea of ​​these roles is to group individual members of your team into functional groups to eliminate complexity and get a better overview in case you have a large team and the second reason is for good visualization of different roles with different colors.

It is required to have a limited color palette size to keep them somewhat distinguishable. The big advantage of this approach is that we will color in this roll type level and each row can have an unlimited number of team members assigned to it. potentially adding an unlimited number of team members to the entire project, that is why the team management section of the project 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 to automatically display the role shortcode, so like I said, we could make this list as big as we want, but for now I'm going to have about 30 rows so that it still fits on the screen and eventually close the section at the At the bottom let's add these gray border lines.

The name column only requires a simple text entry, but for the assigned roles column we now want to take all the defined roles from the other section and make them available in a dropdown list. The important thing here is that we want this dropdown selection to always show exactly only those values ​​that are defined but not the values ​​of empty cells to create a dynamic range reference that grows and shrinks based on the number of non-empty cells in a range that we can use the offset function for those of you who are not familiar with this powerful function, I quickly demonstrated here that the offset function always returns a range starting at an initial reference, so we could add a row or column offset which we don't need in our case, but I need to define the height dynamically by counting the number of non-empty elements in the maximum possible range and for that we can use the count one function as you can see, this perfectly returns a dynamic array with exactly all those non-empty values ​​and once add another entry the array grows accordingly and when I remove it the array shrinks again.

Now we can use this formula directly to define a data validation drop-down list, so let's open the data validation window pick list and then for the source, we use the scroll. function with an initial reference and then skipping the next two arguments and then defining the height as the number of non-empty elements in that range, as you can see, exactly those five defined roles can now be selected here in each row, when we delete an entry, the dropdown selection adjusts accordingly, which is just amazing in the end. For the code column here we want to automatically display the respective shortcode based on the role selected for that task.

Excel offers a wide range of search functions, but the go-to formula I prefer and recommend is good old index match for those of you. If you are not familiar with this formula, the index function requires you to select a range from which you want a value to be returned and then assign a specific value in this range of columns, the second input required is a row number and to get the correct row number we can use the match function which simply looks for a lookup value in our case which is the role assigned in a lookup array which is the offset column here and it has to be an exact match for the match function return the correct row number for the index function which then returns the correct shortcode, simple as that, now let's see what happens once we add this formula to the row below we get an error n as no one is selected roll here, since this doesn't look very pretty, let's look at this. arrow by wrapping the formula with if and a function that allows us to return an alternative value in case this arrow appears, now we can add it to all the other rows and also add some additional team members.

You will see that the correct code appears instantly once you assign one of the defined roles, that is amazing, the last column that we have not taken care of until now is the column to automatically count the number of team members assigned to each role and for this first we check if the roll cell is not empty because otherwise there is obviously nothing to count and in case it is not empty we use the countif function to count the number of values ​​in that role range that is equivalent to the role defined in this list ; otherwise we just return an empty text string and add this formula for all the rows and instantly have an overview about the role distribution whenever we add a team member and assign a role this count is updated correctly which which is great for giving both lists a more list-like appearance.

I like to add some white. separating horizontal border lines between the cells looks pretty good and now we are ready to go directly back to the Gantt worksheet where we are going to make use of these

smart

lists here, now we want to be able to select a team member through a list dynamic dropdown and then automatically display the corresponding role right next to it. Let's add this dropdown list of team members by applying exactly the same technique we've 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 one function to count the number of non-empty cells in the entire team member name column now we can easily assign the defined team members for these tasks and milestone items and to automatically display their respective roles, we just need to search the selected name in the project team members list and return the shortcode of the role for this, let's first configure a dynamic name reference for the team member just like we did before for the type, call it team member.

The scope is this worksheet and to make it dynamic we will remove the dollar sign again and then to find the respective row we define an index match formula which is wrapped in an if. In 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 find the team member in this name column as an exact match in in case this returns an error, let's just return an empty string again and once we add this formula to all the rows using autocomplete, we see that this works perfectly, let's just decrease the font size and change the color to a gray shade and let's also add some indentation, we can even increase the size of this column. a little bit and now this is a near perfect implementation of this team member and role assignment.

There's just one last thing I want to add to this feature to make it completely complete. For this, I go back to the configuration worksheet and look at the beginning. We need to delete a column here to get the Gantt button back to its original place, since we've obviously added an extra column in the process. Ok, now what I want to add is an easy to use way to remove team members from the project. What may happen from time to time deleting a team member like Wylin here is not a problem, but what if the team member you deleted here is currently assigned to an item on the Gantt worksheet?

Well, you'll see that the role is empty because the search results in n a. error, but the name is still written in the cell, so I think it is very useful to visually indicate that this team member is no longer part of the team and that the item potentially needs to be reassigned, so my idea here is to add a rule conditional format. that checks if a match function search for that name in the name list returns an error using the sna function and if that's the case we change the font color to a warning red exactly like this, so now when let's add this team member again to in the list there is no warning and once this team member is removed again we get the indication that we may need to reassign this item.

Surprisingly, now that we have set up the entire item details section, let's start implementing basic manual scheduling with independent start or end. dates, the most important columns for the actual plan will be these two columns calculated for the start and end date because all the date related display in the chart area will be based on these two columns, so the start date and completion should always be calculated is that we will have different input options, as mentioned above, the calculation of the start and end date will always be based on two inputs, one input is the number of working days required and the second input is an independent start date or an independent date. end date or a dynamic start or end date that depends on another element of these two calculated start and end date columns, one will always be defined directly from the date section and then the other will be calculated based on that and the defined number of business days 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 weekday column we make sure the content is center aligned, let's get started. By setting the simplest of all options, which is to provide a separate start date and the number of workdays required to make these cell values ​​easy to query in other formulas, we define dynamic name references for the start date. standalone start, always keep in mind to remove the dollar sign in front of the row number and then we do the same for weekdays 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 this by returning the maximum of the cell value and 1.

This has two big advantages: firstly, it guarantees that an element will beinstantly visible once a start or end date is provided, even before the number. The number of business days is entered and secondly, we do not need to actively enter a business day number for milestones as we will need to reference the start of the calculated plan and plan n for the Gantt chart display. We'll also add dynamic name references for these, I explicitly call them plan. beginning and end of the plan, since these will always be the ones that will always represent the real plan in a constant place.

Alright, the formula for starting the plan in this scenario is pretty simple. We first check if the standalone start date is not empty and if that is the case, we return the standalone start date; otherwise an empty string, so this is the date taken directly from the input section, while the plan end date should be calculated based on plan start and workdays similarly. We also check if int start is not empty because only then the plan start is also not empty and then we will use the business day function entered in the plan start as the start date and add the business days minus one, so in this example nine business days for the start of this plant, the reason we have reduced this number by one is that the planned start date is already one of these 10 business days, so the plan completion date is only nine days skillful later.

The fallback value in case int start is empty is again just an empty string let's do a quick check if the calculation is correct by looking at the timeline as you see, May 10th to May 21st are exactly a time span of 10 business days , so it works correctly when we look closer at this formula, there are two crucial things that we always need to keep in mind first whenever we calculate one of these plan dates by adding or subtracting the business days from the other date, it is crucial that we do reference to the other calculated plan date and not to the entry date as int start in this case directly because otherwise we will end up creating circular references at some point and the second aspect to consider since both formulas will become much more complex with time and we will also reuse certain calculations, it is advisable to transform all subcalculations into Here, into what I call named calculations, what that means is that instead of entering these subcalculations directly into the formula itself, we will create names for them in the name manager and we'll just have to reference the name in the larger formula to get exactly the same result.

That not only makes larger formulas easier to read and reusable, but also allows us to manage and potentially adjust each subcalculation in a central place in the name manager. Now let's use this to manually set the schedule for tasks and milestones. From this stage as you can see, as soon as we enter the standalone start date, the plan start and plan are displayed instantly as the workday value is set to 1 by default and for the milestone we only need to enter the date independent start and we can Leave the workday cell empty because we want the milestone to start and end on a particular date.

Perfect, with this scheduling example set up, we can now create the default display in the chart area just below the timeline. We have all the information prepared that we need. decide if an item is within your plan in a given cell because now we know what is the start of the plan, what is the end of the plan and what is the respective date on the timeline. These name references work correctly from every cell in this area of ​​the chart, no matter which one. one that is taken to determine if a cell should display something or not, all we have to do is check if the date is within the planned time span, so if the date is greater than the start of the plan and less than the end of the plan, this formula returns true or false and for demonstration purposes, let's display that as 1 or 0 by multiplying it by 1.

We can use the autocomplete handler to add this formula to the entire row and as you can see, this displays a 1 in each cell that belongs to the defined plan time span when we add this to all the rows, we can verify that it works perfectly for all the defined elements. Surprising for this formula again, it makes sense to transform it into a named calculation, so let's copy and create a new name that is called element in the plan set the scope of this worksheet and paste the formula. It's that easy. Now we can override this formula and have a short expression that works on every cell in this area of ​​the Gantt chart and that's all we need to implement the default value. visualization A big goal of this template is to have stages, tasks and milestones displayed differently, and while stages and tasks can be perfectly displayed using a cell color fill without any cell content written, we want milestone show an actual milestone symbol for For that purpose I have prepared two Unicode icons, this one for an open milestone and this one for a completed milestone and for now this symbol for the open milestone needs to be written somehow in the cell, so let's copy this Unicode icon and start building the milestone visualization. as a formula in the cell in the formula, we just have to check two conditions that must be true first, the type must be a milestone and the item must be in the plan in this cell, if that is the case we simply print this symbol milestone. otherwise an empty text string, let's add this formula to the full range, we will instantly see that the milestone symbol is printed on the correct day and in addition we will align the cell content in 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 jump directly to the conditional formatting rules manager and create two new rules, let's create a default rule for stages simply Like for milestones, this formula checks if the type is a stage and if the item is planned in the respective cell.

If both 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 now displays perfectly as a dark gray bar, so let's move this rule down the rule order, which simply means that this rule is now applied after the other two rules and then we duplicate this rule to Create a similar ruler with a lighter color. gray for tasks once we apply this rule, both stages and tasks are now displayed beautifully. The only thing left now is that we also want a similar gray color for the milestone symbol, so let's go back to the conditional formatting rules manager.

Duplicate one of these two. The rules then change the condition from type to milestone and this time we don't want to control the cell padding, so we clarify that we first jump to the font tab and change the font color to this darker gray. I decided to make this milestone symbol colored. matches the color of the stageTo stand out better and once applied now, all these types of visualizations are in perfect harmony. Beautiful now, before I close this conditional rules formatting manager, I need to show you two concepts here that are crucial in terms of worksheet efficiency at the beginning, always add a control. this stop if is done via the checkbox if a rule and all following rules are mutually exclusive, what that means is that in this case if the scenario coloring rule here applies because its conditions are true , then the next two rules don't have to be checked because it can't be true at the same time, that will save you a lot of computational resources, especially with a larger number of rules here and the second concept somehow relies on this stop-if feature true and is even more crucial when we take a look at these default values.

By coloring the rules we see that each of these does the same item in the plan check which is super inefficient as we do the same condition check over and over again and to avoid this we can add an extra rule that does that this element in the plan verification be done once at the end. At the beginning and only if the cell passes this test, we continue looking at the next rules, that's why I call it stop rule because what we do in this new rule we will check the opposite of the condition that we want to be true, so in this In case we check if it's not element and plan, we don't need any formatting here, just click OK, place it right above these color rules and then check the Stop if true checkbox, so now, If the element is not in the plan, in a cell, do the following. the rule conditions will not be checked because it is not needed and this cap rule now allows us to simplify the coloring rules by removing the element in the plan condition and that leaves us with the type checking here and now, all this configuration is super efficient and We are perfectly prepared to scale this with some additional color rules later.

Great, now that we've set up the default display, let's take a look at the other manual scheduling option which, instead of entering a separate start date and scheduling ahead, you enter a separate end date. date and doing backward scheduling, let's reproduce the exact same time span for this stage by entering the end date that is calculated in the plan and the cell at this time, which is May 21 at the time the plan is started and the plan and the formulas are only able to work with the separate start date input and that's why now they don't show anything but an empty cell and the display is also gone, but we're going to change that in a second, first we'll create a dynamic name reference for the end and date and then jump directly to these formulas, this time we start by modifying the plan and the formula at the beginning instead of returning an empty string if ind start is empty, now we continue with the second if statement which check if end end is not empty because if that is the case we want to directly get the value of end end and only otherwise we will return an empty text string which 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 should be calculated backwards based on the plan end so let's add the same logic here first checking if the end end is not empty and if that is the case use the function of business day placed at the end of the plan as the start date of that calculation and then we add the negative number of business days, so we multiply the business days by -1 and since the end date is already one of these 10 days workdays, we have to add one again so that it only goes back nine workdays and otherwise if int end is empty we simply return the empty text string.

Now we have successfully reproduced the exact same time span that we had before, so let's again take that subcalculation and create a new named calculation that we call plan start calculation. Let's update the other rows at the start of the plan. column and test if it works on the other rows just as well and as you can see we can easily recreate these time periods by providing only separate end dates. That is surprising. We have intentionally implemented both formulas for plan and plan start using hierarchical logic. that means that these formulas are written in a way that the initial int value will always override the ending and value, so once we enter an initial int value, the ending value will simply be ignored, which is really important as it ensures that there is no confusion about what value is taken if multiple entry dates are provided at once;

However, at the moment this logical hierarchy isn't instantly obvious when just looking at the start and end columns, so what we do to make this super intuitive is add a conditional formatting rule for the end and end column. which just checks to see if int start is not empty because if that's true then the end end value is basically dead, it's not used and we indicate it by giving it a medium gray font color, there we go. Now when I enter a start date, the respective end date is automatically grayed out and I instantly know which of the two is currently active.

Let's overwrite the stage and task elements with the original in the start dates, leave the milestone defined at the end and date and then quickly set up a plan for the second stage with the stage and the first task using a forward schedule and the other two backward scheduled tasks here you can seeperfectly how the bar now grows backwards as soon as we set the number of working days to a number greater than 1, the same for the next task and for the milestone again, it is enough to just provide a great date. Now is the perfect time to show you how you can quickly automate calculations and visualization of stages based on your elements, since we have no intention of overriding the start of the calculated plan, 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 to the start end cell of a stage and then replace this placeholder with the planned start range of the items at that stage and in the same way we can dynamically calculate the number of working days using this placeholder formula referencing the plan. start and then calculate the net working days between the start of this plan and the maximum of these plan elements and dates we are now free to change any of these tasks or milestone schedules the stage time period will always update automatically, that is surprising, but I think that the use of the formulas in these input cells somehow make it necessary to highlight an input cell every time a formula is used, for that let's add a new conditional formatting rule to that start column int which checks if the cell contains a formula, let's make the cell reference completely relative and The format that I think makes sense in this case is this strong blue font color because it is a color that we are commonly familiar with from links , for example, from websites or documents, and somehow these formulas are linked to other cells.

Let's also include weekdays. column in this rule and that makes these formula inputs clearly distinguishable from the normal input. Once we have used such auto stage formula in one stage, we can simply copy it to another stage as the range reference is relative and covers all these included elements perfectly. directly in case you have a different number of items in that other stage, all you need to do is adjust this reference range with the mouse and you are good to go and the same for the autostage workday formula. What's really amazing about this general setup is that We can now collapse the date entry columns and have the workdays as key information still visible and now whenever we adjust the number of workdays required for a task, there is no need to manually update the working days of the stage, since they are automatically updated as a calculation of the working day of this automatic stage. it's not just adding up all the working days of your items, it actually calculates the maximum time span of all the items that it is fully capable of handling as long as the items overlap within that time span, while for auto stage workdays makes sense to show the actual number i think about the auto stage int start value, we can make it even more obvious that this is not a real start date by adding an additional rule that only focuses on this column and is also limited to only stage rows, so whenever a cell is in a stage row and it 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 show the text automatically, let's set the text alignment to center and I think that's a beautiful and intuitive setup, this manual scheduling with separate start or end dates is quite useful, but it's not nothing compared to what we are going to create now because now we will add the main feature of this template, which is intuitive and super Powerful dependency engine that allows you to make your project plan completely dynamic.

We'll implement the four possible types of dependency connections you can have between elements, so this step will transform the scan graph from an attractive project visualization tool to a professional project. planning tool, this is the section that will allow you to quickly create all kinds of dependency connections between elements. We will build all the dependency logic based on the second task p2. Now, instead of depending on one of these manually defined independent dates, make that task dependent on task p1, so delete all these entries for subsequent items, delete the independent finish dates, and since we want this task p2 depends on task p1, we will also remove the separate start date for it. weekdays can stay there since we will use them the same way we did before.

We also temporarily move the timeline one week to the right so that not only forward-facing dependencies but also backward-facing dependencies are displayed instantly once they are deployed. The key idea for making an element dependent on another element is that you will have to bind it to the other element's id. This obviously requires us to create a reliable identification system beforehand 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 cell number. row, since we don't want to manually enter any id, somehow we need them to be automatically created and self-maintained in case we insert or delete a certain row, that means we want to have an id system where each id represents dynamically the row position of its element in the entire list of elements as a single number, the simple solution of just entering and autocompleting a sequence of numbers directly is not enough in that case because as soon as we insert a row and use the option to complete, we get duplicates here and would need to manually update all subsequent ids again in the same way by setting the first id to 1 and then for all subsequent ids simply referencing the previous id and incrementing it by 1 also doesn't completely work as which unfortunately the id below the inserted row still references the same id, plus both very simple solutions would be completely destroyed as soon as you delete the initial row, so we have to create something that is much more robust and self-sustaining.

The key to achieving this 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. short call the range, short for the previous column range and define it as a range that starts from the header cell and ends at the cell just above right now this is a fixed range but remove the dollar sign in front of the last row of the cell will make this a range that will grow and shrink dynamically depending on the cell it is referenced from, also since we could make use of this in other columns as well, let's also remove the dollar signs in front of the column 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 on the formula below that you see with this selected cell, refers to the exact range we need, the reason we include the header here is because it guarantees that even if we insert a row above the initial row, it will become part of that range and will not be excluded .

Now we can use it 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 starting row in the list of items, that is the case whenever the cell row itself is equal to the header row plus two two because we have the separator row in the middle , so the starting row will always be two cells below the header cell if that is the case the id has to be one and for all subsequent cells for which that is not the case we simply calculate the maximum of the range from the previous column and we increment it by a simple one like that, let's drag the autocomplete handle down to add it to all the rows of this column and that looks good so far but the real power of this becomes visible once we insert a row and we fill in the values, you will see that all the id values ​​update perfectly to correctly represent their new position in the list, even if you were to insert a row above the first row and use the fill option, it also updates correctly, which which gives us an automated and robust identification system that is not instantly destroyed every time we forget to do it manually. update it now we can jump to the dependencies section and make task p2 linked to task id p1.

This link should always be created by referencing the respective id cell to ensure that the linked id number is updated correctly in case the other item's id changes due to an inserted or deleted row to help you instantly see if the id linked is a real cell reference and not just a fixed number typed, we use the conditional formatting rule which 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 blue which tells us that it is linked to another cell.

A super powerful advantage of referencing the other element's id is that it couldn't be easier to make this task depend on a different element because you can simply click on the cell and drag the reference to another id OK, now that we've found a way to create a strong link to the id of another element, let's make this cell value dynamically referenceable as point id where the d stands for dependent and the initial thing we want to do with this id is to extract some fundamental information about the other element, the first information we are interested in is the position of the other element, so if it is located above or below the row of this element, this information can be really useful as an indication for a forward or backward scheduling dependency. dependency and to indicate that visually we are going to make use of these two Unicode arrow icons, the formula for this is quite simple at first we check if the point ID is not empty and if that is the case we just have to check if d . id is smaller than the id of this element which means it is located above this element and we insert a placeholder for the up arrow and alternatively in case d id is greater than the id of this element , the other element is obviously located below this element, so here we want to show the down arrow and as an alternative option we simply return an empty text string, jump to the Unicode icons and copy over the up arrow, paste it to replace the placeholder and then let's do the same for the down arrow, perfect, let's also decrease the set font size. aligning the text to center and adjusting the column size and that gives us a nice indicator of the position of the linked element in the next column we want to automatically display the type and name of the linked element this is a classic search problem so we can use the match function to search did in the id column to get the correct row number and then use the index function to return the respective value of this or this column as we will need the row number of the other element on multiple occasions let's get started with the match part and let's do a named calculation for the lookup value we pass d.id then for the lookup array we can pass the entire column b as an absolute reference with dollar signs and we need to have an exact match, this returns 16 in this example which is actually the row number of the linked element, so let's copy that formula and transform it into a named calculation and call d.row, perfect for extracting the type of another element, we use the function of index. to take a look at column e and return the value of the calculated row in row d which seems to work correctly, let's change the id to reference the milestone which also works like a charm so we can save this calculation as d.type and for the name of the linked element is 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 this information about the other element available as a named calculation, let's also decrease the size from font to bit and change the font color of all this information tothis dark ray to show that this is secondary lookup information and now we can create a concatenated string containing type d in parentheses followed by name d awesome but once we add this to some rows that have this was not referenced it will result in an error, so to catch this and other possible arrows, let's wrap this expression in an if arrow function.

An additional thing to consider is that the name of the other element could be much longer like this, for example. the text extracted in the d element column would spread to the following columns and to avoid this I recommend adjusting this formula instead of just directly printing the full name. We will first check if the length of this name exceeds a certain threshold, for example, 7 characters. and in case it actually exceeds that threshold we will 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. exactly at that threshold and once we increase the column size a little bit, everything now fits perfectly and as soon as that threshold is no longer exceeded, the points disappear correctly and we have the full name displayed perfect, let's do a final test for everything d output the element by changing the referenced id once again and the information we get is exactly what 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 In column d.with we add a drop-down list that allows us to 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 this makes the plan start linked to another date, an increasing number. of working days required will make this task grow from start to finish means that this item starts on the same day that the other item starts, so the start of this item's plan is linked to the start of other items' plans, which It also means that it grows forward with an increasing number of working days required. start finish now means that this element has to finish just before the other one starts, so this time the plan finish date of this element is tied to the planned start date of the other element, which means that a increase in required working days causes this item to grow backwards and eventually finish to finish means that this item ends on the same day that the other item ends, so the plan end date of this item is tied to the plan and the date of the other item and with increasing number of required working days it also grows backwards, a great way to provide basic information. and the instructions for an input column like this are to select the header cell and then open the data validation window without adding actual data validation.

What we are interested in is the input message tab which allows us to enter an informational message that is displayed every time this cell is selected. I will simply enter basic information about the four types of dependency connection and now this information is available here whenever you need it. The column next to it will allow us to define a delay by which we want this element to be changed. on the right in case we have a positive value or on the left with a negative value, let's set the text alignment to center for both columns, adjust the column size and create a dynamic name reference for both columns, we call first d.con and we 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 start of the plan, the plan and the formula.

I hope you remember the hierarchical logic. what I talked about before, which was basically that both the start of the plan and the plan and the formula always start by looking at the initial value of the point int and only if that cell is empty do they consider the int and the value and only if that cell is also is empty they will now consider the dependency section and do the date calculations based on what's here, so let's start with the plan start formula and replace this empty string with a completely new expression. The first thing we need to do is check if d.id es is not empty, otherwise there is nothing that can be calculated here and in case it is not empty, we can see what type of connection is selected for the startup formula of the plan.

We are only interested in those types of dependency connection that will make the start date linked to the other item. The first of these is the classic end to start dependency in that case you want to do the respective calculation for which we simply enter a marker of position for now and alternatively if the selected dependency connection type is start to start we want to do the respective calculation from start to start in case it is one of the other two dependency connection types then we know that the plan and the date They will be linked to the state of the other element and we must do the calculation backwards according to the plan. and the date and the number of business days required and guess what is something we have already defined as the plan start calculation, what a great coincidence now we just have to close these brackets for the last two if the statements provide an alternative value in case id is empty and it will eventually close that bracket too for now with end 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 are already available, which is not yet the case.

To capture these arrows in general for this huge formula, we are going to include them inside an if error statement just to make sure that it will always return an empty string in such case for the plan and formula, we are going to implement exactly the same pattern instead of return an empty string. Here we will first check if d.id is not empty and if that is the case, we now check if d.con is equal to one of the other two dependencies. connection types that are end-to-end and, alternatively, end-to-end; otherwise, in case it is one of the other two values, we need to calculate the end of the plan based on the start of the plan and the number of required working days that are already covered in the plan and the calculation. let's close both if statements we enter an alternative value in case d.id is empty and we can also directly wrap the entire formula in an if arrow function to catch potential errors the basic logic of when to do what calculation is now implemented when switching between different values ​​of decon the placeholders show us perfectly which of the plans begins or plan and is the one that is calculated based on the other element, this allows us to proceed to replace these placeholder calculations with some real calculations and for the beginning we go To remove this delay value here to avoid confusion in the end and start calculation, we need to set the start date of the plan to the business day after the plan and the date of the other item, so we want this date to be a day business day after this plus a possible delay, the business day function is what allows us to do exactly this calculation, we just need to get this value somehow, but since we already know the row of the other element, we can simply return this value using the index function by entering column t as the return matrix and row d as row with this, now we have a start date defined in the business day function and we only need to specify the number of business days that we want to add to this date, since to finish to start the dependency connection that we are looking for. the next business day plus a possible delay, we will enter 1 plus d delay and close the business day return.

At first glance it seems to work perfectly, the start of the plan is calculated correctly and therefore the end of the plan can also be calculated based on the start of the plan. and on weekdays when we add a span to this dependency connection, the second task shifts right or 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 has finished, perfect for start to start calculation, now we can just copy that formula as we just need to make some small adjustments for this one, we want both items to start together, so this time instead of taking the plan and date of the other element, now we need to take the planned start date of the other element from the s column and for the day argument we are going to remove that because by default now it is not the following but the same day. that we want to have and we only want to change it for a potentially given delay when we now make this a start-to-start dependency connection, both elements start perfectly together, changing the duration of the other elements in this example has no impact, but changing the independence of the other element.

The start date makes the two move seamlessly together for better maintainability and less complexity in this huge formula. Let's transform both expressions into named calculations. The first one will be called fs calculation and the second one, consequently, ss calculation and that way we have now transformed this huge. amount of calculation logic and functionality in a relatively small and understandable set of commands, let's move directly to the plan and the formula because here we are now going to create similar expressions for these two types of dependency connection for the end-to-end calculation that we now want . have the plan and date of this element one day before the planned start of the other element plus a possible delay, of course, that means that we are going to use the business day function again and as a date to start we will take the plan of others elements starts by applying the index function to column s with row d as the returned row and since we want to calculate the work day just before that date, we add -1 this time plus the potential value in d lag, let's close that statement. press Enter and change the dependency connection from start to end and it works like a charm when we change the start date of the other element, both elements stay perfectly linked through this connection and also adding a positive or negative delay works as it should. eventually, for the end-to-end dependency connection type, let's copy this expression and you probably already know the adjustments we will make: we will remove the -1 and take the date of the other element from the t column instead of the s column, as we need . the plan and date that way, both elements will now end up together on the same day, but will potentially be changed by a certain delay.

This also works as it should, now both the plan and the dates are dynamically linked and adding a delay value also produces the desired result and now you can transform these new expressions in the plan and the formula into two called calculations, the first one is called sf calculation and the other, consequently, calculation ff. Let's insert these named calculations instead of the full formulas and do a final check if everything is still working correctly, that looks pretty good in all this dependency logic, there's only one thing we can't consider so far and that's what what happens if no dependency connection type is selected so the cell here is empty with the given settings which 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, since neither of them can find one of their two relevant dependency connection types there, so the plan start and plan and calculation The calculation is executed at the same time. the good news is that we can easily avoid this by setting a default value in case d.con is empty.

IMO end to start is the most intuitive option here and the quickest way to make this the default is by saying apply end to start calculation for plan start whether decon equals fs or d. with is not equal to anything so an empty string that way end to start is always the default in case a different value is not selected, now we have successfully built all the dependency logic so we can decrement the height of this formula barhere again update all cells in start and floor plan and column with newly created formulas do same for element d columns and from finish to start is default value in dependency calculation, i suggest to just paste it as a real value for all cells by default and then make these columns behind the did column invisible as long as did is not referenced, we can achieve that with a simple conditional formatting rule this rule will check if did is empty and in that case set the font color to white that way nothing is visible as long as the id is empty and both the d element and the d-con value magically appear once we put a reference in the d-id column, in my opinion, that makes the user experience will be much better, eventually let's remember the logical hierarchy for which entry date to consider that we have integrated both in the start of the plan and in the plan and the formula with these updated versions of both formulas, the dependency functionality has the last place in this hierarchy, which means that it is overwritten by both the end date and the start date, just as the end date is overwritten by the start date of the dependency connection, this means that it becomes inactive if at least one of the start or end dates is not empty, so let's add the same dimmed conditional formatting to the dependency section that we already did for the standalone end date column.

This rule checks if at least one of the start or entry dates is not empty, in case it is true we set the font color to this medium gray and we can see that this rule is currently running as the top rule, it does decontamination values ​​to appear again, so let's move it below this rule here to ensure that the font color is white. If the ID is empty, we click Apply again. and a problem is solved great, let's do an example of planning with the dependency engine for all the elements we have configured so far.

We will make this task dependent on the previous task with a simple ending to start the dependency and see how the task has one. business day by default and as soon as we enter a number greater than that it grows to the right similarly, now we could make the milestone depend on that last task at that stage by referencing id4 but in cases like that where we just we want If you have consecutive element dependencies, there is a nice shortcut that you can use, which is the autocomplete function, just select the point id of the previous element and then drag this autocomplete handle down and since this is a relative reference, this autocomplete reference is now linked to the next ID, which is 4.

For milestones, I generally prefer an end-to-end dependency, since a milestone is often logically linked to the successful completion of one or more tasks for the second stage. We can start by copying the autostage formulas because we already know the number of elements in that stage, in case that stage had a different number of elements, we could easily click on the formula and adjust the reference range to cover all the elements and same for auto stage workday formula using this stage, I want to demonstrate how you can easily make a stage depend on another stage, for example with a start to end dependency connection.

All you have to do is determine what the initial element is in the second stage, then bind this element to the first stage and create the subsequent elements depend directly or indirectly on the initial element for consecutive dependencies within the stage, we just have to bind the second element to the first and then we can use autocomplete to create consecutive dependencies for the last two elements. Let's change the dependency on milestones. connection type to finish to finish and enter the number of working days required for the tasks. See how easy it was. Both stages are now linked and each change affects the plan and timing of the first stages, such as a longer duration or a change in the dependency structure. within that stage will cause the second stage to 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. making the plan and date of the second stage dependent on the other stage, for example as an end-to-end dependency, that would mean that the stage and date are fixed and each increase in the duration of this stage would cause the stage grow backwards to achieve We have to reverse the dependency logic in that second stage. We initially determine a final element at that stage;

In this case, this would probably be the milestone; then we link this final element to the other stage and make all other elements of that stage directly or indirectly. depends on that one, so we update these dependencies accordingly by clicking on 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 end-to-end dependency, then we make the last task dependent. on that milestone with the end to end dependency and for the top two tasks we can use autocomplete again and we just have to update the end to end dependency connection type for both every time we increase the duration of these elements during the entire stage. now it grows backwards and we need to start earlier of course once the end date of the first stage plan moves right into the future which will now move both the planned start and the plan and the second stage again to the right for the For completeness, let's also transform this back into your stage ending to start the dependency connection by updating the initial element to bind it to the first stage again, make subsequent elements consecutively depend on that initial and change all types dependency connection for the tasks again. to finish to start as you see, even these fundamental changes to the dependency structure of that schedule can be made in a few seconds, that's amazing, let's also take a quick look at how to extend the stage with additional elements, let's add two additional tasks and a milestone. to the second stage, the first thing to do in case you have applied the automatic stage formulas, click on them and adjust the reference range to also cover these new elements, the same for weekdays to allow the stage cover the entire time span of the stage by simple consecutive relationships we use the autocomplete identifier, then change the milestone dependency connection to finish to finish and enter the number of working days required for the tasks.

Please note that this is just a simple example. You are completely free to always transform this into a much more complex dependency. structure, for example, we can have this i4 task start along with the initial tasks by simply updating the linked id and switching to a start-to-start dependency connection. Now a change in the duration of these two tasks does not necessarily have an impact on the overall duration of the stage as these tasks run in parallel now or let's say we want both milestones to finish on the same day and let's figure out what that means for the required start date of the last two tasks.

Let's make this milestone depend on each other and these two. the tasks now depend on that second milestone with a finish to finish and for this a start to finish dependency given this setup, once we increase the number of working days here, now we just have to start earlier and now this even has an impact in stage time. All of these examples are just a small fraction of all the possible dependency structures you can create, so the opportunities with this dependency engine feature are basically endless. As fast as we add these three elements in seconds, we can also remove them.

See the IDs on the left perfectly updates the reference ranges of the auto-stage formulas and also does not require a manual update and since we just deleted some of these content rows and now only have 16 left at the moment, we can easily add new ones at any time by simply selecting the last row and using autocomplete as soon as we have more potential rows in this Gantt chart than fit on the screen, we would need to scroll down to see them all, but instead of applying scrolling to entire worksheet, It is recommended to scroll to the top once and then select the starting row of the Gantt area, go to the View tab and click Freeze Panels to freeze the header including the separator row.

Now only the Gantt area below the header moves when we scroll and like that. is much easier to use before continuing, let's use this extra space to add two more stages to this Gantt chart. We call this deployment preparation and make it dependent on the second stage with an end-to-end dependency connection, so this stage is affected by changes in both the second and first stages and then we add another stage called deployment that will begin right after the deployment preparation stage ends. It's simply amazing how quickly you can set up a completely dynamic project plan with this feature. as it is incredibly intuitive, fast and easy to use and another amazing thing is that when you are done setting up the dependency structure for your project plan, you can collapse all these columns with a single click and that gives you a clean and dense of the most important information about the scheduled plan and you will still have full control over the workload, so you can adjust the number of working days required for an item even in this dense view and any time you want to make a change to the underlying dependency structure, simply reopen the window. section and you are ready to go right now.

All of 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 tracks and visualizes the entire process. project time span for that, let's start by collapsing this extended planning section as we need to focus on the start of the plan and the plan and the column. Given the values ​​in these two columns, we will calculate the time span and duration of the project in business days for which we have. We have already prepared these placeholders up there, the project time span is nothing more than the minimum and maximum of these dates here, but we will not only display these date values ​​up there, but we will also display the time span in the timeline section, let's create a name. calculation called project start that dynamically determines the minimum start date of any element in our project.

The range we pass in for this starts right at the header row and to give this project some room to grow we go down to row 1000 and then set up a similarly named calculation for the project and have it calculated as the plan maximum and the column for the duration of the project in working days. Let's create an additional named calculation that is based on the net workdays function and calculates the workdays. between the start and end of the project, allowing us to replace this manual start date here with the dynamically calculated start of the project, then replace this placeholder with the end of the project, let's align this to the center and the end of the project to the left and then for the project duration we are 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 the duration of this project in working days is based on the time span of the project and is not necessarily equal to the actual workload, especially if you have tasks and stages running in parallel, now we can make use of this information to beautifully visualize the project time period in the timeline area. Let's use this row which is right at the top of the current timeline and add a conditional formatting rule for these cells we want to format all the cells for which the date on the timeline is greater equal to the start of the project and less the same at the end of the project.

All cells for which this condition is true will get this fancy green background padding that looks like it works pretty well, but it still doesn't look perfect because the row still has this standard height and that's a little bit over the top, so we need to do this row much tighter. Let's eliminate thistemporal description of the Unicode icon and let's set a row height to 6. pixels, let's add a similar conditional formatting rule to the real timeline date rows, the formula for this rule is exactly the same, but for this one we simply darken a little bit of the background by changing the fill to this slightly darker gray tone in both conditional formats.

The combination results in a fairly refined visualization of the project time span with any change in the schedule, whether it is a change in the duration of tasks or a change in a dependency structure, this visualization updates smoothly to always capture the full project time span and also, this feature becomes even more valuable when we have a really big project because once we scroll down and the upper stages are no longer visible, we still see the overall project time span relative to the elements currently visible and that is quite surprising, however, now. Since this project time period is calculated dynamically, there are still two weak points that we have to take care of.

The first weakness becomes evident when we look closer at this initial timeline date cell because it references this dynamic start date of the project 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 schedule start date more robust than a simple reference to the project start date and we create a perfectly custom name calculation called schedule plan start, we set the scope of this worksheet and what we want to do in this calculation is first check if the project start is equal to zero because then we want to use today as the alternative value for the start of the timeline and to make sure it always shows the start of today's week, we simply subtract today's weekday number with an encoding that returns 0 for Monday up to 6 for Sunday that way in today's case. is Wednesday, for example, this expression subtracts 2 and returns to Monday of the week and only in case the project start is not zero, we return this project start value minus its weekday number with the same encoding , then let's replace this original formula into this first timeline. date cell with named calculation defined right now the start date of this project is may 10 which is a monday so this is the first date shown in the timeline when we change the date start of this project on May 7, which is a Friday.

You can see that the schedule start calculation ensures that the schedule starts on Monday of that week and last but not least, in case no date is given for the project items, the schedule uses the week of today as an alternative value to start. then that no longer results in an error and we have successfully eliminated that weak point. The second weakness becomes apparent whenever we use the automatic stage formulas to dynamically calculate the time span of the stage, but the dates of the elements of that stage are not available. however, that will temporarily cause this int start value and therefore the planned start and plan and that stage values ​​to be 0, which alone wouldn't be a problem if not taken into account when calculating the project start date, which is now incorrectly zero as well, and that causes the timeline to use the alternative value as the start date for no good reason, but don't worry, this problem can be solved with a little adjustment In the plan start formula, this initial if statement ensures that the value in the start cell is only taken into account in case the cell is not empty.

Now we need to change that to make sure that this value is not only not empty but is also greater than zero. This small adjustment completely eliminates the second weak point: The only visible impact we will have is that the automatic stage 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 elements in that stage before any stage calculation can be performed. done here, let's update the plan start formula for all rows. We can perfectly see how the project start date and timeline show the correct values ​​now so that we can eventually redefine the element dependencies in the deployment preparation stage.

It's amazing at this state that in the template we have successfully implemented all the planning logic, this allows us to now focus on the display aspect of the template and set up an incredibly powerful auto coloring engine that will allow us to switch between four color modes different and fully automated. Let's start by hiding this. planning and configuration section of the dropdown menu for color mode selection, we merge these 6 cells we set the text alignment to the middle we set the font style to size 10 and bold and we add some gray borders that the label of this selection will have dropdown the text color by and just below is where we want to place the actual dropdown, so let's also merge the 6 cells, align them to the middle, set the size to 9 this time and fill the background with this light color to add a dropdown menu. selection, we jump directly 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 modes color can be selected with a single click and To make the selected value easily usable in other formulas, we can define a static cell reference here and call this color by the gray color we currently have for the indicator and Gantt chart area is what our default color mode looks like.

It appears to be clean and simple and the rules for defining this color are simply based on the type of element; However, now that we want to implement much more complex and fully automated color modes, we need to approach this differently and introduce a system that allows us to render different ones. color logics but at the same time keeps the formulas in the conditional formatting rules quite short, easy to understand and easy to scale, that is why we will build a numerical color coding system with a color coding generating formula that will cover all logic and numerical logic.

The color codes returned by this formula for each row will be used to create conditional formatting rules for both the color indicator section and the chart area. We make these color codes referenceable by setting a dynamic name reference for these cells, for now this color code name. it just references the contents of the cell, but then we'll insert the final formula directly into the formula fields down here to make this a named calculation, since we want all the colors, including the default colors, to be based on color codes. color. Let's take a quick look at how these default indicator colors are currently created.

Well, let's first move this up and set the stop check mark if true to increase efficiency. You see, for this default color, we have two rules implemented that simply check what the element type is at the same time. We have three different rules for the chart area since, unlike the indicator, coloring the task and milestone elements requires a different display for our new color coding system, we will move these types of checks to the color coding formula and we will let the result be represented by some numerical codes, since we have three different rules for the default color here, obviously we will need three different default color code numbers here to generate the default color codes.

We start by making sure the type value in the row is not empty and then use the switch statement to return different numbers depending on the type of item selected, we keep it simple and return a 1 in case it is a stage for tasks we will return a 2 and for milestones it will be a 3. otherwise, in case the type is empty we simply return a -1 let's add this formula to all the rows and that gives us a numerical encoding based on the selected element type, let's open the conditional formatting manager for the indicator color section and let's change the two color rules for this one that will replace this logical test with a color code equal to one and for this one, these two conditions will be replaced with a color code equal to two or three.

The indicator color still works as before as the logic behind it hasn't changed at all, so let's do the same for in the chart area, select the chart range, open the conditional formatting manager and replace this logic test original with a color code to check the stages, it is one again for tasks two and for milestones simply three and again it is still working fine based on these color codes since the final version of this color code generation formula will be huge and We will also reuse this expression for the default color codes. Let's copy and transform it into a named calculation called default color code.

Now in the general color code formula we can simply refer to this expression. by name, that makes even more sense since for this entire formula we now want to consider the selected color mode and build the respective formula pattern to act based on the selected color mode to find out what the currently selected color mode is. make use of the shift function, enter this color by reference as expression to look at and in case the selected mode is project structure, we want to run all the respective calculations for which we enter a placeholder value for now in this switch statement we can now just use the default color code calculation as the default return value, then if team rules is selected as color mode we want to do a different color code calculation and eventually in case problems are 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 show the respective placeholder value.

For now, let's first build a color code generating expression for the project structure mode, for this mode we will use eight main colors. from our color palette and we want each stage with all its elements to have a new individual main color similar to the default color mode. We will also need different color codes within a stage to represent the different types of elements with different shades of the same color that we need to differentiate between stages. Let's start by introducing an automated stage identification system in this column. This will automatically assign the same id to all elements in a stage, so a 1 to all elements in the first stage. 2 for the second stage and so on to make it easy to reference this stage id, let's add a dynamic name reference called stage id and then start building the formula at the beginning, we need to make sure that the type is not empty because It is an undefined element. cannot belong to any stage in case a value of type is available, we do the same check that we already did for the parent id to determine if the element is the first in the entire list, in case the row of this cell be two rows below the header cell we know that this has to be the first stage and return one for all subsequent elements, then we want to continue with the same stage id until we reach another stage element for which we then increment the number of stage id to 1. that means if the type is equal to s, we calculate the maximum id number in the above column range and then we simply add 1 since it is a new stage, if the type is not equal since the element is a task or a milestone, in that case we simply return the maximum id number of the above 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 will test the functionality by making the second stage a task, instead you will see that the stage id adapts perfectly to the new situation by including all consecutive elements in the first stagewhile making sure that the 3rd stage originally has now assigned the id of stage2 and of course this works too. the other way around, when we convert this task in the second stage to a stage, it now becomes stage number three, while the stage after it now has stage ID 4.

Now that we have the stage ID defined, let's jump directly to the color code formula to replace this placeholder on the right. here with some color codes generating expressions, the first thing we will make sure is that in case the stage id is -1, the color code should also be -1 which means no color will be displayed; However, in case we have an actual stage ID available, we will create a three-digit color code that we will first concatenate as text and then transform into a number 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 by a change expression based on the selected element type, we will add a o1 for a stage element for a task element will be o2 and for a milestone element o3 after that this concatenated text string will then be transformed into a number by wrapping it in the numeric value function and eventually we add a closing bracket to this if statement.

After adding this formula to all the rows, we now have a clean numerical 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 number Limited 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 number of scenes that are beautifully colored in order to be able to differentiate them from the surrounding scenes, so the logical solution for this mode of project structure 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 get the same colors as the first stage, then stage number 10 will be the same as the second stage and so on for the color code which means instead of using the stage id directly, we will use the so called modulo operation which returns the remainder by dividing the stage id by a divisor .

This divisor in our case has to be 8, so this function will basically divide the stage id into a part that is divisible. by 8 and a remainder that is not divisible by 8 that will then be returned, so in case the stage id is 1 it will return 1 and if it is 9 it will eliminate 8 because it is divisible by 8 and then it will return the remainder that is also is 1. the only special case that doesn't work in our favor is when the stage id is completely divisible by 8 because it then returns the remainder of 0. and in that specific case we really want to have an 8 as the first digit of the code color, so let's expand this with an if statement that takes care of the perfect special case, update all the rows, and now artificially increase the stage id here to show you the effect of this little adjustment.

You will see that the color codes for all these stages are generated correctly and as soon as you pass stage 8. it starts again with the initial color code for stage 9 and continues like this infinitely for all 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 the first digit, for that we can simply duplicate these two rules that we already have implemented for the default color and just adjust the color codes that these rules look for for the first stage elements, the code is 101 the main color for this first stage will generally be this blue and since it is a stage element let's choose this main tone of stronger blue similarly we adjusted the formula for tasks and milestone items by changing this to 102 and 103 and this time by selecting this lighter shade of this blue color, once applied you can already see how we now have some indicator colors shown.

Let's not forget to set a check mark and yes, there we have these beautiful color indicators for the first stage, of course we need. also configure the respective rules in the chart area, so let's open the conditional formatting manager for cells d and just duplicate these three different rules that we have here, since we have this powerful cap rule that checks if an element is within the plan. In a given cell, we just need to move these new coloring rules just below that stop rule and then we can just focus on the right choice of color and color codes.

The procedure now is quite similar, we set this color code to 101 and change the fill to this strong blue, then we set 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 color, we set the color code to 103 and this time we change the font color to strong. blue and that's how easy it is to set up these corresponding coloring rules for a color code family, going back to the default color mode from time to time perfectly demonstrates that all the elements are still in place, the only reason the other stages are not.

What is visible yet is that we have not configured the conditional formatting rules corresponding to their color codes, for now let's externalize this expression to a named calculation that we call color code project structure and then replace this expression in the main formula using that defined name. Before configuring the conditional formatting rules for the remaining color code families, here we will first implement the formula for team role color code generation, because we want the team roles and color mode of the project structure use the same conditional formatting rules and therefore also partially the same color codes so that the whole implementation is lighter and less redundant for the color codes of the team rolls, which means that we will use the same color palette of 8 main colors, but in this context the 8 main colors will represent These 8 team roles can be defined in the configuration worksheet, as we have the team roles shortcodes available in this column, we can easily implement a corresponding role id encoding by searching for the role shortcode in the configuration worksheet, for that let's start by defining a dynamic. name reference for this function column and also dynamic name reference for function id column and then we use the match formula to search the function in this array, make sure to make this an absolute range reference with dollar signs and finally enter a 0 to search for exact matches, this statement will simply return the position of the team role in the configuration team rules list and we can directly use this position as the role ID, as you can see, if this cell of role is empty, this will generate an error n a, so let's add an if. and a statement to capture that arrow and return a -1, then we can add this formula to the entire 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 that yes, it appears that each role is adequately represented by its position in the role type section of the configuration spreadsheet.

I still want to make a further modification to this formula and you may or may not agree with me on this. In my opinion, it makes sense to limit the assignment of team members to tasks and milestones only because these are always connected to real actions, while stages tend to be more abstract concepts used mainly to better structure the entire project and group elements, so my preferred approach in Least for this color-related visualization here is to return only a role id for tasks and milestones , while for stages we will simply return a -1. With this modification you can still assign a team member to a stage if you wish, but this will not be the case. be considered by the automatic coloring engine.

Well, let's use this role id to replace this placeholder with some meaningful calculations. The first thing we do here is check if the role id is equal to -1. You should know that -1 can have several reasons. there is no element defined in the respective row or there is an element which has no team member assigned to it or it is a stage element which means it will be -1 by default, in all these cases we want to apply the default color as an option alternative because this ensures that even if there will be no colors related to the team role, the item will still be displayed and in case we have an actual role ID available, we will create a three-digit color code just like we did for the project structure mode, the numeric value function again. helps us transform a concatenated text string into a numeric value and within this function we concatenate the role id as the starting digit and then use the switch statement to determine the last two digits based on the element type since it is not necessary to display the hierarchy. differences between a scenario and its elements, this time we do not need to use different shades of a color and therefore we simply use the strong tone version of each main color for both tasks and milestones, that means in case the type is a task. we generate a color code that ends in o1.

These are the final digits we use for stage elements in project structure mode, which means we can use exactly the same conditional formatting rules for the indicator color and Gantt character bar display and for the milestones we have to introduce a new color code ending o4 since so far we do not have a final color code that corresponds to both a strong color tone for the indicator color and a strong font color for the marker area. Gantt diagram. Let's close the statement and update all the colors. Code the cells in this column and take a quick look at what we have now.

The initial thing we can notice is that all elements that do not have any team members or roles assigned to them are displayed with the default gray color, so it works correctly when looking at the The visible three-digit color codes 101, which correspond to The tasks assigned to the project management role are the only ones for which we have defined conditional formatting rules so far. It's also obvious that with team role color mode all of these three-digit color codes will be displayed. it only ends in o1 or o4, so let's include this ending o4 in the conditional formatting rules that we already have in place, since we want to have a strong color tone, here we are going to make this rule an o statement that also considers 104 and to the Gantt chart area we simply include this color code in the rule that we have already configured for the particular milestones, so let's make this a declaration or also include the color code 104 with this configuration now all the management elements of projects are automatically displayed in this corresponding strong blue and to show you that this has no negative impact on the functionality of the project structure mode, we quickly changed it here and it still works perfectly fine.

The only thing left for both modes now is the configuration of the conditional. formatting rules for all remaining three-digit color codes that have two through eight as the leading digit. Before we do that, we'll 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 we have here.

Fortunately, that process will be quite simple for the indicator column, we will duplicate these two specific rules seven times and then stack them on top of each other, then we will leave the first set of these two rules as is and jump directly to the second set for which now we are going to adjust the color codes and the color format, this will be for color codes 201 and 204 and the color of choice here is this red tone and in the same way for color codes 202 and 203 we wantTo have this light tone version of that red color to be displayed, we continue this process until we have covered all the color codes up to the code that starts with an 8.

For each new pair of rules we will select a new main color from which we choose the versions of strong and light tones. I'm going to speed up the process a little here to save you some time. Once this process is finished, you will be able to see how the indicator column beautifully displays all the required colors no matter what the stage number is and when we change to the team roles color. mode all assigned roles now have the corresponding indicator color displayed, of course we have to go through this process for the chart area as well, so let's select this range, open the conditional formatting rules 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 time, but it will be worth the effort once we have eight sets, let's jump straight to the second set, start with the first rule, change the leading digit to 2 and then select the corresponding color. it will always be the strong tone version of the respective color for the o1 end, then for the o2 end it will be the light tone version and the strong tone font color version for the o3 and o4 end of the color code once we apply these updated rules, we can already see in the background that the second stage has now become fully visible again in this beautiful colored design and that should give us enough motivation to do this six more times for the remaining color codes.

The only important thing to ensure here. is that the color families used for these rules are consistent between the indicator column and the chart area. Excellent, at this point we have completed the most difficult part of this automatic coloring engine. It took some effort, but the result we can now see is good. It pays off in project structure mode, the entire project structure is now beautifully visualized with different color families for each stage. Any updates to the project structure are immediately reflected in both the indicator and the chart section and with this lightweight design that reuses the color palette over and over again.

Again, each stage has a different color than the stages immediately around it and you can add as many stages as you want, that's really amazing; Then, for the team roll color mode, we were able to reuse the exact same 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 ylin's role from finance to marketing. This update is immediately reflected with a new color for the marketing role, as we have the default color implemented as a fallback option for all items that do not have any team members assigned to them, each item is visible at any time and once we decided to assign a team member, for example here, it will simply update the color with a smooth and amazing transition, which means that now we can to choose between simple and less colorful default color mode, project structure color mode or the team roles color mode with a single click, we will eventually implement the latest color mode that will allow you to highlight elements for which an issue has arisen since the corresponding formula will be based on this issue column, let's make the cells referenceable dynamically as a problem, then let's copy that Unicode problem symbol and start replacing this placeholder with a formula.

We will use an if statement to instantly check if the problem is equal to the symbol and if that is the In case we want to generate one of the two potential color codes, as for the other two advanced color modes, we have only used the one to eight as the initial digit. Now we simply create two color codes which are 901 and 902. 901 will be used whenever the type is stage or task and since we need a separate rule for the font format of the milestone symbols, we create a separate code 902 for these, that is It's that simple and of course we still want to display all the other elements without problems, so again we just use the Default Color Code Calculation as a fallback option, close the statement, update all the rows and the only thing left to do is Add the corresponding conditional formatting rules for the indicator column.

We just need a simple rule that we can use for both color codes. In case the color code is equal to 901 or 902, we want to have a warning red fill here and then just for the chart area, we need to make the distinction between the element types, so we duplicate these two rules here , the first will be for the color code. 901 and just fill the cell with this warning red, while the second rule is intended for milestone symbols, so color code 902 and a warning red font style are perfect now, whenever we switch between the default color mode and the problem color mode, the red color softly covers the underlying default colors and that is just beautiful, finally we just need to take care of the main color code formula at the beginning we transform this expression into a named calculation called problem color code color replace this part accordingly, quickly test if all the color modes still work as required, that looks good and now instead of having this color code formula written in each individual cell of this column and dynamically referencing to each of these cells with the color code name we can simply take this formula and put it directly here to make this a directly named calculation; however, I recommend removing all line breaks and spacing, otherwise it will be difficult to read in the name manager. and then we just copy and paste it here so that the cell content in column a is no longer needed and we can delete it, resize this column to get the original layout of the worksheet back, minimize this formula bar and finally review all the color modes once again.

Time is just beautiful, let's move on to the next amazing feature that will help you take a snapshot of your actual plan, save it as a base plan, and then compare it with the changes you make to your actual plan over time. The crucial idea of ​​this. The feature is that once you have created your schedule with all the dependencies, you can document the date of the recent changes you made and then jump to the base plan section showing these two columns and simply copy the actual plan as simple values, let's decrement . the font size for this and change the font color to this dark gray and once you've taken that snapshot, you should also document the date there so you always know when the snapshot was taken, when time passes and you decide to adjust your plan real as, for example, postponing the start of the project by one week and this decision was made on May 7.

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 them. 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 dropdown which is called display and you will have two list items to select from the first option is simply called plan and refers to displaying only the actual plan while the second item will be called plan versus base which means we want both plans are displayed in the chart area. at the same time, let's select plan for now because that is the default option that is already implemented here, then we create a static name for these cells and we call it just display and of course we will also add some dynamic name references for both the date columns of the base plan, so for the start of the base, as always, we're going to remove the dollar sign here and of course the end of the base based on this start of the base and the end of the base, now we want to create a named calculation that does exactly the same thing for the base plan, as the element in the plan calculation already does for the actual plan, to remind you that the element in the plan calculation simply checks each cell in the area of ​​the graph if the date value in the timeline in the respective column is between the start of the plan and plan and date and then it returns true or false and for the base plan we can set a calculation with similar name, so let's copy this, let's create a new name, call it element in the base, set the scope in this worksheet, paste the formula and now we just override this with the start and end of the base every time we select the plan was a space option here.

Now we want to use this element in the base calculation to additionally display the original base plan here for the milestone symbols we currently generate with the formula from within. the cells this means here now we have to show an additional milestone symbol basically a week before, let's adjust this formula to do exactly that instead of just checking if the type is equal to m and the element in the plan, now we want to do this check of type initially and after that. We add a second if statement to determine if the element is not planned in the current cell.

If that is the case, we will return this milestone symbol regardless of the current display mode because we want the actual plan to always be displayed in both modes. The special aspect to consider now is that in case we are in plan mode versus base mode, we only want to show an additional symbol for the base plan if it is not the same day as the actual plan, so it makes sense to only consider the display mode selected if the element in the plan returns false, in that case we only want to print a milestone symbol for the base plan if the plan was bait mode and the element in the base calculation returns true; otherwise we simply return an empty text string. just like we do in case the type is not equal to m perfect, let's add this formula to all the cells in the chart area and you will see that the base plan milestones are now displayed in addition to the actual planned milestones and we can control if we want hide them. or display them via this dropdown selection just in time these base plan milestone symbols have the default dark blue font color, but we're going to change it to a font color that stands out less.

Let's open the conditional formatting. admin and create a new rule that will directly target these symbols only to apply only to base plan milestone symbols. The formula for this rule has to test multiple conditions that show equal to the plan. If a spatial element in the 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 apply and just apply the plan color real, which means we have to put the element in the plan in a not statement to revert it and eventually of course the type has to be m for the format, we choose the super light gray, which is even lighter than the lightning default color of the actual plan, so no matter which color mode you have selected, these two should always be distinguished once we confirm that it is seen. quite surprising from a visual point of view, it is instantly clear which of the two is the actual active plan and which is the instant base plan to visualize the stages and tasks of the base plan, we will only need to create a conditional formatting rule, let's select the range.

Open the conditional formatting manager again. Here we can see that we need to move this milestone rule down a little bit, just above the actual plan break rule, then we can simply duplicate it and adjust this formula for the base plan stages and tasks for these. We also need to make sure that the item in the base calculation is true, but unlike milestones, we want the base plan stages and tasks to overlap the actual plan stages and task bars, if necessary. You'll see how that can be done in the formatting options in a second for the formula, although this means we don't have to make sure that element in the plan is false, so we can discard that part and eventually we'll change this last condition to a declaration or that checks if the type is stage or task for the format, we change the font color back to automatic, we go to the fill tab and the reason why we can display both the base plane and the real plane in the same cell is that we will use pattern fills for the base. plan, that means that 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 entire cell fill of the actual plan as the color for this pattern fill which I recommend using.this dark gray because that allows us to use this not so heavy dot pattern that says 25 gray and it will still be perfectly visible.

Let's click OK and there we will have the beautiful but still subtle display of the base plane compared to the real plane even for In the darker colors, you can easily identify the part where both the base plane and the real plane overlap, since it is darker or brighter than the rest of the element depending on the specific color palette you use. I recommend playing with the style of the pattern. a little so that these overlapping parts are easy to differentiate from the rest. With the dropdown selection up here, we now have full control over the visibility of this base plan, allowing us to keep the complexity low in the chart area and only show the overlapping parts. base plan when we really need it for a perfect user experience.

I also want to make sure that the timeline automatically adjusts its starting date to perfectly show the full scope of both the actual plan and the base plan at this moment. This initial timeline start date is only based on the actual plan, for that reason the base plan starting on May 7th is not fully visible here to change that, let's start by adding a named calculation that calculates the starting date. start of the project according to the base plan which is simply the minimum value within the base start column using the base plan start date, then we can set up another calculation similar to the schedule plan start calculation, we will call it start schedule base and all this does is take the raw project base start. and calculate the start of the respective week by subtracting its weekday number which will be encoded as 0 for Monday and 6 for Sunday, so this will return the Monday of the week from the start of the base plan now that we have the start of the timeline plan. and the timeline based on defined, now we can make the initial timeline date depend on the display mode, in case the display is equal to the plan was a space, then we have to make sure that the start of the base of the timeline is greater than zero, if that is true, we will return the above between the base start of the timeline and the start of the plan timeline, which ensures that regardless of whether the base plan is ahead or behind the actual plan, the timeline will always adjust optimally in case we haven't taken a snapshot yet, which means the start of the timeline base is not higher than zero, the timeline plans to be used directly even if the plan was a spatial mode are active and in case the selected display mode is not planned versus space, then the start of the timeline plan will be used as before. once we press Enter, you can instantly see how the timeline adjusts perfectly to show the entire base plan.

The base plan starts on May 7th and the timeline starts right at the beginning of that particular week and once we change the display mode back to the plan. the timeline also adjusts accordingly and that way you don't have to actively manipulate the timeline view to have everything visible. In case the base plan section is empty, it directly uses the start of the timeline plan and in case the actual plan is not behind. hat, so start before the base plan, this case is also perfectly covered, this base plan visualization also works completely independently of the color modes, so the only thing left here is to convert this formula from timeline start in a named calculation called timeline start, let's replace this whole formula with the clean and simple timeline start calculation reference and that's it for this function, as a next step, Let's add an intuitive and partially automated way to track progress and visualize it in an attractive and easy-to-interpret way for this.

Let's leave this column at the end of the input section and start by changing the number format to percentage that way we can enter the percentage of completion for each item and it automatically gets assigned the percentage symbol even as we type each percentage of completion that we have entered should be reflected in the Gantt chart accordingly, since we have enabled proquest display to turn proquest display on and off, let's add another dropdown labeled show progress and yes or no as 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 complete value to be accessible so we create a dynamic name reference called percentage complete by taking this percentage complete value then we want to display the number corresponding number of completed business days. in the chart area, so for this stage it would be 60 of 10 business days, meaning we need to highlight the initial six business days as completed to determine for each cell in the chart area if it is part of this time period complete or not. to set up a name calculation that is similar to the element and plan calculation, so let's copy that formula, create a new name called full element, set the scope as usual and paste the formula as we want the prompt bar start from the beginning. of an item we can leave the first condition that ensures that the date in that column is greater than the start of the plan.

What we have to modify is the second condition because instead of simply taking the end of the plan as the upper limit, we want to calculate dynamically. 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 start of the plan and then add the number of days completed workdays to To calculate the number of completed workdays, we simply multiply the percentage completed and the number of workdays required and since the plan started is already one of the days in the time span, we have to subtract one from this product to look at this first stage element called The calculation we just set up should now be true for all six highlighted cells, so we can use it to set up conditional formatting rules for the progress display.

The first rule will target the stage and task elements and we want to highlight a cell as completed business day as long as we have selected yes in the show prompt dropdown selection and the column date is within the time span of completed business days, so incomplete element must be true and since this rule is for stage and task elements the type must be equal s or t, if all these conditions are met we want the cell to be filled with this dark blue color in the manager conditional formatting. Now it is crucial that this rule is placed somewhere on top of the other plane coloring rules because that ensures that this dark blue progress fill will always cover and not be covered by all subsequent rules for greater efficiency, don't forget to set a check Check on the stop option if it is true and then click on OK to see the beautiful result, as you can see, the completed work days are now displayed correctly for the scenario and task items according to the given percentage values, It is important to mention that the cell can only be filled completely or not, meaning that for a work day to be shown as completed, the equivalent of the entire work day must have been completed by then for this task. for example, the second day will only show as completed when we have passed the threshold of 67.

Similarly, the percentage complete value must be at least 50 for this task before any progress can be seen now that we have taken care of the stage and elements of the task, let's focus on visualizing the progress of the milestones that will be really special. First we're going to create a conditional formatting rule for the color and for that we can simply duplicate the progress rule we just set up, move it down and then adjust this last condition to write equal to m for the formatting, we don't want to have a padding of cell, but we want to set the font color to the same dark blue, so now as soon as that percentage value is set to 100, it is now colored as completed, which is great, but to make it even better, let's make the milestone symbol will also transform into this goal flag icon to emphasize that we have successfully crossed the finish line, so let's copy and modify the symbol generation formula as we want. progress is only displayed for the actual plan and not for the base plan.

This is the only place where we will make modifications and instead of directly printing this milestone symbol, we will now ask if show progress is set to Yes and maybe this milestone. it's already complete, so if the element is incomplete, if that's 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 entire range of the Gantt chart and there's the nice completion flag for this completed. amazing milestone when we remove that 100 the symbol transforms perfectly back to the normal milestone symbol and putting it back there will simply cause it to transform again, of course this milestone transformation works for every milestone and the entire progress display is can activate and up, so it's safe to say that the fundamental mission tracking feature is now complete and that gives us a great opportunity to take a closer look at how we can partially automate progress tracking.

What makes the most sense to automate here is the tracking of stages because for most use cases it makes sense for the progress of the stage element to linearly reflect the progress of all elements or at least the tasks covered in that stage to For that purpose, I have prepared another automatic stage placeholder formula up there. Basically, this formula just calculates the sum of the product of the working days and the progress of all the items that belong to this stage and then divides it by the sum of the total working days for all items at this stage.

Once applied, stage progress is now automatically updated every time we change. the progress value for these tasks once all the elements of the stage are completed, the stage is also completed and that saves you a lot of manual work to highlight the fact that the stage progress is linked to other cells that we are going to use the same blue font color formatting that we have already set for several other columns like the weekdays column for example, here we have the respective conditional formatting rule. Let's add this column to the relevant range by typing a comma and selecting the perfect column values ​​and now we instantly see that okay, the progress of this stage is automatically calculated and linked to other cells.

Let's add this automatic stage progress formula to the other stages as well and keep in mind that if you have a different number of items in a stage, you need to adjust the reference ranges. in that formula so that it works correctly in our example case here, where all the stages have the same number of elements, we were able to automate the progress tracking of all these stages in a few seconds with respect to the milestone elements here, there is a thing you should note that with this automatic stage progress formula you will create references to the business day and request cells directly and not to their dynamic name references, which also means that an empty business day cell like the one we have here for the milestone element it will be counted as zero and not one and therefore not affect the calculated stage progress, at least not until we put a real one in there, in my personal opinion that's actually a good thing because milestones are something that should not be measured in working days and are often linked. until the completion of the tasks anyway, so I'll leave the workday cells empty in case you want your milestones to contribute linearly to the stage progress, all you need to do is enter one in that workday cell, Since I just mentioned that the completion of milestones could be tied to the completion of tasks, let me quickly show you how you can do that correctly.

Let's say we want this milestone to be set to hundred percent only in case this task here is completely completed, for that we can write the formula one multiplied by the progress value equals one, this entire 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 onceAfter we press Enter, we see that the Progress milestone is now highlighted in blue, which tells us that this is linked to another cell and with the task one hundred percent completed, the milestone is also completed, but as soon as we just remove the one percent here, the completion of the milestone is set to zero percent of Of course, this concept can also be extended to multiple tasks, for example, so that the completion of the milestone depends on the completion of all elements of the task in The phase.

We can check if the sum of these values ​​is equal to the number of elements in the task and now, whenever any of these tasks. is no longer completed, the milestone completion is immediately reset to zero. I think this concept alone is something that makes this request viewing and tracking feature even more powerful. At this point, we have achieved a huge milestone as all the formulas and conditional formatting rules to visualize the stages, tasks and milestones in any possible way have now been completed, the entire implementation is already quite lightweight, efficient and responsive, but there is still some potential for improvement that you can recognize when we turn the progress display on and off, the progress bars and flags appear and disappear quite quickly. but there is still a small delay of a few milliseconds that we can decrease a lot with a powerful trick and that trick I'm going to reveal to you right now one of the main reasons why right now it takes a little longer for this worksheet. update when, for example, we turn on proquest display is that displaying milestone symbols is a two-step process.

The first step of this process is to print the actual milestone symbols in the cell based on this formula in the cell and then in a second step we apply the conditional formatting rules to give them their respective color. It is not possible to completely eliminate this first step as the cells still need to have some content to display these Unicode icons, but we can simplify the cell content by making the icon definition part of the respective conditional formatting rules for do that, let's copy this default milestone icon, open the conditional formatting manager and scroll right to the bottom to start with the first milestone color rule right now, this rule just defines the color for this. milestone icon, but when we go to the numbers tab and select the custom category, we can apply the same trick that we used earlier for the auto stage start date instead of defining a number pattern, we just paste the milestone icon here right now in the sample section. it doesn't show anything because for this to work we need to have some numeric value written into the cell, which we'll do in a second, but for now let's click OK and we'll instantly see that this milestone symbol is now the default number format applied by this ruler. quickly adjust the formatting for all other milestone rules in the same way, this also includes the issue milestones rule and the one that shows base plan milestones only for the proquest milestones rule, we will need to use the flag icon, so let's copy this one and reopen it. the conditional formatting rules manager and adjust the number formatting for this rule accordingly and that's it right now, the milestone symbols are still generated by this formula in the cells, but since the conditional formatting rules we have created along with this formula they have basically inherited the entire format. logic of when to show which symbol now we can 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 must contain some numeric value otherwise the numeric format The conditional formatting rules cannot be applied to anything, so we add 0 to all cells in the chart area, then set the default number format for all these cells to an empty string to hide these zeros in all positions that they are not milestones and what remains are the milestones. symbols that are generated purely 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 decrease the font size slightly to make them visible and voila everything works as before, but now. the cell content is as simple as a random number, which means that no additional calculations need to be performed on these cells and the impact of this reduction in required calculations can be immediately noticed when we now turn this display option off and on.

Progress on visual updates in the chart area occurs almost immediately without the lag we had before, so it appears this optimization had a significant impact on the performance and efficiency of the worksheet. Excellent to perfectly complement the request tracking function. We will now introduce a feature that allows you to statically highlight a particular day. visible on the timeline or dynamically highlight the current day to put the project progress in perspective for this date highlight feature we want to have a dropdown selection that has all the dates on the timeline and also an option for today available, so let's create a new dropdown selection that has the label highlighted, then we give this dropdown cell the highlighted static reference name and change the background fill to light green this time, unlike the other dropdown selections up there, we want it to This list does not contain fixed elements. static values ​​but to reference our timeline range so that it updates dynamically and always shows exactly those states that are visible since these date cells are spread over multiple rows, we get this warning alert because it can only reference a single row or a single column. but don't worry, we can manually adjust this to only reference the cells in row 10 as this will make all the values ​​accessible.

Let's click OK and take a look at a drop-down selection that we get when you view the full range of dates visible in the timeline from May 10 to July 12 can now be selected here. Let's select June 7 and set the number format to our preferred date style. So far, the dropdown list allows us to select a particular static date that will not change. However, once selected, 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 that 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 extra element part of that reference range at the time we reference these elements in row 10, so why not use this empty cell right here to add an additional value to the list?

Let's simply expand this list to include the w column. and then enter a placeholder text value for the dynamic date highlighted when we now open the dropdown list, today appears as the parent item at the top of the list and that makes a lot of sense from a user experience perspective because most of the time we simply want to dynamically highlight today and only occasionally we may want to highlight a particular static date on the chart. Well, to highlight the selected date in the chart area, we will create a new conditional formatting rule that can work with both dynamic and static. highlighting options we use an if statement to check if the highlight is equal to 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 a actual date, alternatively the highlight has to be one of the static dates, in which case we can directly compare the date and the highlighted value, while the static dates from the dropdown selection will always be one of the visible business day dates on the line of time, today's dynamic date could be on a non-working day like Sunday and in such case we want to highlight the next working day, the next working day can be easily found by applying the working day function by passing today minus a normal day like start date and then adding a business day in In case today is a business day, this expression will have no impact as it will simply jump back and forth to the exact same date, but in case it is a non-working day such as Sunday, will return to Saturday and then advance to the next working day.

It's probably Monday, let's take a look at the format we want to use to highlight a date. We want all of 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 solid line that is placed right at the end of the selected date, then for the timeline date and day of the week up there, we will add a similar line conditional formatting rule that has the same formula so it will also apply to the same selected date but exclusively to these top cells the layout we choose here is a dark blue fill and a bold and green font style, let's adjust the brightness of this green shade for 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 placed right at the end of that highlighted day.

With this setting, we can now switch between any of the visible dates on the timeline. or use the dynamic today option to always highlight the current day whenever the timeline values ​​are updated, for example if we turn on base plan comparison, not only does the highlighted date move correctly with the timeline but also the dropdown selection is updated correctly to now offer us the newly visible Dates on the timeline. I have intentionally chosen this exact design for the date highlight feature because it fits perfectly into the overall color and design theme, visually it is not too overbearing and the most important point is that it perfectly complements the progress display through the dark blue to game.

The color and the end of the date highlighted with that dotted line, this layout allows us to instantly see how many business days we are behind with each item compared to the selected date. Suppose today is June 8th and then we instantly recognize everything behind it. The blue line that is not the same dark blue color is still open, but should be finished by the end of the day in case today is a day later, we instantly see that we are a few days behind at that stage, in a nutshell. It's just an incredibly clean, effective, and easy-to-interpret display feature.

As a final step, I want to show you how to add some easy-to-use scroll buttons to dynamically move the timeline left and right. This will be especially useful if you have a larger project that doesn't fit completely on the screen at once, the way scrolling works for this timeline is actually quite simple: scroll to the right, we just need to add an amount of business days to this initial timeline start date and how we want to control this Number of business days added using the scroll buttons. We need to put that value somewhere on the worksheet.

Let's use this cell here. Put a zero here for now. Give the cell a reference name called scroll increment and then add that value to the start date of the timeline. through the business day function to now intuitively control this number, let's go to the developer tab that 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 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 be placed in the top right corner without requiring too much space to For these buttons to now control the scroll increment number we simply have to bind it to this cell, then we set the maximum value to 50, leave the incremental shift at 1 for now and remove the 3D shading for a cleaner design, that's all What we need to make this timeline scroll right and left using our mouse is great, you can perfectly see how the scroll increment number continuously increases and decreases as long as we hold down the respective button to increase the scrolling speed, we can modify the formula in the cellinitial timeline date or alternatively just right click and format. these buttons to increase the incremental shift value to 5, for example, which basically makes scrolling 5 times faster.

Surprisingly, I eventually recommend hiding the contents of these two cells. The quickest way to do this is to simply set the font color to white and in this we have finally completed the process of creating this amazing Gantt chart template in Excel. What a great result to help you get the most out of this template. Let me give you some additional tips on the best way to add, manage, and even filter elements in this template. template, let's take a look at this four-stage Gantt chart. The most common way to add new items is to simply add them to the bottom and if you know you want to add multiple items, make sure you have an empty row available that is part of the Gantt chart because then you can easily select the entire row and use the Autocomplete feature to add as many new rows to the scan diagram as you want.

All conditional formatting rules and dropdown menus are automatically available for these new rows so you can start adding new items instantly in case you have accidentally used the last row and therefore there is no free row available in a scan chart, just use the autocomplete feature to create an extra row, then remove all the values ​​from the input columns and then you can use it to add as many new rows as you want in case you want to insert an element between other elements, For example, suppose we want to insert another task for this deployment stage.

I recommend using the quick access toolbar up there. the section in your Excel application that by default includes these save, undo and redo commands and most of you may be used to having this located right above the ribbon, but especially for this template I prefer to have that toolbar quick access below that ribbon to make some useful Custom Commands available with a single click. I've already added these four commands that are used for fill, populate, insert rows, and filter, and I can easily add additional ones that could be useful for this use case, such as deleting sheet rows by simply selecting them on the left. here by clicking 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 significantly improve the user experience because now we no longer have to click right here to insert. or delete a row, but instead we can simply press this command to insert a row and then use the fill command to auto-populate the new row with formulas and values ​​from above so 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 prefer to change the milestone to depend on this inserted task, so we drag this reference to 10 and that's it in case If we want to delete this task again, we just have to select the row and use this delete row from sheet command from the quick access toolbar and of course now we have to rebind the milestone so that it comes back to depend on another task in addition to the way we have configured.

This Gantt chart offers many interesting dimensions to filter items for quick and lightweight filtering. I recommend making use of the native filter feature that you can easily include in your quick access toolbar to filter the Gantt chart by a specific column. 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 make your filter selection, for example we can easily create a high level view of the project showing only the stages giving us a compressed view of the full schedule and overall project progress removing the filter is as easy as hitting the filter command one more time, since we have defined a stage id in a column of its own we can use the filter command to display only the selected stages including their task and milestone elements allowing us to have multiple stages that are actually located far apart from each other in a condensed view.

Another great idea is to filter by the assigned team role, allowing you to split the project and not simply base it on individual team members. but instead, it is based on areas of responsibility that also indicate the distribution of tasks between team members with the same role. Two other really interesting columns are these date columns because by applying a filter to one of them you can choose from a large selection of specific dates. filters just to show you one of them, let's filter all the elements as of May 26, it works like a charm and that's it for this tutorial, if you want to download this

ultimate

Excel Gantt chart template, it is available at

excel

find.com.

The link for that is in the description. I hope you enjoyed this tutorial and I would like 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. appreciated and with that said I wish you a beautiful day and until next time, greetings