Suppose you were asked to name the main functions of Excel, what would you say? For sure it will be a worksheet, calculation formulas and graphs for information visualization.
Surely, many Excel users know what a graph is and how to create it. However, one type of chart is still confusing to many users and that is the Gantt chart. In this short guide, we will try to explain to you the main features using this type of charts, and also show with examples how to create the simplest Gantt chart.
In this guide, we will cover the following topics:
- What is a Gantt chart.
- How to make it.
- What are ready-made chart templates.
What is a Gantt Chart
The graph is named after the American engineer who invented it in 1910. The graph displays the task with the help of vertical scales. These scales break down one project into several smaller ones, each with its own start and end date, and also show possible connections between different projects, thus making it easier to track progress.
How to make a Gantt chart
Unfortunately, Excel does not have a built-in template for creating a Gantt chart, however, this will not be a big problem – we can easily solve this problem, just by making a normal graph and formatting it a bit.
We bring to your attention a simple example that will help you create such a graph in the shortest possible time. We will work with Excel 2010, however, this method will also work on 2013, 2016 and 2019 versions.
1. Create a table with the project
We start by entering the necessary data on the worksheet. Each project task must be specified on a separate line.. Структурировать таблицу нужно следующим образом: одна колонка отображает задачи, вторая – дату начала работы, третья – крайний срок, четвертая – длительность (то есть время, за которое проект должен быть закончен).
Clue. To create a schedule, you only need a start date and a duration. If you only have a start date and an end date, you can use a simple formula to calculate the duration:
Duration = End Date – Start Date.
2. Create a normal chart
To start making a Gantt chart, first you will need to create a regular line chart.
- Choose a start date. Make sure you only select cells with numbers. In our case it will be B1:B11.
- Click “Insert” and select a line chart.
- From line charts, we need a “stacked line chart”.
The result should be a graph like this:
3. Add “Duration”
Now we need to add one more value to the graph:
1. Right click anywhere on your graph and click “Select Data” from the menu that appears.
У вас откроется меню для выбора данных. Как вы можете увидеть на примере ниже, «Дата начала» уже добавлена в «легенду». Вам нужно только добавить «Длительность».
2. Click “Add” and select data in the “Duration” cell.
3. You will see the “Row Changes” window. Do the following:
3.1. In “Row name” type the name of the desired column (“Duration”). Or you can just click on the cell with the column name.
3.2. Click on the icon in the Value line.
4. You will have a smaller window. Here you need to enter numerical values from the “Duration” column. You need to do this by selecting the desired cells.
5. Click the “Close Window” button. This will bring back the previous Edit Row window, and then click OK.
6. You will return to the data selection window and see that “Duration” has appeared in the “Legend”. Click “OK” and the new value will appear in your chart.
As a result, you should get something like this graph:
4. Add the title of the tasks to the Schedule
Now we need to display a list of tasks in the graph on the left:
- Right-click anywhere on the body of your graph (the blue bars representing the values) and click “Select Data”.
- A familiar window will open. Here, select “Start Date” and click “Change”, the button is on the right.
- The Axis Labels window will appear. Click on the icon to select a range. In the column with tasks, highlight all your tasks and then click on the same button.
- Click OK.
- Delete the box with the name of your scales.
Your graph should look something like this:
5. Turning a Line Chart into a Gantt Chart
Our schedule is still a regular cumulative one. And so we need to format it in such a way that it looks more like a Gantt chart. First of all, we need to remove the blue scale so that only the scales with “tasks” are displayed. In fact, we won’t remove them – they’ll technically become invisible.
1. Click on any blue bar with the right mouse button. In the menu that opens, select the data series format.
2. A window will appear on the right, where you need to go to “Fill”. Select “No Fill”, then select “Borders” and “No Lines”.
The note: do not close the window, as it will still be needed.
3. You probably already noticed that the “Tasks” in your schedule are displayed in the wrong order. We’ll fix it. Click on the task list of your schedule. You will see a Formatting Axis window. In the options under Axis Options, select Format Reverse. Close and save all changes.
As a result of the changes, we will get the values that are in the right order, and the line with the dates has moved from the bottom to the top of the chart. Our chart is becoming more and more like a Gantt chart.
6. Work on the design of the graph
Even though our chart looks more or less like a Gantt chart, we can still work on it:
1. Remove empty space on the left side of the chart. Where our blue scales used to be, there are now empty spaces, and we need to fix that. To do this, right-click on the very first date from the “Start Date”, select “Cell Formatting”, “General”. In the window that appears, enter the date number, in our case it is 41730. Excel stores dates as numbers that are based on the number of days that have passed since January 1, 1990. If the correct value is already indicated there, click “Cancel” because you do not want to change anything here.
Click on any date in the date row – this will highlight all dates. Press the right button and select “format axes”.
In “Axis Options” change “minimum” to “fixed”. And enter the number that was used above.
2. Customize date numbers in the chart
In the same window as in the previous paragraph, we change the main and intermediate divisions to a fixed one and enter the numbers that will be responsible for the date intervals. Generally, the shorter the project, the shorter the intervals used.
The note: There is no fixed or automatic function in Excel 2013, 2016 and 2019, so the numbers you need must be entered manually.
3. Remove extra spaces between the scales.
If you group your scales a little on the chart, it will look even more aesthetic and clearer:
- Click on any orange scale – this will select all such scales at once – and right-click, in the menu that appears, select the data series format;
- In the menu that appears, find the row options and set the row overlap to 100% and side clearance to 0%.
As a result, we get a simple but beautiful Gantt chart:
But don’t forget that this is only a simulation of the Gantt chart, and such a chart would still support the basic characteristics of line charts:
- The graph will understand if you remove or add data.
- If you change the “Start Date” or “Duration”, the chart may automatically change the values.
- The graph can be saved as an image or converted to HTML for online publishing.
Tips:
- You can customize the graph design to your liking. Add fill, border color, shadows or apply 3D format. All the necessary tools for this can be found in the Data Series Format.
- If you’ve created a cool chart design, you can save it as a template for future reference. To do this, right-click on the chart and select “Save as Template”.
Gantt chart templates
As you can see, creating a simple Gantt chart is not difficult. But, Let’s say you need something more complex, with Project Milestones, Milestones, project readiness percentage display – what then?
Well, maybe you are an Excel master and can make such graphs yourself, but in that case, you would hardly be reading this article.
But here, if you are a beginner, or you do not want to spend too much time creating a graph, The best way is to use templates. Below we will look at some of the most common options.
Gantt chart template for Excel
This template is called the Gantt Project Planner. The graph tracks project progress with values such as Planned Start, Actual Start, Planned Duration, Actual Duration, and also reflects the completion of the project as a percentage.
To use this template, go to the “File” tab, “New Document” and enter “Gantt” in the search bar. If the search did not return results, the template can be found on the official Microsoft website.
Online schedule in Gantt
It is an interactive online tool for creating Gantt charts. This program is easy to use and offers a 30 day free trial. Working with this template is quite easy – just enter your project data in the table on the left, while the system automatically generates a schedule.
Graph templates for Excel, Google Sheets and OpenOffice
These templates can be found on the website. . These are free templates that work with all versions of Excel, Google Sheets and OpenOffice. Templates work on the same principle as regular tables. Just enter your project data and get your schedule.
Gantt Project Management Template
Это бесплатный проект, который помогает отслеживать прогресс проекта по количеству затраченного времени. The project can be followed by choice, either every week or daily, depending on the duration of this project.
We hope that you will like some of the proposed templates. If not, then others can be easily found on the Internet using search engines.