How to Create Gantt Charts in Excel

 Microsoft Excel remains the go-to option for many businesses to perform data calculations and create charts based on the results.

Doug Bonderud

The Gantt chart is a useful guide that isn’t naturally available in Excel. Great for project management and milestone tracking, Gantt charts can help companies better visualize operations and streamline current processes.

In this piece, we’ll dive into the basics of Gantt charts and explore their benefits, then provide a step-by-step guide to creating them in Excel, along with some useful examples. Before long, you’ll be able to use Excel like a pro.

What are Gantt charts?

On the left-hand side, the Y-axis of the chart lists specific activities. At the top, the X-axis of the chart shows time.

There’s no fixed unit for time — it could be measured in weeks, days, hours, or minutes, depending on the tasks you’re tracking. In Excel, each task gets its own row.

As you can see in the example above from Microsoft, task 1 starts at time unit 0 and runs until just before task 2, which proceeds until task 3 begins.

It’s worth noting that tasks can overlap — the purpose of the Gantt chart isn’t to determine the order of tasks but to provide an easy way to see what’s happening, when, and how many processes are happening at once.

The Benefits of Gantt Charts

Gantt charts offer several benefits, including the following.

At-a-Glance Project Progression

Gantt charts let you see when projects began, how far along they are, if they’re reached specific milestones, and if they overlap with other projects.

This makes it possible to better understand how long processes will take and if concurrent processes may cause friction.

Actionable Insight

These charts also provide actionable insight for project management staff. Because teams can see project start dates and timelines at-a-glance, they can pinpoint potential bottlenecks and make changes.

In practice, teams might discover that three processes are due to start on the same day. By staggering start dates slightly, they can avoid possible performance issues.

Improved Time Management

Consider a Gantt chart showing multiple tasks with the same start date and team responsible for them. If left alone, this project framework could waste time, as one team is overworked, and others may not have enough on their plate.

A better understanding of what’s happening, when, and why can help companies improve their time management.

Reduced Risk of Resource Overload

Projects share a pool of finite resources. The more projects happening simultaneously, the bigger the resource drain and the greater the risk of resource overload.

Gantt charts offer a way to compare process resource needs and make adjustments that help keep projects on track.

While every Gantt chart differs, common features include task, taskbars, and milestones due dates. Charts may also include sub-task bars or use bar shading to indicate how far a task has progressed.

How to Create Gantt Charts in Excel

Despite their usefulness and ubiquity, Gantt charts don’t have a built-in template in Excel. As a result, users need to either download a pre-built Gantt chart or build their own.

Here’s a step-by-step guide to creating a Gantt chart in Excel.

1. Enter your project data.

The first step in creating a Gantt chart is entering project data into a typical Excel spreadsheet. In our example, we will enter three data columns: Task Name, Start on Day, and Duration. The result should look something like this.

2. Make a stacked bar chart.

Next, we’re going to create a stacked bar chart. To do this, select the data in the Start on Day column (with the header). Then, under “Insert,” select “Bar Chart”, then “2-D Bar”, then “Stacked Bar” to get this.

3. Enter your duration data.

To enter duration data:

• Start by right-clicking on the chart.
• Choose “Select Data.” This will open a “Select data source” window containing the “Start on Day” series.
• Click the “Add” Button under “Legend entries (series),” then name your new series Duration.

Now, click the icon next to where it says “Series values,” which will open a new “Edit series” window.

Select the data from your Duration column (without the header) and your Start On Day column, then click OK to close the window, and click OK again to add the series to your chart.

4. Add your task descriptions.

Now we’re going to add task descriptions. This starts the same way as the last step: Right-click on your chart to open the “Select data source” window, then select “Start On Day” in the left-hand “Series” list.

Next, select “Edit” on the right “Category” list. This will open an “Axis labels” window. Select the cells in your Task column, then click OK on the “Axis labels” window, then OK on the “Select data source” window.

4. Fix the Order

Finally, we’re going to fix the order of our tasks. To do this, right-click on the list of Tasks on the left side of the chart to open a menu, then click “Format Axis.” From this menu, under “Axis Position,” check the “categories in reverse order” box.

And there you have it — a custom-built Gantt chart in Excel.

Gantt Chart Examples

If you prefer to download an existing template, there are multiple Gantt options in the Microsoft chart templates database. Some great Gantt examples include:

Date Tracking Gantt Chart

This Data Tracking Gantt chart template is completely customizable with your own images, titles, and text. It offers an easy way to track key tasks without having to build your Gantt chart from the ground up.

What we like: This Gantt chart is simple and streamlined. It has plenty of room for tasks, start dates, and durations to help you better manage tasks.

Simple Gantt Chart

This Simple Gantt chart is exactly that: Simple and easy to understand. It can also be tailored to different user groups, such as employees, managers, and stakeholders. You can include photos, graphics, or new fonts.

What we like: In addition to basic task information, this chart also adds the ability to break projects down by phases (denoted by the different-colored sections) and who is responsible for each task.

Agile Gantt Chart

This Agile Gantt chart example provides color-code task categories and visual reminders to help Agile teams meet deadlines.

Given the ongoing feedback loop that underpins Agile methodology, Gantt charts such as this are instrumental in task tracking.

What we like: The color-coded bars provide immediate visual feedback, while the ability to quickly publish and share this chart in Excel helps improve team collaboration.

Going the Distance with Gantt

Gantt charts are a great way to track project schedules and see at-a-glance where overlaps occur and where it may be necessary to make schedule changes.

While Microsoft Excel doesn’t come with a built-in Gantt generator, you can go the distance with Gantt charts by building your own from scratch with our step-by-step guide or downloading an Excel template that lets you customize data and headings on demand.

https://blog.hubspot.com/

Activity relationship chart. Part 1

 

An activity relationship chart (ARC) is a tabular means of displaying the closeness rating among all pairs of activities or departments.^[1] In an ARC there are six closeness ratings which may be assigned to each pair of departments, as well as nine reasons for those ratings (each is assigned by a reason code).

Rating symbols

1. A: Absolutely necessary
2. E: Especially important
3. I: Important and core
4. O: Ordinary
5. U: Unimportant
6. X: Prohibited or Undesirable ^[2]

Reason codes

1. Same table
2. Flow of material
3. Service
4. Convenience
5. Inventory control
6. Communication
7. Same personnel
8. Cleanliness
9. Flow of parts^[2]

A rule of thumb is used to restrict the choice of rating letters:

• Very few A and X relationships (no more than five percent) should be assigned
• No more than 10 percent should be E
• No more than 15 percent should be I
• No more than 20 percent should be O
• About 50 percent of the relationships should be U

Developing an ARC

1. List all the departments within the facility, and draw a rectangle around each one.
2. Draw a rhombus between each department, until you fully construct the rhombus as a tree.
3. Divide each rhombus into two halves; the upper half will contain the rating letter, while the lower half will contain the rating-reason code.

References[edit]

1. ^ Groover, M. P. (2007). Work Systems: The Methods, Measurement & Management of Work, Prentice Hall, ISBN 978-0-13-140650-6
2. ^ Jump up to:^a b Tompkins, J. A., White, J. A., Bozer, Y. A. (2010). Facilities Planning, Wiley, ISBN 978-0-470-44404-7

How to Create Activity Relationship Chart in Excel

Are you an Industrial Production Engineer? Or do you have an interest in supply chain, system, and production engineering? Then, fasten the seat belt because there will be a bang now. In this article, we’ll demonstrate 4 easy and detailed steps to create an Activity Relationship Chart in Excel. So, let’s go through the article to perform the task efficiently in your work.

Introduction to Activity Relationship Chart

Designing the arrangement of the amenities that already exist on the production floor in order to maximize their productivity is known as facility layout. These resources could be tools, personnel, spaces, etc. For increased output, all of the assets must be appropriately organized on the shop floor. The foundation for creating any form of plant layout is an Activity Relationship Chart. For this chart to provide a useful plant layout, extreme care must be taken in its design.

---

What Is Activity Relationship Chart?

An Activity Relationship Chart is also known as ARC in short. It’s in a tabular form to express the closeness rating between all sets of activities or departments. Each pair of departments in an ARC may be given one of the six closeness ratings, with nine justifications for each rating (each is assigned by a reason code).

---

Closeness Rating and Basis of the Coding in Activity Relationship Chart

The relationship chart shows which entities are connected to one another and assesses how important that connection is. The ratings and their closeness are given below.

RATING	CLOSENESS
A	Absolutely necessary
E	Especially important
I	Important and core
O	Ordinary
U	Unimportant
X	Prohibited/Undesirable

Also, there are some reasons behind this closeness. We express them through number codes. So, you can see them below.

CODE	REASON
1	Flow of Material
2	Ease of Supervision
3	Common Personnel
4	Necessary Contact
5	Noise
6	Similar Pieces of Equipment

---

Importance of Activity Relationship Chart

The primary goal of the Activity Relationship Chart is to guarantee that the facility you are developing has the shortest possible distance between two pieces of equipment or departments that are crucial to one another. If the prior activity hasn’t been completed, no other activities can be performed. Imagine if you assigned a metal object to paint first, then welding, as opposed to welding first, then painting? Therefore, putting facilities closer together and shortening cycle times are the main goals.

4 Steps to Create Activity Relationship Chart in Excel

In this section, we’ll learn the 4 basic steps to create an Activity Relationship Chart in Excel. So, let’s go through them one by one.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience. Don’t forget to inform us if you face any trouble while using any other versions of Excel.

---

Step 01: Create Basic Outline

In the first and foremost step, we should create a basic outline where all the elements of the chart can accommodate themselves. It’s so simple and easy also. So, let’s see it in action.

📌 Steps:

• At the very beginning, create a table in the B4:M14 range.
• In cell B4, we wrote down the text Departments.
• Besides, we can see 1 to 10 in Column B and in Row 4. These are the serial number of the departments which we’ll input in the next step.
• Also, we have left the D5:M14 range blank for future usage.

Step 02: Disclosure of Department Names

In the next step, we’ll insert the names of the departments of the facility. Here, we’ll use 10 different departments in our sheet. So, without further delay, let’s dive in!

📌 Steps:

• At this time, write down the name of the departments one after one in the C5:C14 range.
• In this case, we adverted the Fabrication, Welding, Paint, etc. departments.

Step 03: Construct Cell Borders

Now, the main part of embellishment comes. We’ll modify the blank spaces in the table to create the Activity Relationship Chart. Let’s see the process in detail.

📌 Steps:

• At first, select cells in the D5:M14 range.
• Then, go to the Home tab.
• After that, click on the Borders drop-down on the Font group of commands.
• Later, select All Borders from the list.

Currently, the worksheet looks like the following image.

• Again, select the previous range.
• Additionally, repeat the previous steps to open the Borders drop-down list.
• But, this time, select the More Borders option at last.

Immediately, the Format Cells dialog box appears before us. Automatically, it will take us to the Border tab.

• Then, apply the diagonal border as shown in the image below.
• As usual, click OK.

Presently, the final result is before our eyes.

Step 04: Give Closeness Rating and Reasons in Each Cell

In this last step, we’ll do the technical part of this chart. Before starting, the dataset is purely fictitious and used only for your understanding. So, let’s get started.

📌 Steps:

Before inserting the data, we’ll restrict some cells. Because no data will sit in them. To do this,

• Firstly, select the cells along the diagonal in the D5:M14 range.
• Secondly, proceed to the Home tab.
• Thirdly, click on the Fill Color drop-down icon on the Font group.
• Afterward, select Black from the Theme Colors.

Moreover, the final look is like something below.

• Now, select cell E5 and write down the following text in that cell.

O     3

There are 5 blank spaces between these two letters. We did it intentionally to accommodate these on both sides of the diagonal border in the cell.

• After that, press the ENTER key.

• Similarly, fill up the other cells with correct data.

So, If you notice, you’ll see that the data is symmetrical along the corners on both sides of the black cells.
If you want to find the relationship between the Welding and Receiving departments, how could you do that? It’s so easy.
The serial numbers of these two departments are 2 and 5 respectively. So, you will find the result at the intersection of 2 and 5.

Therefore, 2 and 5 intersect with each other two times. In cells E9 and H6, values are the same. So, the relationship between these two departments is A1. That means their closeness is absolutely necessary, and the flow of material will also happen between them.

https://www.exceldemy.com/wp-content/uploads/2022/11/Creating-Activity-Relationship-Chart.xlsx

 

Shahriar Abrar Rafid