ProjectSheet icon

User Guide for ProjectSheet for Excel add-in

Contents


Check your add-in software for the latest release, select your platform:   Windows   Mac OS   Office Online


Video tutorials

Install add-in

Add sheets

Gantt chart in spreadsheet

Task columns and Edit tab

Resource columns (Pro only)

Settings tab

FAQ - Frequently Asked Questions

Contact


Video tutorials

Install add-in [2:10]

Quick tour of free add-in [2:49]

Quick tour of Pro add-in [3:45]


Install add-in

In Microsoft Excel you can install from the Add-ins store that can be found in the Insert ribbon. Enter "projectsheet" in the search box and Add the add-in, as shown in the Install add-in video.

You can also install from the Microsoft AppSource.

After installation the Home ribbon gets a ProjectSheet button to open the task pane.

Add sheets

The task pane of the ProjectSheet add-in opens with the Open Taskpane button in the Home ribbon. It appears on the right side of the spreadsheet.

You can Continue with the basic version or Sign in for Pro using Single Sign On (SSO) with your Microsoft account. Then you can start a 10-days trial by filling in a registration form. During or after the trial you can buy a Pro license.

screenshot with instructions for license screenshot with sheets to add

When opened for the first time, the basic version adds two worksheets: Project and Tasks, the Pro version adds four worksheets: Project, Tasks, Resources and Holidays. The Project worksheet shows a summary of the project. In the Holidays worksheet you can enter dates on which the project is not working.

The task pane will be replaced with three tabs, one to Edit, one for Settings and one with information About the add-in. You can close the task pane and continue with editing.

screenshot with tabs of add-in

Gantt chart in spreadsheet

The Tasks worksheet is shown in the screenshot below and consists on the left side of a table with the schedule and on the right side of a generated Gantt chart. On the far right is the add-in's task pane. When edits are made in the spreadsheet or task pane, the schedule and Gantt chart are updated during the wait period, indicated with the wait color at the top of the task pane.

screenshot of Excel with add-in

The Start and Finish dates correspond to bars in the Gantt chart. The WBS code indicates how the tasks are structured with summary tasks.

The start date of the summary task equals the earliest start date of its subtask(s), the finish date of the summary task equals the latest finish date of its subtask(s).

screenshot with Gantt bars

The progress percentage corresponds to a double line in a bar.
The date of today is shown with a vertical dashed blue line in the Gantt chart.
A task is blue, a summary task is gray and a milestone is orange.
When present, the background color of the Task description is shown as the color of the bar.

Timescale

The Gantt chart shows the entire schedule or a part of it, depending on the timescale set in the Settings tab in the task pane. You can set the Start date and the Time period of the timescale. The Start date can be set with the date picker or to today or to the earliest start date if the selected tasks. The Time period starts with 1 day per cell and ends with 3 years for the entire timescale (18 days per cell). Both Start date and Time period are set automatically with Autofit.

screenshot with auto fit for Gantt timescale

You can set the start day of the week in the Gantt, default is Monday.

Task columns and Edit tab

WBS and Task description

In the Task description column you start entering the schedule. The WBS column is calculated and shows a code indicating the Work Breakdown Structure of the tasks. When present, the Task description background color is shown as the color of the Gantt bar after clicking the Refresh Gantt button. Deleting the task description text will remove the entire task.

screenshot with up/down buttons
These up/down buttons in the Edit tab move to the previous/next task row. The spreadsheet row index is shown behind the buttons.

screenshot with WBS buttons
These buttons in the Edit tab decrease/increase the indent WBS level of the selected task row(s). The WBS code is shown behind the buttons.

The WBS hierarchy is also shown in the indention of the Task description text. So-called summary tasks are in bold text.

screenshot with dropdown in task pane
This dropdown in the Edit tab has functions to collapse/expand selected or all summary tasks.
The 'collapse level' function collapses all summary tasks with the same indent level as the selected summary task.
The 'hide 100% completed' function hides all (summary) tasks that have 100% progress.

Start and Finish dates

The Start column contains the start date of a task, the Finish column the finish date. The summary dates and Gantt chart are calculated when applicable. For the basic version a task becomes a milestone if it only has a finish date.

screenshot with datepicker in task pane
The date picker in the Edit tab puts the picked date in the selected cell(s) in the spreadsheet.

Duration (Pro only)

The duration of a task is the time between Start and Finish date. When changed, the Finish date is recalculated, while the Start date is kept. When the Finish date is changed, the Duration is changed while the Start date is kept. A task becomes a milestone if it has a duration of 0.

screenshot with spinners in task pane
The Days and Hours spinners in the Edit tab set the duration in the selected cell(s) in the spreadsheet.

Predecessors (Pro only)

You can enter a predecessor task by entering its spreadsheet row number. Multiple tasks as predecessor are separated with a semicolon (;).

screenshot with predecessor spinners
You can also link tasks by mouse click in the spreadsheet and in the Edit tab of the task pane. First click on a task in the spreadsheet and then on 'from' in the task pane. This task will go from italic to bold in the task pane. Then click on a task and on 'to'. You can repeat this for more 'to' tasks. Clicking on 'to' again will unlink. You can also do this reverse with 'to' and one or more 'from' tasks. After linking you can change the type and lag/lead time in days and hours.

Condition (Pro only)

The Condition column shows additional conditions for tasks on how the planning should be scheduled. The rows have a pull-down menu with four options when the cell is selected:

No text in the cell means ASAP.

Resources (Pro only)

The Resources column shows the assigned resources from the Resources worksheet. The resource corresponds to the name or abbreviation if present. The default assigned quantity is 1, between square brackets [] a different quantity can be entered. Multiple assigned resources are separated with a semicolon (;).

screenshot with resources dropdown in task pane
The resources in the Resources worksheet are also present in a dropdown menu in the task pane. Click on a task and select a resource from the dropdown menu. Already assigned resources are in bold. The default assigned quantity for a new resource is 1. You can enter a different quantity for an already assigned resource with the spinner. A quantity of 0 will remove the resource from the task.

Work (Pro only)

The amount of work hours per task or summary task.

Cost (Pro only)

Total cost per task or summary task for all assigned resources. The cost calculation depends on the resource Rate and Type. When no resources are assigned, you can enter costs yourself.

Progress percentage

The progress percentage of each task. Summary task progress is calculated from its subtasks. Entering the percentage for one or more cells at once can be done in the task pane:

screenshot with percentage slider in task pane
This slider puts the chosen progress percentage in the selected cell(s) in the spreadsheet.

Shift start date

This function allows you to shift the start and finish dates of the selected row(s).

screenshot with datepicker for shift start
The date picker will show the earliest start date in the selected rows.
When you change this date, all selected start and finish dates will shift accordingly.

Resource columns (Pro only)

Resource name

Entering a name will add the resource, removing the name will remove the resource. The resources in the Resources worksheet are also present in a pull-down menu in the task pane. A resource can be assigned to one or more tasks.

Abbreviation

The abbreviation replaces the resource name in the Resources column in the Tasks worksheet for convenience.

Type

The types are in a pull-down menu when you select the cell:

Rate

Tarif per hour for Work and Equipment type resource and cost per unit for Material type resource.

Count

Total hours for Work and Equipment type resource and total quantity for Material type resource across all tasks.

Cost

Total cost per resource across all tasks.

Settings tab

Timescale

The timescale of the Gantt chart is explained here.

Progress indication

The progress indication can be set for the Progress column and Gantt chart independently.
The colors have the following meaning:

Condition Color
today before task start date no color
today after task start date and progress on schedule green
today after task start date and progress behind schedule yellow
today after finish date and progress less than 100% red
progress equals 100% no color

screenshot with Excel and settings in task pane

screenshot with settings tab in task pane

Critical path indication (Pro only)

Linked tasks which are on the critical path will cause project delay when they are delayed. Non-critical tasks have slack time before they cause project delay. Options for critical path visibility in the Gantt chart:

Working week (Pro only)

Set the working week by selecting

The non-working days (weekends) are gray columns in Gantt charts that have a scale of 1 day per column.

Work per task (Pro only)

You can assign the work hours of a task among the resources or per resource.

Task duration and Work hours can be set independent or follow each other with the set working hours per day. The option takes effect on changes and on new tasks only.

FAQ - Frequently Asked Questions

How to expand the timescale of the Gantt chart?

Do not change the Gantt chart in the spreadsheet itself. Change the Start date and Time period of the chart in the Settings tab of the task pane. By changing the Start date you can scroll through the Gantt chart.

Why are the Gantt bars sometimes not aligned with the tasks in the Pro version?

This may happen when the spreadsheet zoom level is not 100%, as a result of an issue in the Microsoft framework. As a workaround there is the basic Gantt option at the bottom of the task pane on the right.

How to update the add-in to the latest software release?

The update procedure depends on the platform. Windows, Mac and online are available here.

What is the minimum requirement for this Excel add-in?

The Microsoft AppSource store states Excel 2019, the minimum Excel version required is the 1808 (2018, August) update. You can find your version under Account > About Excel.

Can I transfer my license to another account when I leave the company?

Not your license, but you can transfer your remaining time to another active license. Send a request for transfer to the Helpdesk in which you mention the other active license account. The remaining time is then added to the license end date of the other account and your own license is terminated.

How to remove the add-in?

Find the ProjectSheet add-in in the Insert ribbon under Office Add-ins in the MY ADD-INS tab. Click on the three dots for the menu and choose remove. If ProjectSheet is not there, you can look in the ADMIN MANAGED tab and ask the administrator.
Make sure that you remove the add-in from all the desktop Excel apps that you used on all your devices and also from Office online, to prevent a task pane with a Trust this add-in button when you open the spreadsheet again.

Why are the dates in the Gantt scale 4 years and 1 day earlier than the dates in the Start and Finish cells?

Excel has two date systems, Windows always uses the 1900 system, Mac also uses the 1904 system. ProjectSheet for Excel uses the 1900 system. Here you can read how to switch from the 1904 date system to the 1900 date system in Excel, depending on your Excel version: Date systems in Excel.

Contact

Helpdesk