A common requirement in business is creating a simple Gantt chart in Excel (chart that shows the start and stop dates of certain activities with their overlaps). The trick with creating the Gantt chart is understanding what the data needs to look like as the chart itself is a simple bar chart.
Simple Gantt chart in Excel (YouTube- one of 4 methods)
Data set up for gantt chart in Excel
Below, in the top block, is what we are trying to show. Set Up starts on the 1st June and goes for 10 days. Print starts on the 12th June and goes for 7 days etc.
For the chart data however, we need to create a new block of data. Same descriptions but notice that we have a column called Blank and one called Filled (you can change these names). The Blank shows the start date and the Filled shows the end date of each activity, which is achieved by creating a formula that is equal to the start date plus the duration.
Now set up a Bar Chart based on this new block of data
Just to make things easier and understandable we should change the axis to start at our start date (1 June)
If you look at the red bar, what we want to see is it hovering at the correct start point (we don’t want to see the blue bar- it must disappear). So the ‘Set Up’ is correct, but the next step (Print and Distribution) should start at 12 June. To make this appear to float, we can make the blue bar overlap the red bar and change it to white to blend in with the background.
You will need to make the series overlap (format a series), and depending on your column order you may need to change the order (right click on the chart and choose Select Data).
We are almost there. Now if we just change the blue column to be white and play with some of the others setting we can get to this
You can now change the dates in the data block and your Gantt chart will update.