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.
It would stop with 40th Employee.
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.
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.
Now, you will be able to see data for 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
Step 2: Select Row 45
Click on 45 to select row 45.
Step 3: 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’
Step 6: Select row 87
Click on 87 to select row 87
Step 7: Clear Row 87
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.
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.
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.