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.

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.

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.

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.

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.

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.

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.

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

=MAX(K:K)

Press Enter.

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.

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.

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.

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.

The data importing task is finished.

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.

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

=G4+1

Press Enter.

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.

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.

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.

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.