Our Small Business PTO Manager Excel template can be used to calculate and report PTO balances for employees in a company. One of the features of the template is the Calendar which shows the monthly view of PTO taken/scheduled by the employees. By default, this sheet was limited to show 40 employees at a time so that it can fit within 1 page to print. If your company has more than 40 employees to track PTO, then this could be a limiting factor. In this blog post, we will learn how to extend the Calendar view to show more than 40 employees.

The default calendar view looks like this.

PTO Manager - Calendar View - Default
PTO Manager – Calendar View – Default

 

It would stop with 40th Employee.

Calendar shows Employees 1 to 40
Calendar shows Employees 1 to 40

 For this exercise, let’s assume we have 80 employees in our company. We will use the following simple steps to view more than 40 employees.

VIDEO DEMO


Option #1 (40 employees at a time)

If you don’t need to see data for all 80 employees in the calendar at the same time, then we have a very easy solution already built-in.

Just type 41 in cell A7.

 

PTO Manager Calendar - Enter different starting number
PTO Manager Calendar – Enter different starting number

 

Now, you will be able to see data for employees 41 to 80.

Calendar shows Employees 41 to 80
Calendar shows Employees 41 to 80

 

Option #2 (More than 40 employees at a time):

This involves a few more steps.

 

Before we begin, please save a copy of the file for backup. We will be making formula changes and it is always recommended to create a backup.

Tip: It is also recommended to create backups regularly even if you don’t make formula changes.

 

Step 1: Unprotect sheet

Please unprotect sheet with password indzara

Unprotect Sheet
Unprotect Sheet

 

Step 2: Select Row 45

Click on 45 to select row 45.

Click on 45 to select row 45
Click on 45 to select row 45

 

Step 3: Right click and choose ‘Copy’

Right Click and Choose Copy
Right Click and Choose Copy

 

Step 4: Select rows 46 to 86

Select rows 46 to 86 (we need to select 1 row more than our desired expansion. Since we want to expand by 40 rows, we select 41 rows)

Step 5: Right click and choose ‘Insert Copied Cells’

Right click and choose Insert Copied Cells
Right click and choose Insert Copied Cells

 

Step 6: Select row 87

Click on 87 to select row 87

Click on 87 to select Row 87
Click on 87 to select Row 87

 

Step 7: Clear Row 87

Right click and choose ‘Clear Contents’

Right click and choose Clear Contents
Right click and choose Clear Contents

 

Step 8: Change Print settings

Since the calendar will be more than 1 page long, we need to make some modifications to the Print Settings.

First, Click on ‘Print Titles’ in the Page Layout ribbon.

Select Print Titles
Select Print Titles

 

Set rows 1 to 6 to repeat. This allows the 6 rows to print on second page (employees 41 to 80) as well – allowing the reader to align the dates to the calendar.

Select rows 1 to 6 to repeat in the Page Setup
Select rows 1 to 6 to repeat in the Page Setup

 

Step 9: Protect sheet

Now we are done with making the changes to extend the calendar. We can protect the sheet again to prevent unintentional editing of formulas.

Protect sheet
Protect sheet