Summary

This user manual has been created to help you get the most out of your new download.

Your download file will contain the following:

• 2 x Excel files (UK & US Versions) that includes:
  • DATA & Dashboard main page – this is where we add tasks, due dates and lots of other good stuff and as this is the single page version – it is also where all the auomation sits for the Gantt chart, slicers selction buttons) date calculations etc.
• This user manual
• 1 x sample data file

Whether you are working on a large multifaceted programme for a challenging client or keeping track of your small business – this tool is just what you need to get completely organised without the need for expensive software.

It will show you what task is due to be completed by when, who should be doing it and how close you are to the finish line (% Complete) amongst other things.

This guide will take you through each element of the workbook with screenshots – so you always have it to refer back to if you get lost.

I am constantly working on new tools so please do come back and visit me at any of my various homes to see what else I might be able to help you with.

KEY POINTS TO NOTE

Please note that this single page gantt chart project planning tool has a number of key features that you are unlikly to find elsewhere. There are also a few features that you may want to take into account before you start to use it.

The dates in the Gantt chart headers are week commencing dates – this means that wheatever date you add into the DUE DATE field will be “translated” into it’s week commencing date.  (I am working on a sheet that shows single dates ONLY so please check my shop over the coming months)

For example, if you added 31st august 2022 as your due date, the sheet will know that that date sits in the week that starts with monday 29th August and will automatically colour the cell that sits under that w/c/ date.

A version of the tool that uses START date instead of DUE date can be created on request.

In this version of the sheet, the start date is automatically calculated using the due date and the duration. (See page 7 for more information on this)

This sheet is also driven by 2 mandatory pieces of information – these are the due date and the task duration. Without these fields being completed – the sheet cannot work.

The duration days is used to calculate the start date of a task and uses the “workday” function which automatically excludes weekends. So if a task will take 2 weeks, enter “10” into the duration field as 2 weeks = 10 working days.

Entering The Data

Any plan is only as good as the data we can put into it.

This project planning tool allows for 2 levels of task – the reason for this is that as our projects get bigger, we often find that we have tasks within tasks (see page 14 for information on adding more levels if you need more than 2)

Column B – Level 1 tasks

Let’s say for example that you are working on an email campaign for your new product “super amazing product #1”

Your Level 1 task would be “Product #1 email campaign”

Column C – Level 2 Tasks

Your next subtask might then be

1. Select target audience
2. Set up the email software autoresponder
3. Decide affiliate programmes to pair with my product
4. Select upsells for the campaign
5. Select on sells for the campaign
6. Launch

Your data input would therefore look like this:

Note that I have repeated the information at level 1 – this is very important as excel will use the data in this column to show you the information that you select at the top of the page in the slicers.

For this same reason it is important that the data is EXACTLTY the same so please copy and paste it into the cell where you need it to be to avoid any spelling mistakes or spaces in the wrong places.

When I use this sheet for my projects, I often create these fields as drop-down menus to ensure consistency – this is very easy to set up if you feel like it would help & you tube has some great videos on how to do it.

Columns A &D – Task Reference

The task reference is useful when you have linked tasks and want to make sure that one is completed before the next one starts.

NOTE: Please ensure that you add data into this column as some of the conditional formatting relies on it not being and empty cell.

In your download folder you will see that I have added an extra copy of the file which is prepopulated with the data I have used in this manual, it’s just for demonstration purposes so it’s not strictly accurate but it might help you out of you get a bit stuck.

Please DO NOT delete any columns as it will damage many of the linked formulas and your sheet will stop working properly. If you need to delete ROWS of data though – that’s perfectly fine.

Status (Column E)

This is a Calculated Field – DO NOT TYPE IN THIS COLUMN

This field will automatically calculate and colour code the status of your task based on the due date, completed date and task duration.

It will use today’s date to automatically highlight those tasks that have fallen due and have not had a completion date entered in the relevant column. 

Progress (Column F)

This is a Calculated Field – DO NOT TYPE IN THIS COLUMN

Using the due date and task duration this field will provide a % for progress completed based again on today’s date – this can be useful if you see a task that is almost complete, but you know it’s delivery will be delayed – you can change the due date and duration. 

Key Milestone (Column G)

A simple “Y” in this field will allow you to select all milestone tasks in one click from the slicer at the top of the sheet, this is a manual entry field, you can type “Y” or “Yes” or X – whatever you like – just keep it constant.

Key update (Column H)

If you have updated a large number of tasks on one day or just want to group some activities together and want to see them all in a single view – denote them as key update tasks and select them using the slicer at the top of the sheet.

You can use different letters or key words for different groupings if you like. Just keep it short as whatever you put in here will show up in your slicer selections at the top of the page.

Duration (Workdays) – (Column I)

This is the 1^st of the only 2 mandatory fields in this workbook.

Enter here how many workdays you think this task will take: e.g. if you think it will take you 2 weeks – enter 10 into this field.

Note that this field requires numbers only – anything else such as a letter or full stop added here will prevent the formulas from working and might make the rest of your sheet look very broken!

It is important to add the duration days to the sheet as it will use this to calculate the start date of your task.

Using our previous example, lets imagine that we have a number of due dates that we want to complete our tasks by, and we have an estimate of how long these things will take.

Changing the number of days will automatically change the Gantt chart, the progress % and the status of the task.

Column J – Due date

This is the second of the 2 mandatory fields in this worksheet. There is a UK version and a US version in your download file depending on how you like to see your dates.

Enter the due date of this specific task – DATES only – please don’t add any text in here as this field drives a number of other calculations throughout the sheet.

If you notice that some of the cells next to the due date look wrong, it might be that you have entered the date in incorrectly.

If you are in the UK type “dd/mm” or “dd/mm/yyyy” if you want a date outside of the current year. So, for the 1^st of December I would type 01/12 or 01/12/2023 – NOT 01.12, the computer will see “01.12” as text – not a “date”.

For the US version of this sheet type in “mm/dd” or “mm/dd/yyyy” if you want a date outside of the current year, as above use the slash symbol to separate your date from month, not a full stop (period).

NOTE: The sheet will use due date and duration to calculate the start date, status and progress of your task. All these calculations take place inside the hidden columns L thru W – please take care not to delete these.

To show how this works in practice – lets look at task “T001” that was expected to take 15 days, it will be due on 30^th September and has a status of “Not Started” at todays date is 5^th September.

If I simply update the duration of my 1^st task from taking 15 days to taking 55 days – the formula in the sheet now calculates how may days I must have already completed (based on “todays date”) and will look like this:

NOTE: the “TODAY()” formula used in this sheet is always “today” and takes the system date in your computer to see exactly what date today is.

The task has now automatically changed to “Started”, the progress bar has changed to 65% and the Gantt chart will show all the relevant work weeks coloured in blue instead of beige.

Date Complete (Column K)

If your task has been completed – enter the final completed date in here and your reports will instantly show that the task is complete.

The status bar will turn dark blue as will the Gantt chart bar.

Please take care again to enter the date in the correct format.

Task Owner (Column X)

Add in here the name of the task owner – you might only want to use this if you have a team of more than 1 person!

Please also note again that as this field is used to select and filter the data in the shortcut buttons both on the data tab and on the dashboard – if you do use multiple names, please make sure that you spell them the same way every time.

Rows 2&3 – Navigation

1.        Date selection

If you want to change the dates in view on the Gantt chart, then you can skip forward or back a week at a time using the arrows either side of this handy little scroll bar.

If you click inside the scroll bar it will skip forward or backwards 4 weeks at a time.

The date range will be automatically detected and will use the earliest start date in your data and the latest due date.

NOTE: all the dates in the Gantt chart are week commencing dates and assume that the week starts on a Monday.

I added an extra little check box here that will “jump to today”. If this box is checked then your Gantt chart date range will start at “TODAY” if it is unchecked, then it will revert to the earliest start date of all your tasks.

Using the slicers

The slicers at the top of your data page will have automatically updated to show your data so you can click on anything and see just that in your data set.

If for example you wanted to only see overdue tasks or tasks due in the next month – then select that from the relevant slicer box.

Use as few or as many as you like:

I can also make multiple selection in a single slicer by 1^st checking the “multiple selections” icon as shown here:

To clear your selection, click the X on the filter sign which will be red when you have selected (or deselected) something.

Task Progress Bar

The task progress bar at the top left of your sheet will change depending on the selections that you make using the slicers.

The bars are colour coded in the same way as the Gantt chart and the status column:

• Dark Blue = Complete
• Pale Blue = Started (In progress)
• Pink = Overdue
• Beige = Not started (but has a planned due date and duration)

The Gantt Chart

As previously noted, this range starts at the earliest start date in your data set and will automatically roll up all tasks due in that week (Monday – Sunday) to belonging to that week commencing date.

So, if you have 1 task active (i.e. started but not yet completed) on Friday 15^th July, another on Monday 11^th July and a 3^rd on Saturday 16th July, this will place all of these tasks in the column for W/C 11^th July.

If you have started a task in week 1 and are due to complete it in week 5, all these weeks will be coloured light blue if you have started it, beige if you have yet to start it, pink if it’s overdue and dark blue if you have completed it.

There is no need to colour anything in here – it’s all automatic!

It’s worth opening the sample data set up and just playing around with the dates and durations to see how everything changes

Adding rows to the data

The data in this sheet is formatted as a table – this means that if you start typing a new row of information in the next available row at the end of the current data – it will automatically assume that you are adding data to the table and copy all the formulas that you need down into your new row.

If you skip a row before typing – it will NOT do this, and your new data will not be included in the slicers.

Alternatively, you can add a row into your existing data by selecting a row, right clicking and selecting “insert row” from the menu – this will again copy all the necessary formulas down with it.

One final note on entering data in a data table such as this one, if you do add rows and then find that they have not been captured in the reports or are being excluded from the filtering options, please click into the table (select a cell in one of the headings for example) then go to “Table Design” menu at the top of your page:

When you have this menu up you will see on the far left an option to “resize table”

Click on resize table, and in the popup box make sure that the range is enough to capture all your rows of data – if you’re not sure, just increase the number of rows and have a few spares at the end of your data set – i.e. in this example you might change the “$A$3:$W$11” to “$A$3:$W$25” to capture an extra 14 rows.

Be careful here – selecting just the number you want to change can be a bit tricky – if you mistakenly delete everything, just click cancel and start again.

Adding Columns to the data

If you are reasonably; proficient with excel and feel comfortable resizing charts and repositioning the slicers, then you can add as many new columns as you need – if you are not then please do get in touch and I will do my best to accommodate your requirements.

Please note that there are a lot of hidden columns that contain a great many calculations so deleting columns is not recommended.

If you do want to add a 3^rd level of task, then using the same example of the email campaign, let’s assume that in your level 1 task of “select target audience” you want to do 3 more things – these might include:

1. Look at my customer records to see who has bought something similar in the past
2. Make a list of businesses that would benefit from my product
3. Examine my competitors’ customers to see what I can learn

If you added this data to your sheet – it would now look like this:

You will see again that I have repeated the information in the 1^st 2 levels so that our Gantt chart and the slicers will work properly.

MORE INFORMATION

I have done a short video that walks you through how this tool works which can be viewed via the link below.

https://linzibee.com/onepage-gantt-chart/

If you have any additional requirements or suggestions, please get in touch: help@linzibee.com

I am constantly working on new tools so please do come back and visit me at any of my various homes to see what else I might be able to help you with.