Unlock Efficiency with the Best Progress Billing Template for Planning

Progress billing template for Planning

in this video i'll show you how to make a gantt chart with the progress bar in excel a gantt chart is a bar charts which is used for illustrating tasks that are to be completed over time to create our gantt charts the first step is to set up a project table i've already added a list of tasks that need to be completed in our projects and their starts and end dates the next step is to calculate the duration of each task this is simply the end date minus the start date we can then drag this down to apply the formula to the below cells later on we will use the start dates and duration columns to create our gantt charts i've also already input the progress towards achieving each task as you can see the first three tasks are 100 complete while the fourth one is 70 complete and so on we can use this progress column to identify how many days of each task have been completed this is simply the duration of each task multiplied by the progress now that our project table is set up we can use this information to build our gantt chart the first step is to select our start dates go into the insert tab and then select a stacked bar chart completed the bar charts should look like this next we'll add the duration data to do this right click the charts click select data and then click the add button the series name is duration which corresponds to cell e2 the series values are the values in the duration column which are e3 to e12 finally we can click ok the bar charts will now look like this next we're going to add our project tasks to the y-axis of the charts to do this click edit under horizontal axis labels and once the axis label opens select your project tasks and then click ok the charts will now look something like this next click on any of the blue bars right click and then select format data series we're going to remove the fill color and remove the border line this will ensure that only the duration of the tasks between the starts and end dates is showing you'll also notice that the tasks in the gantt charts are in reverse order compared to the tasks in the project table to overcome this right click on the tasks click format axis and then tick categories in reverse order therefore the tasks are now arranged in chronological order and the access containing the dates has been moved to the top next let's remove the empty spaces on the left and right sides of the charts to do this let's firstly identify which number corresponds to the starts and the end days to do this right click on the start date and then click format cells we can see that the number that corresponds to the start dates is 44 352. we can do the same for the end date here the number is forty four thousand three hundred and ninety three we will use these two numbers when formatting the date axis let's right click the axis and click format axis and then let's input the numbers here so the start date was 44 352 and the end date was 44 393 we can remove the excess white spaces between the bars by right-clicking the orange bars and selecting format data series let's keep the series overlap as hundred percent and change the gap width to zero percents let's also apply a gradient fill to show the percentage completed as a progress bar we will apply error bars to the series we have hidden click on the hidden series and then select error bars followed by more options then select plus for the direction and a no cap end style for the error amounts select the custom option which allows you to specify the values that should be included select the number of days completed column let's now modify the percentages which are showing as lines to make them clearer we can do this by changing the width until it covers our bars we can also change the fill color to green now we can visually see how much progress has been made for each task however let's also add data labels to show the percentage completion to do this click the plus icon and then click data labels and more options we're going to untick value and then we're going to select the existing data labels click label options and instead of value we're going to select value from cells here we're going to select the progress percentages and click ok now our gantt chart is complete we have a clear overview of the tasks that must be completed and how much progress we have made so far please give this video a thumbs up if you found it useful and subscribe to the excel hub for weekly excel tutorials techniques and examples