Creating an Excel Schedule Template
How to Make a Schedule in Excel
Working in a busy accounting office gives me a diverse workload; with problems trying to efficiently plan time it is often important to create a work schedule to provide an overview of workload and therefore be able to plan accordingly. While professional tools like Microsoft Project give you plenty of options to design detailed work plans, the complexity and steep learning curve often are too much for most.
Therefore creating a schedule template within Excel that uses some of the powerful functionality of conditional formatting will provide a simple yet powerful solution. The end result is an easy to use tool that allows tasks to listed providing a simple Gantt chart for a great overview of workload.
The instructions below will guide you step by step on how to create a schedule template in Microsoft Excel:
Step one – Creating a Basic Schedule Layout in Microsoft Excel.
The first thing to do is decide what information is needed on the schedule. As an example the following information will be incorporated into the design:
- The Task Name
- Start Date
- End Date
- A range of dates – in this case the schedule will display the next 25 days
- Comments – to add details about the task
Start by creating a basic worksheet. Enter the date in column D and then add a simple formula to the subsequent cells that will add one to the prior cell. For example – cell E1 has the formula =+D1+1.
The format of the worksheet does not matter at this time. The example uses simple formatting, with the dates aligned vertically. Generic data is added so that any formulas and formats applied to the worksheet work as intended.
Step 2 – Adding Formulas to Create the Excel Schedule
The Gannt part of the schedule will be represented by a ‘bar’ that corresponds to the start date and end date. To accomplish this add a formula in each cell so that it compares the Start Date and End Date of each column to the date of the row – if the date of the row is within the range then the formula will place an x in that cell, otherwise it will leave the cell blank.
The formula in cell D2 is: =IF(AND(D$1>=$B2,D$1<=$C2),"x","")
This is a fairly simple formula using the =IF function: =IF(Condition, True, False)
Condition: AND(D$1>=$b2,D$1<=$C2) – this is checking that the date in D1 is greater or equal to the date in B2 and less or equal to the date in C2 – if it is then it will use the ‘True’ result, otherwise it will use the ‘False’ result). Note – the formula uses the $ sign for two different reasons:
- D$1 – this ensures that when the formula is copied down and across, the D will change to the relevant column while the 1 will remain the same.
- $b2 – this ensures that when the formula is copied down and across, the B will remain the same but the 2 will change to the relevant row number.
In the diagram above, the formulas have placed an x in the relevant rows and columns to coincide with the Start and End Dates.
Step 3 – Using Conditional Formatting to Format the Schedule.Click thumbnail to view full-size
Using Conditional Formatting in Microsoft Excel:
- Select the area to be conditionally formatted
- On the Home menu on the Microsoft Excel Ribbon click Conditional Formatting.
- Click on New Rule
- Select Use a formula to determine which cells to format and enter the formula in Format values where this formula is true
- Click Format to amend the format of the cell (when the above formula returns a true value) – click OK to return back once the format has been selected.
- Click OK to apply the format.
Highlighting today’s date
To make 'today' stand out more a conditional formula can be used to change the format of the cell on row 1 of the worksheet. Using the conditional formatting method (right):
- Area: Cells A1 to AC1 (apply this format to the top row only)
- Use formula: =IF(A1 = TODAY(), 1, 0) (This will compare every value in the selected range to today’s date – if it is the same it returns TRUE and formats that cell based on the selection. The cell reference after the IF should be the top left cell in the range)
- Format: Use Fill -> Fill Effects to create the format.
To break up the schedule and also to provide additional information about work days and weekends conditional formatting can be used to highlight any dates that fall on the weeked. This format will be applied to the entire schedule.
- Area: Cells A1 to AC6
- Use formula: =IF(AND(A$1 <>"", OR(WEEKDAY(A$1, 2)=6, WEEKDAY(A$1, 2)=7)), 1, 0) (This will return a value if the date in question is a Saturday or Sunday – the WEEKDAY function will convert any date into a number ranging from 1 to 7 – where 6 and 7 represent Saturday and Sunday)
- Format: Use gray background and black font color.
Highlighting today’s date in schedule section
The schedule already highlights today's date in row one. Applying a different format to today's date for all rows below is a good visual guide on the schedule.
- Area: Cells D1 to AB6
- Use formula: =IF(D$1 = TODAY(), 1, 0) (This will compare every value in the selected range to today’s date – if it is the same it returns TRUE and formats that cell based on the selection. The cell reference after the IF should be the top left cell in the range )
- Format: Use Fill -> Pattern Style to create the format.
Replace ‘x’ with blue ‘box’
The 'x' in the schedule provide a graphical view of the date range, however replacing the 'x' with a solid blue box will create a schedule that is similar to the tradional Gantt chart.
- Area: Cells D1 to AB6
- Use formula: =IF(D1="x", 1, 0) (This will compare every value to ‘x’ – if it is the same it returns TRUE and formats that cell based on theselection. The cell reference after the IF should be the top left cell in the range)
- Format: Use Fill and FONT color and use the same color for both.
The design of a simple Microsoft Excel schedule is complete. To add more tasks simply copy the last row and paste it into the next row – the conditional formatting will be copied too.