How to Create a Workback Schedule in Excel: 5 Methods

Method 1 – Create a Preliminary Summary Layout

Select cell B1.
In the Insert tab, click on the drop-down arrow of the Illustration > Shapes option and choose a shape according to your desire. Choose the Scroll: Horizontal shape.

Choosing shapes for the workback schedule report

Write the title of our report. We wrote the Workback Schedule Summary as the title of the sheet.

In the range of cells B4:E4, write the following heading and allocate the corresponding range of cells B5:E5 to input the results.

Creating the first table of the workback schedule report

In the range of cells G4:K4, write the following entities to enlist the work plan.

Select cell K1, and in the Insert tab, click the drop-down arrow of the Illustration > Pictures option and choose the This Device command.

Inserting company logo in the workback schedule summary

A small dialog box called Insert Picture will appear.
Choose your company logo. We use a website logo to demonstrate the process.
Click on Insert.

The job is completed.

Create Preliminary Summary Layout to Create a Workback Schedule

Say that we have finished the first step, to create a workback schedule in Excel.

Method 2 – Input Sample Dataset

In the range of cells G5:I5, input the following data.

Input Sample Dataset to Create a Workback Schedule

In cell J5, write down the work starting date. We input 1-Sep-22.

Get the value of the End Date, and write the following formula in cell K5.

=(J5+I5)-1

Press Enter.

Determine the end date of the task to create a workback schedule

The second task will start after the first task is finished. To get the starting date of the second task, write the following formula.

=K5+1

Press Enter.

Estimating the starting date of the 2nd task to create a workback schedule

Select cell K5 and drag the Fill Handle icon to get the end date of Job 2.

Select the range of cells I6:K6 and drag the Fill Handle icon to the last of your job list. Since there are five jobs, we dragged the Fill Handle icon up to cell K9.

Select cell B5 and write down the Project Name.

To get the project’s Start Date, select cell C5 and write down the following formula. Use the MIN function.

=MIN(J:J)

Press Enter.

Calculating project starting date to create a workback schedule using the MIN function

For the End Date, write the following formula in cell D5 using the MAX function.

=MAX(K:K)

Press Enter.

Calculating project ending date by the MAX function

To get the Duration value of the project, write down the following formula in cell E5.

=(D5-C5)+1

Press Enter for the last time.

Estimate the Duration of the Project to Create a Workback Schedule

The task is completed.

Method 3 – Import Dataset into Detail Workback Report

Write the title of this sheet.
Write the headings according to the last sheet.

To get the first job no., write the following formula in cell B6 using the IF function.

=IF(Summary!G5=0,"",Summary!G5)

Press Enter.

Importing data by the IF function to create a workback schedule

Drag the Fill Handle icon to your right to get all other four entities up to cell F5.

Select the range of cells B5:F5, and drag the Fill Handle icon to copy the formula to cell F9.

You may notice that the Start Date and End Date columns show some random numbers instead of dates.

Unformatted dates in the workback schedule report

To fix this issue, select the range of cells E5:F9, and from the Number group, choose the Short Date formatting located in the Home tab.

Modifying date format to create workback schedule report

The data importing task is finished.

Import Dataset into Detail Workback Report to Create a Workback Schedule

Method 4 – Creating Workback Gantt Chart

Write the dates of the corresponding month.
The project’s first day will be the Gantt chart’s first date. To get the date, select cell G4 and write down the following formula.

=E5

Press Enter.

Inserting first date of the project in the workback schedule

Select cell H4 and write down the following formula to get the next date.

=G4+1

Press Enter.

Estimating all the month dates

Select the H5 and drag the Fill Handle icon to get all the dates of that month up to cell AJ4.

Select cell G5 and write the following formula using the IF and AND functions.

=IF(AND(G$4>=$E5,G$4<=$F5),"X","")

Breakdown of the Formula

We are breaking down the formula for cell G5.

AND(G$4>=$E5,G$4<=$F5): The AND function will check both logics. If both logics are true, the function will return TURE. It will return FALSE. For this cell, the function will return TRUE.

IF(AND(G$4>=$E5,G$4<=$F5),”X”,””): The IF function will check the result of the AND function. If the result of the AND function is true, the IF function returns “X”. On the other hand, the IF function will return a blank.

Press Enter.

Getting the value using the IF and AND function to create a workback schedule

Drag the Fill Handle icon to your right up to cell AJ6.

Select the range of cell G5:AJ5 and drag the Fill Handle icon to copy the formula to AJ9.
You will see all the dates along the job, which show the value X.

Using the Fill Handle icon to copy the formula

In the Home tab, click on the drop-down arrow of the Conditional Formatting > Highlight Cell Rules option from the Style group and choose the Text that Contains command.

Applying conditional formatting to get the desired cell color in the workback schedule report

The Text That Contains dialog box will appear.
Write down X in the empty field, and select the Custom Format option in the following empty field.

Fill up the empty fields