This template makes it easy for you to create a weekly school schedule. The template has hours divided into 10 minute intervals. You can easily change the conditional formatting color.

School-schedule-template-final2

Download template excel 2007 *.xlsx

school schedule.xlsx

How I created this dynamic template

Weekdays

  1. Select cell G1
  2. Type Monday
  3. Select cell H1
  4. Type Tuesday

Repeat above steps for remaining cells in cell range H1:M1 and weekdays.

10 minute intervals

  1. Select cell F2
  2. Type 8:00
  3. Select cell F3
  4. Type 8:10
  5. Select cell range F2:F3

Create headers

  1. Select cell A1
  2. Type Weekday
  3. Select cell B1
  4. Type Subject
  5. Select cell C1
  6. Type Start
  7. Select cell D1
  8. Type End

Create named ranges

  1. Go to tab "Formulas"
  2. Click "Name Manager" button
  3. Click "New..."
  4. Type Weekday in name field
  5. Refers to: =OFFSET(Sheet1!$A$2,0,0,MATCH("ZZZZZZZZZZZ",Sheet1!$A:$A)-1)
  6. Click OK

Repeat step 3 to 6 with these named ranges:

Subject : =OFFSET(Sheet1!$B$2,0,0,MATCH("ZZZZZZZZZZZ",Sheet1!$A:$A)-1)
Start : =OFFSET(Sheet1!$C$2,0,0,MATCH("ZZZZZZZZZZZ",Sheet1!$A:$A)-1)
End : =OFFSET(Sheet1!$D$2,0,0,MATCH("ZZZZZZZZZZZ",Sheet1!$A:$A)-1)

Enter array formulas

  1. Select cell G2
  2. Click in formula bar
  3. Type:
    =IFERROR(IF(SUMPRODUCT((Start=$F2)*(G$1=Weekday))=0, "", INDEX(Subject, SUMPRODUCT((Weekday=G$1)* (Start=$F2)*(MATCH(ROW(Weekday), ROW(Weekday)))))), "")
  4. Press and hold Ctrl + Shift
  5. Press Enter

Copy array formula

  1. Select cell G2
  2. Copy cell (Ctrl +c)
  3. Select cell range H2:M2
  4. Paste (Ctrl + v)
  5. Select cell range G2:M2
  6. Copy (Ctrl + c)
  7. Select cell range C3:M50
  8. Paste (Ctrl + v)

Create conditional formatting

  1. Select cell range G2:M50
  2. Go to tab "Home"
  3. Click "Conditional Formatting" button
  4. Click "New Rule..."
  5. Click "Use a formula to determine which cells to format"
  6. Type =SUMPRODUCT((G$1=Weekday)*($F2=Start))
    in "Format values where this formula is TRUE" field.
  7. Click "Format..." button
  8. Click Border tab
  9. Click to create three borders
  10. Click OK
  11. Click OK

Repeat above steps with remaining conditional formatting rules:

The border and fill formatting are also shown in the picture above.

1 : =SUMPRODUCT((G$1=Weekday)*($F2>Start)*($F2<End))

2: =SUMPRODUCT((G$1=Weekday)*($F2=End))

3: =SUMPRODUCT((G$1=Weekday)*($F2=Start))

4: =SUMPRODUCT((G$1=Weekday)*($F2>=Start)*($F2<End))

Download template excel 2007 *.xlsx

school schedule.xlsx