In the next three chapters, I’ll discuss how to create a Gantt chart dashboard. In this chapter, I’ll take you on a tour of the dashboard so you can become familiar with its mechanisms from a high level. As is the case with most of this book, you’ll reverse engineer what’s already been created. This allows you to divide the underlying mechanics into different sections. Moreover, it should drive home a salient point about reusable components: your work can be broken in disparate pieces. What you build is the sum of its parts, and those parts can exist independently on their own or together in unison.

Figure 13-1 shows what you’ll be investigating in this chapter. You can follow along by opening Chapter13GanttChart.xlsm, shown in Figure 13-1.

If you’re new to Gantt charts, here’s a brief refresher. Gantt charts were created by Henry Gantt in the early 1900s. Their goal is to communicate a project’s schedule using data visualization. Gantt charts can get complicated quickly, showing everything from project changes to dependencies and including multiple projected end dates based on different scenarios, among other things. I’ll keep things simple here and attempt to show three different dimensions for each project record. Figure 13-2 breaks down what we want to show.

For this Gantt chart, the project’s date begins when you start seeing some gray area. If it’s simply white, as you can see by the records in Figure 13-1, the project hasn’t started yet. Similarly, the end date is the last date shown in gray. The shaded gray regions are essentially heatmaps for how many hours are required to complete the project. The exact values aren’t shown within the heatmaps, but you can visually ascertain the differences. The darker the gray area, the more hours required.

Features of the Gantt Chart Dashboard

In this section, I’ll go through three different features of the Gantt chart.

• The pop-up information box
• The banded region chart
• The legend beneath everything

Let’s go through each of these items.

Pop-up Information Box

If you look at Figure 13-1, you’ll see a pop-up. I provide a larger version of this pop-up in Figure 13-3. The pop-up provides several pieces of information. It provides the project number, the current day, the number of hours visually represented by the gray area, and the average number of hours per day for a given project.

The pop-up feature is driven by the rollover mechanism described in Chapter 5. That means to see information for a given day and given project, the user simply needs to roll their mouse over a given cell. They do not to need to click anywhere.

Banded Region Chart

The Gantt chart also provides a graded chart across the top (Figure 13-4). I’ll talk about it in this subsection.

The bar charts represent the culmination of all hours worked on a specific date. Some days the project might have too many hours assigned to work on it. There are graded regions to help the user judge when too many hours have been assigned. At the top of the chart, the darkest region signifies that far too many hours have been assigned to a specific day to work. If you saw this on your real-life dashboard, you would know to take corrective action. Below the High region, the Warning region provides a small margin of extra hours that can be considered a cushion before moving into the High region. A warning region is not ideal but may be tolerable. Below the Warning region is the Good region. Cumulative hours within this region indicate the number of hours to work is well within tolerable limits.

Dynamic Chart Legend

In this subsection, I’ll talk about the dynamic chart legend shown in Figure 13-5.

What makes this legend dynamic is its ability to calibrate to whatever back-end data is supplied. I’ll get to the back-end data used in this dashboard in a moment. But for now, take a look at Figure 13-6, which shows that the legend is actually based on a series of formulas.

The reason I bring this up is to drive home that the legend is not static. As the back-end data of this model changes, the legend can easily adjust to this information. You need make this legend only once, and further changes in data will automatically adjust to accommodate the new minimum and maximum data. This legend is just another example of a reusable component.

Behind the Scenes

In this section, I’ll go into the back-end elements of this dashboard to understand how it all comes together. The dashboard includes three tabs, as shown in Figure 13-7.

These three tabs are as follows:

• Dashboard: This is the main page of your work. It includes the information that will ultimately allow you to understand how hours are allocated among your days and projects.
• Calculation: This is an “intermediate” tab, which you’ll use to transform the raw back-end data so you can present it on the dashboard. It includes several calculations that will enable you to present this information.
• Data: This is where the dashboard’s back-end data resides. This data is used by the Calculation tab and then ultimately by the dashboard to present information to the user.

Notice the design pattern here. The Data tab informs the Calculation tab, which then informs the Dashboard tab. This follows the information (Data tab) a transformation (Calculation tab) presentation (Dashboard tab) dynamic presented in Chapter 8. The Dashboard tab never refers directly to the Data tab. Instead, it uses the Calculation tab as an intermediary.

I’ll go through each of these tabs from right to left (see Figure 13-7).

The Data Worksheet Tab

In this section, I’ll talk about the Data worksheet tab. Figure 13-8 shows an excerpt from the tab starting from the top.

The Data tab contains one thing—an Excel table. There’s nothing fancy you do with it except store your back-end data. The table consists of four different columns. Let’s go through each of them.

• Id: This column simply includes a unique identifier for each record.
• Project Name: This column tracks the project name a given record refers to.
• Day Number: This column tracks the day a given record refers to.
• Hours Required: This column tracks how many hours were required for a given project and day.

I can understand if you’re confused. But the best way to think about this data is that for each project and for each day displayed on the dashboard there is a given record detailing the hours required. If there are 18 projects and 13 expected workdays, then there would be 18*30, or 540, total records.

The Calculation Worksheet Tab

The Calculation worksheet tab is where you do work (that is, perform calculations) on the back-end data (from on the Data tab). This pattern (the back-end data feeding into the Calculation tab, which then feeds into the presentation layer) is part of the framework presented in previous chapters. Figure 13-9 shows a snapshot of the Calculation worksheet tab.

The Calculation worksheet tab is divided into two sections. The top section is a tabular view of the back-end data. The bottom section is used to create the banded region chart shown in Figure 13-4. I’ll hold off on talking about the banded region chart until later in the chapter. For now, focus your attention on that top section.

You can think of this top section as laying out the data in much the same that a pivot table might. Figure 13-10 shows how the back-end data is mapped onto this tabular table. The name of each project becomes the row key and the corresponding day number becomes the column header. The hours required, which appear to be listed vertically in the table on the Data worksheet tab, are now listed horizontally at the intersections of their corresponding project name and date.

This layout is similar to a pivot table, but it’s specifically not a pivot table. As I mentioned at the beginning of this book, I’ll try to avoid using pivot tables to the extent possible (except for when you are employing their functionality as part of Microsoft’s new Power BI tools), and here is no exception. If you were to implement a pivot table, you would lose the ability to have live updates in the back end of your data automatically populate your dashboard. That’s because pivot tables require you to refresh the connection to the source data for each and every update.

Because you’re not using pivot tables, you’ll have to use formulas to create the same schema. As explained previously, each project has 30 associated records representing the 30 days available to be worked. Since your data is in sorted order, you know the location of the records associated with each project. For instance, the first 30 records deal with Project 1, records 31 to 60 deal with Project 2, records 61 to 90 deal with Project 3, and so on.

As means to help you look up the locations of the records you’re interested in, I’ve created a Location column (Figure 13-11) that sits to the left of the tabular data.

Notice the locations follow what I described previously, although the location numbers are off by one, which you’ll understand in a moment. But recall there are 30 records between each project, and in Figure 13-11, you see multiples of 30 for each location. Project 1 starts at 0, Project 2 starts at 30, and Project 3 starts at 60.

To create this list, I simply started by typing a 0 and a 30 in cells A4 and A5 and then dragged down to include the required amount of cells. Figure 13-12 shows this in action.

Once you have the locations set, you can look up the hours required for a given day. Since the day numbers are listed across the top, you can use this information for the lookup. In cell C4 (Figure 13-14), you are looking for the hours required for Project 1 on Day 1—the first record from the back-end data. In Figure 13-13, you are using the INDEX function to find that record. Recall that INDEX works by returning a designated row (and optionally, a designated column) from a given array or range of data. Whatever record you’re interested in, you can find it by using the location for a given project and then adding the given day number across the top. In Figure 13-13, C$3 + $A4 would return a 1, for the first record in the project table. You can achieve this lookup based on INDEX by dragging the formula across.

Similarly, the hours required to work on the sixth day for Project 2 (Figure 13-14) would be found in location 36 (the result of adding H$3 and $A5) on the project table (Figure 13-15).

I hope you now understand the underlying mechanics of this table, which shows the back-end data in tabular form. But, before moving on, I should note a problem I ran into in its setup. In Figure 13-16, I’ve once again highlighted the first data point on the table. Notice that the formula in the formula bar is an array formula (you can tell by the curly braces shown at the beginning and end of the formula).

Since you are not returning multiple cells or working with arrays, an array formula may feel like an odd, if unnecessary, choice. However, to make the setup of this table easier, an array formula is necessary.

Here’s why. Let’s clear everything out and start from scratch. In Figure 13-17, I’ve started over and skipped the array formula formulation.

If you use the anchor and drag to the right, you would expect the formula shown in Figure 13-17 to populate similarly across the cells. However, when you don’t use an array formula, a strange thing happens. The table header you’re interested in, Hours Required, isn’t technically an absolute reference. As you drag to the right, the formula begins to cycle through each header name (similar to what would happen if you dragged a formula reference and relative cell reference). This is shown in Figure 13-18 with the resulting formulas displayed.

To get around this issue, you can start with the formula displayed in Figure 13-17 and press Ctrl+Shift+Enter to tell Excel you are working with an array formula. Afterward, you can drag the cell containing the formula without fear the column you’re interested in will change in tandem.

At the bottom of the tabular data, there is a row marked Total. This row simply displays the sum total of hours required for each day across all projects. Figure 13-19 shows the formula required.

The Dashboard Worksheet Tab

The Dashboard worksheet tab is the place where all the information created in the Calculation tab feeds into. You already looked at the Dashboard worksheet tab at the beginning of the chapter. Right now, I would like to point you toward an intermediate table placed below the dashboard to be interested in. Figure 13-20 shows an excerpt of what is actually a much larger section.

I’ll return to intermediate tables later in this book, but you can think of them as an extension of the transformation layer presented as part of the information-transformation-presentation framework mentioned earlier in this book. I could have easily placed this on the Calculation worksheet tab, but for what we’re trying to do, sometimes it makes more sense to keep pertinent information on the same tab—even if that requires some redundancy.

For instance, the data in the intermediate table is actually just pulling from the tabular data in the Calculation tab (Figure 13-21).

That data is then fed into the dashboard front end. In Figure 13-22, you see the formula for one of the cells of the dashboard refers to its corresponding cell (I45—shown twice at the end of the formula) in the intermediate table.

You may be wondering what’s going on with that IFERROR and HYPERLINK stuff at the beginning of the formula. These functions are actually part of the rollover method discussed in Chapter 5. As mentioned at the start of this chapter, rollover interactivity was implemented on this dashboard, and these functions are required to make it work. I’ll go into them in more detail in the next chapter. For now, it may be helpful to imagine these cells are simply referencing the ones below without the added pop-up.

If the pop-up is bugging you as you investigate this dashboard (and, yes, it can do that—sometimes it’s a good idea to make the pop-up the last thing you build), you can disable in the code to help your investigation. To do that, go into the Visual Basic for Applications editor and select the module ScenarioUDF. The user-defined function, RolloverCell, should be immediately in view (Figure 13-23).

There is a commented line after the function definition that says Exit Function. For testing and debugging, you can remove the apostrophe right before Exit to turn it into a VBA command (Figure 13-24). This will tell Excel to immediately leave the rollover function without executing the remaining code. Just remember to add the apostrophe back in when you’re done with your investigations or code debugging.

If you’re still following along (and you should be!), go ahead and remove the apostrophe so you can continue your investigation. You should now be able to move your mouse around the dashboard area without the pop-up box following suit. Depending upon where your mouse was when you removed the comment symbol in front of Exit Function, the pop-up box might still be showing. The way the function is set up, if there is no data in the region highlighted, the pop-up box will disappear. However, if your mouse was previously hovering over a cell project data in it, the pop-up box will remain over that cell, even if it no longer follows your cursor. Feel free to click the pop-up box and simply move it to the side. (Don’t delete it! You’ll need it later.)

The graded color region on the dashboard is generated using conditional formatting. You can see this for yourself by highlighting cells D7:AG24 on the Dashboard tab. Alternatively, I’ve already given this region the name Dashboard.GanttChartArea, which you can select from the Name Box drop-down (Figure 13-25).

With the region selected, you can view the conditional formatting set by clicking Conditional Formatting on the Home tab and going to Manage Rules. The Conditional Formatting Rules Manager dialog box will pop up (Figure 13-26). As shown in the figure, select the second rule and then click Edit Rule.

Clicking Edit Rule will bring up the Edit Formatting Rule dialog box (Figure 13-27).

The Edit Formatting Rule dialog box contains rules associated with the data region. Notice in Figure 13-27, I’ve selected “Format all cells based on their values” as my rule. Under Format Style, I’ve selected 2-Color Scale. This tells Excel I’m interested in formatting the range of cells based on two different colors at the extremes. For Minimum, I’ve told Excel I want to use a specific number by selecting Number in the Type drop-down. Technically, the lowest value available in the data range is zero, which indicates no hours required for that project for a specific date—such values are found either before the project has started or after it has ended. You want these zeros to remain white and not be colored. Therefore, you set the rule to a number arbitrarily small and close to zero but not at zero—in this case, .1. On the other hand, you won’t always know what the maximum will be, so I’ve let Excel analyze the data presented to figure this out. I’ve done this by selecting Highest Value in the Type drop-down under the Maximum label.

Next, you set the colors desired to be shown at your extremes. For the lowest value, select a pure-white color. The effect of this is that zeros in the dashboard will be white. If you were to set it to a light gray instead, everything less than .01 would also be light gray. Figure 13-28 shows the effect of this. On the other hand, if you start with a solid white, Excel will know to keep cells holding numbers less than .1 white and shade cells with greater numbers.

Once you are finished looking at the Conditional Formatting dialog box, click OK until you get back to the dashboard. Typically when you apply conditional formatting rules to cells, the numbers inside the cells remain. Yet, in this dashboard, you can see that no numbers are shown; rather, only their fill colors as defined by the conditional formatting rules remain. I was able to remove the numbers from view by using a custom formatting trick.

Once again, you can go ahead and select the shaded data region either by highlighting cells D7:AG24 or by selecting Dashboard.GanttChartArea from the Name Box drop-down (shown earlier in Figure 13-25). With this region once again selected, take a look at the Number group on the Home tab. In the Number Format drop-down, you’ll see Custom has been selected (Figure 13-29). That’s because I’ve defined a custom format to hide the numbers. In the lower-right corner, there is a button to click that will present the Format Cells dialog box (Figure 13-30). Go ahead and click where the cursor is present in Figure 13-29.

In the Format Cells dialog box, I’ve defined a custom format for the selected cells. You can define your own custom format by selecting Custom in the Category listbox on the left. This will present you with an input box to define your own custom formats. As you can see in Figure 13-30, I’ve simply typed "" (two quotation marks), which tells Excel to replace numbers with a zero-length string. In other words, where there are numbers, you are directing Excel to show you nothing.

Why custom formats? They allow you to hide the numbers in the cell without changing the underlying values. For instance, even as you no longer see the numbers in the cells of the presented example, Excel still recognizes a value in the cell; you’ve simply formatted the number to appear differently. Neither formulas nor code provides this advantage of having a number both exist and not appear. Think about that one for a minute!

Finally, I want to turn your attention to the squares that grid-off the dashboard’s data range. As I established at the beginning of this book, how you present data is as important as the underlying data. In building this data range, I went through several iterations of how I wanted to segment the information in the data region. In this case, I chose a light border every five-by-five cells to create a light grid (Figure 13-31).

There’s always both an art and a science to choosing how to display this type of information. Later in the book, you’ll return to some of the thought process behind breaking up data to make it more readable. But even now there aren’t any specific rules. Gridding off every cell made the entire thing look far too busy for me (Figure 13-32).

But that choice ultimately rests in your hands. It may be tempting, for instance, to place a border around every cell—certainly that would be the style most common in industry. But you should avoid all things that might inhibit data reception by the user, however small they may seem. That’s thinking outside the cell.

The Last Word

In this chapter, you toured the Gantt chart dashboard, analyzing its features and how its back-end data ultimately feeds into its presentation. You traced the path of the back-end data, from how it began as a simple table on the back end and was transformed into something usable and actionable. By using formulas in place of pivot tables, you defined a living model: back-end changes to the data are always ramified up through to the presentation level.

In addition, you looked deeper into much of the underlying mechanics and presentation. You only dealt with VBA to disable a feature as you investigated the model. Indeed, it’s worth noting that none of the mechanics investigated require VBA to do anything to the data. Formulas help trace the back-end data to the front, and custom formatting and conditional formatting support the visualization. To the extent possible, you let Excel features do the hard work.

In the next chapter, you’ll dig deeper.