Automatically Format the Colour of Task Bars in Gantt Chart

I am often asked in Microsoft Project courses how to automatically change the colour of the task bars in a Gantt Chart dependent upon the tasks resource, or location.

The truth is that this can be a complicated process, but once it is set up it is good to go. Save your view and you will never have to worry about it again.

In this example I plan to format the colour of the task bars by the resource group. I want the colour of the bars to indicate the team that are working on that task. When someone assigns a resource to a task, Project will automatically format the task bar colour in the Gantt Chart.

Continue reading

Sum Every Nth Row in a List

At some point you may need to sum the value from every nth row in a large list. Excel does not provide a function for this. Excel has a few different Sum functions, but not one to sum the value from every other, or every third, fourth, or fifth row in a list.

The spreadsheet below contains totals in every fifth row starting from row 3. We want to only add these sales totals.

Continue reading

Create a Scrollable Chart for your Excel Dashboards

Creating a scrollable chart is a great trick for enhancing the functionality of your Excel dashboards. You may have years of data to display in the chart, and yet space is limited on your spreadsheet.

By adding a scroll bar to the chart, users can interact with the chart and scroll to see the data they want displayed.

Continue reading

Create a Histogram in Excel

A Histogram is used in statistics to graphically represent the distribution of data. It looks like a column chart with each column representing an interval (bin), and the column height representing the frequency that it appears.

Essentially the graph groups numbers into intervals (bins) and displays how often they appear. The graph then beautifully illustrates how the sets of numbers are distributed.

Continue reading

Automatically Create a Table of Contents in Excel

If a workbook contains many sheets you can create a table of contents to make navigating to the sheets easier. This is a fantastic idea when producing a final version of a report in Excel for a customer.

Excel does not yet contain a feature that produces a table of contents, but you can create a macro to get the job done.

Continue reading

Sum a Column Ignoring Formula Errors

Having formula errors on a spreadsheet can be bad news. They look ugly, they frighten the less Excel experienced among us and they stop our other formulas from working.

If you have formula errors on a spreadsheet it is normally best to stop it at its source. To either correct the error, or to hide it using formulas such as ISERROR and IFERROR.

However, if your spreadsheet is large, having these IFERROR functions in every cell to protect against error values will add more calculation time to your spreadsheet.

There is a function in Excel called AGGREGATE which allows us to perform various functions on a range whilst ignoring formula errors.

Continue reading

Create a Drop Down List Field in Microsoft Project

Microsoft Project contains many fields for storing information about the tasks and resources of your project. So much so that it can be hard to imagine what else you would want to know.

However there is usually a desire to create your own custom fields to display other information in your different views and reports. You can create your own fields to store text, dates, cost and other types of data.

To assist data entry, a field can be created with a drop down list. You can look up the value that you need in a list making data entry easier and more accurate.

Continue reading

Type Special German Characters in Word/Excel

I have started to learn German as it has always been a goal of mine to learn the language. So I started to list some vocab in Excel to test myself.

I am using an English keyboard and the German language contains 4 special characters that do not appear on an English keyboard. You have the 3 umlaut accented characters ä, ö, ü and the ß (esszet).

There are two main ways to enter these letters into Word or Excel. One way is to insert the symbol, and the other is to use the number code associated with each character.

Continue reading

Prevent Formulas Showing in the Formula Bar

Your spreadsheet formulas are always shown in the Formula Bar of Excel. This makes it easy to view and edit the formulas of a spreadsheet.

But what if we have created an Excel file and we do not want others to be able to view the formulas. Prevent formulas showing in the Formula Bar by setting them to hide and applying worksheet protection.

Continue reading

Sum the Total Duration for Tasks in MS Project

Microsoft Project uses the duration field to calculate the difference between the start and finish dates of a task. But what if you want to view the sum of all durations for a set of tasks?

Microsoft Project does not have a field to calculate the total durations for a set of tasks. However this can be easily accomplished by creating a custom field.

Continue reading