Dashboard Form Controls

In this example, we will show you how to use form controls if you are creating a project dashboard. Check how the radio button and the slider works!

In the picture below, we have listed all the important information. On the data worksheet, you’ll find the well-known base table: name, activity, responsible person, start, and end date.

form controls overview

We’ll be able to handle the data of 15 business procedures and 15 participating persons altogether.
Only one question remains: how will we jam this large amount of data on only one page with the help of the template?

Fortunately, Excel provides us with multiple options for this!

Tracking Activities

We can keep changing between the ‘Project’ and Human resources with the help of a switch, and now we’ll explain to you the main difference between them. In the picture below, the project view is active.

For each project, more tasks can be assigned to. To summarize these, we can see how many tasks are extruded in a given period; and we get a view of the peaks that can affect the whole business process. So, we have to note that this view summarizes the tasks in a given time period.

project view

We have to know about the HR-view that arises from its name, it focuses on human resources.

As you can see, we have switched view, so now in the list on the left-hand side appears the names of the persons who participate in the execution of the tasks.

activities and resources

Why can this be interesting and, at the same time, also important?

We can establish the encumbrance of resources at once, so we can completely reorganize certain tasks. If 1-1 resources work too many subtasks at the same time and the project manager judge that as a risk, then we can revise or modify that.

Use Excel Form Controls to show activities

We have not yet talked about those Form Controls that really make the presentation interactive.

The first one is the Period Picker, which task is to enable the switching between the daily, weekly, and monthly views. If we would like to analyze the details then chose the daily break down, if we need a review / cumulative screen, then we should use the monthly view.

A slider enables us to be able to handle even several months or even years of activity in a simple manner.
By clicking on the arrow on the slider’s right side, we can display future information, and by clicking on the arrow on its left side, there will be past information displayed.

form controls slider

The heat map is a data visualization tool that displays business connections with the help of colors. In our current example, we displayed a lower rate of charging by light colors. We used darker colors in the periods where the rate of charging is large.

The later can occur because of two things: either too many tasks are running at the same time, or one participant is working on too many different tasks at the same time. This way is understandable why the project activity map is so important, is it?

We have used the IF(), INDIRECT(), and OFFSET() functions to create the template. Everyone knows the first one, which is a comparably simple logical formula. The other two require deeper Excel knowledge. We have it in our plans to introduce their use in a separate article.

We hope you have taken a liking to studying the connections of the project activity map elaborately.
There’s nothing else to do than share with you the excel resource planner template related to today’s presentation.

Download the template

Additional resources: