Wright & Brown

Karl Wright: journalist, editor, copywriter

  • Home
  • About
  • Work
  • Blog

Create an auto-updating Gantt chart in Excel: part 2

27th October 2016 By Karl Wright Leave a Comment

An auto-updating Gantt chart created in Excel

In part one of this post, I covered how to use formulas and conditional formatting to create an auto-updating Gantt chart in Excel. Now, let’s find out how to add different coloured bars to your Gantt chart for each task owner and how to stop tasks starting or finishing on non-work days.

1. Change the formula that powers the Gantt chart
In part one, we used this formula to power our Gantt chart:

=IF(AND(F$2>=$C3,F$2<=$D3),”yes”,”no”)

For an explanation of what this does, see step 5 in the first part of this article. In this step, we want to change that formula so that instead of returning two states — ‘yes’ or ‘no’ — it can return as many different states as there are taskholders.

We have three task owners: the content team, the localisation team, and the client. To display a different coloured bar for each task owner, the cells in the Gantt chart need to be able to check the correct cell in column B — where task owner is specified — and display a value that corresponds to the contents of that cell.

To make that happen, enter this formula into the cell:

=IF(AND(F$2>=$C3,F$2<=$D3),IF($B3=”Client”,”Client”,IF($B3=”Content team”,”Content”,IF($B3=”Localisation team”,”Localisation”,”yes”))),”no”)

This looks complicated, but it just tells Excel to check cell C3. If the value in cell F2 (the date at the top of the column) is equal to or greater than the value in C3 (the task start date) and equal to or less than the value of cell D3 (the task end date), then the cell F3 should display one of these three values:

  • “Client”, if that value in cell B3 is “Client”.
  • “Content”, if the value in cell B3 is “Content team”.
  • “Localisation” if the value in cell B3 is “Localisation team”.

Cell B3 contains the name of the owner of this task. As you can see from the screenshot, the cell into which we’ve entered the above formula now displays the word “Client”.

The next thing to do is, is copy this formula to the whole of week one. Look at the screenshot below, and you’ll see that this gives us our functioning Gantt chart. But it’s a bit hard to read.

2. Update the conditional formatting

Auto-updating Gantt chart in Excel: Conditional Formatting pop-up

This step’s relatively simple. Select the cells in week one of the Gantt chart, then on the ribbon toolbar click the arrow next to Conditional Formatting. From the menu, choose Manage rules.

In the box that opens, select New rule.  Click on the second item in the list, Format only cells that contain.  Under Edit the Rule Description, set up the following rule:

Format only cells with: Cell Value equal to Client

Enter the word “Client” in the box at the end of the condition you’re setting up, as it is in the screenshot below.

Click on the button labelled Format and set the Font and the Fill to the same colour: we chose dark blue with the RGB value of R22, G54 and B92. Repeat the process by adding two new rules, one for the word “Content” and another for “Localisation”, choosing a different colour for each. 

When you create your own spreadsheets, you’ll need to add as many conditions as you have task owners. In Excel 2003 you can only create three conditional formatting rules, in more recent versions you can have as many rules as you like.

3. Stop tasks starting or ending on a non-work day

Exclude non-workdays from your auto-updating Gantt chart

Setting up the Gantt chart to ignore non-workdays is relatively easy. Click in the start-date of task two, in your table of tasks, and replace that formula =D3+1 with this:

=WORKDAY(D3,1)

Instead of starting on the day after task one ends, task two will now start on the workday after task one ends. So if task one ends on a Friday, task two will start on a Monday. Copy this formula to all the cells in your column labelled “Start date”, except the first one which contains the start date for task one.

Now click in the cell containing the end date for task one. In our sample sheet, that’s cell D3. Replace the formula =(C3+E3)-1 with this one:

=WORKDAY(C3,E3-1)

Copy that formula to all the cells in your ‘End date” column.

This tells Excel to calculate the end day of the task by adding only working days to the start date. So if your task starts on a Friday and its duration is two days, instead of having it finish on a Saturday the chart will show it finishing on a Monday.

As in part one of this article, you have to include the “-1” in the article, otherwise Excel calculates the duration as if it doesn’t include the start date. The Gantt chart would, for instance, show a one-day task as lasting two days.

4. Create a list of non-standard holidays

Teach your auto-updating Excel chart to exclude non-standard holidays from task durations

So far, so good. But what about all the non-standard holidays your team and your client take? Or what if you work in a country in which the weekend doesn’t fall on Saturday and Sunday?

The workday formula allows for this. Create a second worksheet in your Excel workbook. In ours it’s called “Holidays”.

Select the first column in this spreadsheet, right click and from the context menu choose Format Cells. From the list on the Number tab, choose one of the date formats. This tells Excel that the values you enter in to this column are dates. Click OK to exit the dialogue.

Now enter all the dates for all custom non-work days, days you don’t want included in a task’s duration, into this column. Your national holidays, your annual leave, your company’s team-building days — enter the dates of all of them in this column.

It’s probably a good idea to format the selection of cells you plan to dates into: the first hundred or so cells in the column should do. This lets the user know if they’re going outside the active area: you don’t want them entering dates anywhere and then wondering why the Gantt chart can’t see those dates.

5. Now make your Gantt chart aware of those non-standard holidays 

Configure your Gantt chart to take non-standard holidays into account

Back on the worksheet containing your Gantt chart, click in the cell containing the end date of task one. It contains the formula:

=WORKDAY(C3,E3-1)

But the workday function allows us to specify holidays, in the following format:

=WORKDAY(start_date,number_of_days,holidays)

All we need to do to make our Gantt chart not schedule tasks on custom non-workdays, is to point our workday formulas at the column of holiday dates we just created.

In the ‘end date’ cell for task one (cell D3 in our sample worksheet), click into your formula and move the cursor so that it sits right before the closing bracket and type a single comma.

Now click on the Holidays tab, click in the first cell in your column of dates and drag to select the entire formatted area of the column. Press enter, which will return you to the worksheet which contains your Gantt chart. Your formula will now look like this.

=WORKDAY(C3,E3-1,Holidays!$A$2:$A$101)

 Make sure you add the dollar signs in front of the column and row values to stop the cell values in your formula changing as the formula is copied into other cells.

Select the cell you’ve just updated and drag down to copy your new formula to all the remaining cells in your “End date” column.

Now, in the same way, add your list of holidays to the formulas in your “Start dates” column. Remember, don’t update the cell in which you enter the start date of your project. You always need to enter that manually.

6. Protect your work

Protect your Gantt chart from muppets

At least one of the people you send this to will flail at the keyboard with their fists, trying to colour in the cells manually. In the process, they’ll break the sheet.

This is bad. Not only are they wasting your time and making your life more complicated, there’s also the cost of the hitman and, you know, trying to dispose of their body, and all the complications that go with that. So let’s avoid that, by protecting the sheet.

Click on the very top cell in the grey sidebar, between the number 1 and the letter A. This will select all the cells in the worksheet. Right click one of the select cells and choose Format Cells. On the Protection tab, make sure that the box labelled Locked is ticked and then click OK.

Left click anywhere on the sheet to deselect all the sheet’s cell. Now, we have to unlock the cells you want the user to be able to edit. These are:

  • All the cells in the first column; the one labelled Task name.
  • The cell in your table of tasks that contains task one’s start date (C3 in our sample worksheet).
  • All the cells in the Task duration column (column E, in our sample).

Select these cells, all together or one at a time. Right click, choose Format Cells from the context menu, and on the Protection tab this time make sure the box labelled  Locked is unticked.

Finally, on the ribbon tab named Review, above the words Protect Sheet you’ll see a grid icon with a padlock superimposed upon it. Click on this icon, then click on OK in the box that pops up.

And that’s it. Your worksheet is protected.

And that’s all folks…

That’s pretty much it. You have an Excel sheet containing a Gantt chart that auto-updates whenever you change either the start date or the duration of a task: no need to manually colour in blocks of cells in Excel, every time a deadline changes.

Filed Under: Project management Tagged With: howto, microsoftoffice

Grep! I’m in a bind

27th May 2016 By Karl Wright Leave a Comment

Use tail and grep to query your Bind server
‘Grep’ always reminds me of a made-up swear word from a comic book, like Judge Dredd’s ‘drokk‘!

Occasionally, I want to check and see what the kids are up to online. I don’t have a content filter set up yet (it’s on the to-do list) but we run Bind as a local-network domain name server (DNS). To see what someone’s doing, I just use the tail and grep utilities to view Bind’s log files.

[Read more…]

Filed Under: Linux Tagged With: howto, ubuntu

Create a Gantt chart in Excel: part 1

27th May 2016 By Karl Wright Leave a Comment

TL;DR: this walk-through shows you how to create an Gantt chart in Excel that auto-updates when you change the start date or task duration. Just want the finished chart and instructions on how to customise it? Click here.

For a while, when I was working as a content manager on the HP account, we used Microsoft Project. I loved it and so did the rest of the team. But none of our clients used it. We got used to them asking for “something like this, but in Excel”.

So that’s what I created. I didn’t have the patience to manually colour rows of cells, only to change them all when the start date or task durations changed. So I used formulas and conditional formatting to create a sheet that would auto-update if you changed either of those values. [Read more…]

Filed Under: Project management Tagged With: howto, microsoftoffice

Watch video over your network

27th May 2016 By Karl Wright Leave a Comment

Media sharing with Windows Homegroups

Want to watch videos or listen to music over your network, without having to copy files from one PC to another? If your PCs run Windows 7, Windows 8, or Windows 10, then you can do it easily.

From your Start menu, open Windows Media Player. In the Windows Media sidebar, you’ll see Other libraries. This lists all Windows User Accounts in your home network. Click on one of the names to see what that person is sharing and play or view any files they’ve made available.

If you have a user account on more than one PC, a desktop and a laptop for instance, you can play your music collection over the network without having to go to the trouble of copying the files.

For this to work, your PCs all have to be members of the same Windows Homegroup.

Filed Under: Windows Tagged With: homegroups, howto

How to setup a Windows Homegroup

27th May 2016 By Karl Wright Leave a Comment

Homegroups are Microsoft’s way of making it easy for normal people, who just want their PCs to work, to share files over their home network. They were introduced with Windows 7 and every time you install Windows it nags you to set one up.

Ideally, it’s better to use a low-power network-storage device to share files. This stays on all the time, so you don’t have to go and boot two PCs in order to get to the files you want. But if that’s not an option, Homegroups are an easy-to-use alternative.

1. Open Control Panel
From the Start button, open the Windows Control Panel. Click on the button labelled Create a homegroup.

Set up a Homegroup: step 1 [Read more…]

Filed Under: Windows Tagged With: homegroups, howto

Got a question?

    Then email me.    

Recent Posts

  • Mindshare: awards entries for award-winning campaigns
  • The Rubicon Agency: AT&T in the US education market
  • PPRO: Politics, commerce and payments
  • Create an auto-updating Gantt chart in Excel: part 2
  • Grep! I’m in a bind

Topics

customerexperience enterprisemarketing homegroups howto magazines marketing microsoftoffice techmarketing ubuntu

Recent Posts

  • Mindshare: awards entries for award-winning campaigns
  • The Rubicon Agency: AT&T in the US education market
  • PPRO: Politics, commerce and payments
  • Create an auto-updating Gantt chart in Excel: part 2
  • Grep! I’m in a bind

Copyright © 2023. Karl Wright & Karen Brown. All rights reserved.