Excel Functions Tutorials
- Excel Charts
- Types of Charts in Excel
- Area Chart in Excel
- Bubble Chart in Excel
- Chart Templates in Excel
- Change Chart Style in Excel
- Calendar Template in Excel
- Inventory Template in Excel
- Clustered Bar Chart in Excel
- Clustered Column Chart in Excel
- Column Chart in excel
- Combination Charts in Excel (Combo)
- Excel Combo Chart
- Control Charts in Excel
- Doughnut Chart in Excel
- Dynamic Chart in Excel
- Excel Chart Wizard
- Flow Chart in Excel
- Flowchart Excel Examples
- Funnel Chart in Excel
- Gantt Chart in Excel
- Gantt Chart Example
- Gauge Chart in Excel (Speedometer)
- Graphs vs Charts
- Histogram Excel Chart
- How to Make Graph / Chart in Excel?
- Legends in Excel Chart
- Line Graphs / Charts in Excel
- Line Chart Examples
- Marimekko Chart in Excel (Mekko)
- Normal Distribution Graph in Excel
- Organization Chart in Excel
- Pareto Chart in Excel
- Pie Chart in Excel
- Make Pie Chart in Excel
- Pivot Chart in Excel
- Radar Chart in Excel (Spider Chart)
- Stacked Chart in Excel (Column, Bar & 100% Stacked)
- Stacked Bar Chart in Excel
- Stacked Column Chart
- Stock Chart in Excel
- Scatter Plot in Excel (Chart)
- Tornado Chart in Excel
- Dot Plots in Excel
- 3D Plot in Excel
- 3D Scatter Plot in Excel
- Standard Deviation Graph in Excel
- S Curve in Excel
- Waterfall Chart in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Tools (98+)
- Excel Tips (178+)
- VBA (162+)
Excel Calendar Template
Excel has a wide variety of useful tools but one of the criticism is it doesn’t have any built-in calendar tool. Yes we don’t have any built-in calendar, I know you have seen some of the calendar templates in excel but those are created by excel users. We need to create a calendar template in excel by using excel formulas.
In this article, we will show you how to create a calendar template in excel. We can create calendar templates in two ways – one with a simple excel calendar and the second one a dynamic calendar with formulas.
How to Create a Calendar Template in Excel?
#1 – Simple Excel Calendar Template
Simple excel calendar template doesn’t require any special formulas, with simple techniques we can create a simple calendar.
Step 1: The first thing we see with the calendar is Month with Year name. So for this, we need to create a Month Name. For this first apply TODAY formula which takes the current date as per system.
Today is a volatile function it automatically updates the date as per the system date, to today’s date is 17th June 2019, so tomorrow it will be 18th June 2019.
However, we don’t need a full date like the above we need only Month & Year together. So apply the date format as “MMMM YYYY”.
Now we have a proper month & year.
Step 2: Now in the below row enter weekdays starting from Sunday to Saturday. And merge the A1 cell until the G1 cell.
Step 3: Now we need to create calendar days like 1, 2, and 3 and so on.
I have entered the day with Sunday, 2nd Saturday, 4th Saturday, etc…
This is ordinary stuff which needs to change the dates every month manually. But how do we make this dynamic so that it shows the updated month with days? Let’s see this now.
#2 – Dynamic Excel Calendar Template with Formulas
For the same template as above, we will create a dynamic calendar.
Step 1: To the left of the template insert Spin Button from Developer tab.
Step 2: Right-click on the spin button and choose format control.
Step 3: Under format control make below changes.
Step 3: Since we are creating the calendar for the specific month we will create 12 different sheets for 12 different months. For this, we need to create 12 different name managers. First name the cell I3 as “Year”.
First, we will create for January 2019. So change the heading of the month from TODAY formula to as follows.
Now for this month, we need to create one more name manager as shown below.
Similarly, by changing the month number create a name manager for all the months.
Step 4: Now with the same template above insert 12 sheets and name each sheet with 12-month names.
Step 5: Now in the “Jan” Sheet in the first-day column apply below formula.
Copy and paste the formula to the next cell but change the number 1 to 2 everywhere.
Similarly for the next change it to 3.
Similarly, increase everything by 1.
Step 6: Copy the above template to FEB sheet. Here only thing we need to change is MONTH from 1 to 2.
Apply the same logic for all the worksheets. Change the month accordingly.
Once all the sheets are done with template changing and formula, we can control the template from “Jan” Sheet.
Step 7: Using the “Spin” button we can change the Year in “Jan” sheet, accordingly when we change the year in “Jan” sheet in all the sheets the selected year’s dates will show up.
This has been a guide to Calendar Template in Excel. Here we discuss how to create simple and dynamic calendar template in excel using formulas with downloadable excel sheet. You can learn more about excel from the following articles –