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.
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.
Simon Cook (author) from NJ, USA on December 20, 2012:
AMFredenburg: interesting idea - I'd probably have to expand it to show how to calculate difference in months, dates, years, hours etc......
Aldene Fredenburg from Southwestern New Hampshire on December 20, 2012:
Wonderful stuff! Do you have an article on how to make times add and subtract? For instance, if you have a beginning time of 2:00 p.m. and an end time of 3:15, can you make the spreadsheet figure out that the worktime is 1:15? And can you have accumulated times so that the total time spent on a project is provided (going beyond 24 hours)? If you don't have an article like this, maybe you can write one. : ))
Jools Hogg from North-East UK on July 11, 2012:
Excel is about the only piece of software at which I am considered 'a whizz'! Most of it is self-taught but I cannot praise Excel highly enough, I struggle to find anything that it cannot do :o)
This is well written and explained and an extremely useful template.
Dan Harmon from Boise, Idaho on July 10, 2012:
Sweet and simple. Good job, Simey, in explaining something that few would every think of doing.
Jasmine on July 10, 2012:
Hm, I should make a plan about publishing more hubs and use an Excel schedule template for it. Well explained, voted up!
Natasha from Hawaii on July 10, 2012:
I haven't read this much about Excel since my mandatory high school computer class! Thanks for the detailed instructions in plain English.