OmniDocHow to make Gantt chart in Excel? Draw a bar chart using date data
Key points of this article
Key points of this article
- The core data of the Gantt chart are the start date and the duration. When there is an end date in the table, first subtract the start date from the end date to calculate the duration.
- When using a stacked bar chart and a horizontal bar chart, the start date series should be set to no fill, leaving only the duration days as visible color blocks.
- Date is a serial number in Excel, and the starting and ending range of the abscissa must be manually aligned with the early start date and late end date of the project.
- The bar chart places the first item at the bottom by default, and the category order must be set to reverse order, otherwise the order of tasks will be reversed from the table.
Many people have a project schedule in their hands, listing the task name, start date and number of days required, and want to turn it into a timeline that can see the sequence and overlapping relationships at a glance. This kind of chart is called a Gantt chart, also called a horizontal bar chart. Excel itself does not have the function of making a Gantt chart independently, but as long as the data format is correct, it can be drawn using a stacked bar chart, and if the date is changed, the chart will change accordingly.
This article is written for readers who need to make project schedules, thesis experiment progress, activity preparation lists or work plans. You'll see how to organize dates into a structure suitable for drawing, how to set up the chart step by step, and where you can easily make mistakes after drawing. The entire method only uses the charting function that comes with Excel and does not require installing plug-ins or writing code.
How to use date data to make a Gantt chart? One sentence conclusion
The idea of a stacked bar chart is to split each task into two segments. The first segment is a "blank" and the length is equal to the number of days from the start date to the starting point; the second segment is a "color block" and the length is equal to the number of continuing days. Hide the blank segments, leaving only a series of staggered horizontal bars. This is the horizontal bar graph. The following table provides methods based on your actual situation.
| Your situation | Recommended practices | Reasons |
|---|---|---|
| The start date and duration are already in the table | Directly use these two columns to plot the data | No additional calculations are required, fewer steps |
| The table is the start date and end date | First add a column of "duration days", and the end date minus the start date | The stacked bar chart requires the length, not the end point |
| The date is text, for example, the format of "2024.3.1" is not uniform | Convert it to the date format first, and then draw the graph | The text date cannot be included in the calculation, and the graph will be all misaligned |
| There are many tasks and need to be updated frequently | Convert the data area into a table and use a formula to calculate the duration | The chart range can be automatically expanded when adding tasks |
| You only want to show it to one person, and there is no need to modify it repeatedly | Just finish the drawing by hand, focusing on the layout and color | There is no need to spend too much time on the structure of a one-time chart |
Before you start: Confirm these points first
Check the data before taking action. Many "wrong picture" problems occur in this step.
- The date is not a real date. After entering a date into the cell, if it is aligned to the right, it is usually the date; if it is aligned to the left, it is mostly text. Text dates will be treated as 0 or ignored when drawing, and the horizontal bars will be overall misaligned.
- Whether the task names are non-repeating and the order is clear. The vertical axis of the Gantt chart is the task name. Repeated names will make people confused; the order of the tables is the order from top to bottom in the chart. It is recommended to arrange them in advance.
- Whether the caliber of duration is uniform. Some people include the end day, some people don’t. After setting the caliber, use the same algorithm for all tasks, such as "end date minus start date plus 1", otherwise the length of the bars will be inconsistent.
- The starting and ending scope of the project. The abscissa must cover early start and late end dates. Find these two dates first, and you will have a basis for setting the scale later.
- Are there any milestones or completion percentages that need to be displayed. Ordinary bar charts only show time. If you still want to mark the progress, you can add another column of "Number of Completed Days" and use colors to distinguish it, but this will make the chart settings more complicated. It is recommended to add it after the basic version is completed.
Steps to create a Gantt Chart using stacked bars in Excel
- Organize the data area. The first column is the task name, the second column is the start date, and the third column is the duration in days. Do not leave empty columns between the three columns, and make sure the header is clear, such as "Task", "Start Date" and "Duration Days".
- Calculate duration days (if necessary). If the original table only has the end date, subtract the start date from the end date in the new column, then decide whether to add 1 depending on the caliber, and then set the result to numeric format.
- Select the data and insert a stacked bar chart. Select the data in the three columns of task, start date, and duration, find the bar chart category in the insert chart function, and then select the stacked bar chart type. At this time, two colors of horizontal bars will appear in the picture. The first is the segment corresponding to the start date, and the second is the segment corresponding to the duration.
- Hide the start date section. Click on the set of bars in the image that represents the start date and set its fill to None and its border to None. This way it still takes place but is not visible, leaving only a patch of color that lasts for a number of days.
- Adjust the order of the vertical axis. Bar charts place the first row of tasks at the bottom of the chart by default. Open the settings of the vertical axis (category axis) and check the option to sort categories in reverse order. The order of tasks will be consistent with the table, arranged from top to bottom.
- Set the starting and ending range of the abscissa. The default abscissa scale is the serial number number, not the date. In the coordinate axis settings, set the small value to the serial number corresponding to the early start date of the project, set the large value to the serial number corresponding to the late end date, and then change the number format to date, and the scale will be displayed in the form of "3/1".
- Beautification and finishing. Delete the unnecessary legend, add a title to the figure, and set the color of the duration to a unified main color. If there are many tasks, you can increase the height of the chart appropriately so that each horizontal bar has enough space.
Check each item after completion
Don't rush to deliver the drawing after it's finished. Check it item by item.
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Is the position of the horizontal bar correct? | Find a task and compare its left end position with the start date in the table | Check whether the start date is in text format and redraw the graph after conversion |
| Is the length of the horizontal bar correct? | Compare the duration of days to see if the right end of the horizontal bar corresponds to the correct end date | Check the formula and caliber of the duration column to see if 1 needs to be added |
| Whether the order of tasks is consistent with the table | Check whether the top row is the first item in the table | Open the vertical axis settings and confirm that the option to sort categories in reverse order is checked |
| Whether the abscissa range covers all tasks | Check the left and right scales to see if they include early start and late end | Adjust the small and large values, and change to a larger scale unit if necessary |
| Whether the hidden segment is really invisible | Check whether the start date segment still has a color or border | Select the series individually and set the fill and border to none |
| Is the chart synchronized after data modification | Change a start date and see if the horizontal bar moves | If the range is not extended, check the data area referenced by the chart |
📊 Core comparison
There are three common ways to make horizontal line charts, each with its own emphasis, and you can choose according to your needs.
| Method | Suitable for scenarios | Main advantages | Main limitations |
|---|---|---|---|
| Stacked bar chart | There are not many tasks, and the timeline needs to be automatically generated using dates | The graph will be updated after the data is changed, no manual dragging is required | Additional information such as progress and milestones require additional settings |
| Cell coloring | There are many tasks and I want to see the time distribution directly in the table | No need to set up a chart, it can be done with conditional formatting | The grid granularity is fixed, and scheduling down to the hour is inconvenient |
| Manually draw shapes | Only make one-time presentations | The appearance is free, you can add arrows and annotations | Manual modifications are required after the date changes, and errors are prone to occur |
❓ Frequently Asked Questions (FAQ)
1. What should I do if the abscissa displays a string of numbers, not a date?
This is because Excel dates are essentially serial numbers. Select the date format in the number format of the coordinate axis, and confirm that the small value and the large value are the numbers corresponding to the date, and the scale will display normally.
2. The order of tasks is opposite to the table, and the tasks below are on top instead?
This is the default behavior for bar charts. Open the vertical axis category settings and check the option to sort categories in reverse order. Note that the abscissa may move to the top of the graph at this time, and you need to adjust the position of the abscissa back to the bottom.
3. My date was copied from a web page or PDF, but the image cannot be displayed. What should I do?
Dates copied from web pages often have hidden formatting that turns into text when pasted. You can first set the date column to the date format, then re-enter or use the column separation function to convert, and then draw the graph. If the original schedule is only a PDF file, you can use the desktop client's PDF editor to export it to Excel, and then process it according to the above steps.
4. Is there a way to display today’s location on the map?
Normal stacked bar charts do not directly support this feature. A common approach is to add an additional auxiliary series and make it a vertical line. This requires some experience in setting up charts. If you only view it temporarily, you can mark today's date separately in the table, or manually confirm it every time you open it.
5. There are English task names in the table. If I want to change them to Chinese, will the pictures change accordingly?
The chart will reference the content of the cell. If the task name is changed, the vertical axis label in the chart will be updated accordingly. If you are translating the entire form from English to Chinese, you can first use the document translation function to process the Excel file, and then check whether the task name and date remain the same. It is recommended to check the results of machine translation, especially professional terms, one by one.
📝 Summary
The key to making a Gantt chart in Excel is to organize the start date and duration into clean number and date formats, and then use a stacked bar chart to hide the start segment, leaving only the duration segment. If you do these three things correctly, organize the data, set the reverse order, and set the abscissa range, most bar graphs will appear normally. Afterwards, as long as the date is updated, the chart will also be updated accordingly, which is suitable for scenarios where the schedule needs to be adjusted repeatedly.
