How to Create a Weekly Calendar in Excel (3 Suitable Ways)

If you have a weekly calendar, then you can do all your work more routinely. Furthermore, a weekly calendar will help you to maintain your daily necessities. Thus, in this article, I will demonstrate how to create a weekly calendar in Excel.

Watch Video – Create a Weekly Calendar in Excel

Here, I will explain 3 suitable and easy methods for creating your weekly calendar. So, you can choose any of them to create the weekly calendar. Moreover, you can modify all those methods according to your preference.

1. Manually Creating a Weekly Calendar in Excel

Here, you can create a weekly calendar manually in Excel. In addition, I will use some Excel features to make my calendar. Basically, I will use different formatting to do this. The steps are given below.

Steps:

Firstly, you may write all days of the week with a certain date in Excel.
Next, you should include the initial time for your day.

Below, I have written the days and times.

Now, let’s make a calendar for every hour of the day.

So, write down the following formula in the B7 cell.

=B6+TIME(1,0,0)

In this formula, I have used the TIME function to add 1 hour.

Subsequently, press ENTER to get the result.

Now, I will copy the same formula using relative cell references. To do this, select cell B7. A square box will be shown in the bottom-right corner of cell B7, it is called the Fill Handle icon. Click the Fill Handle icon, hold it, and drag until you reach cell B24.
Then, press the Mouse button.

So, you will get all the required time. Or, you can just fill 2 or 3 cells manually and then drag the Fill Handle icon up to the last cell. In this case, also you will get the same time period.

Now, select the whole data range.
Then, from the Home tab >> go to the Format as Table feature.
After that, choose your preferred color combination.

At this time, a dialog box named Create Table will appear.

Here, make sure that you have selected your full data range.
Also, check the My table has headers option.
Finally, press OK.

Now, I will format my weekly calendar.

Firstly, select the cells where you want to put the same color.
Next, from the Home tab >> go to the Fill Color option.
Then, choose a preferable Theme color.

Lastly, you will get the following weekly calendar.

An Example of a Weekly Calendar in Excel

Here, I have attached my own weekly calendar as an example. I think this image will help you to visualize more about a weekly calendar and you will be able to make your own weekly calendar.

2. Using Data Validation Feature to Create a Dynamic Weekly Calendar

You can also create a dynamic weekly calendar using the Data Validation feature in Excel. The steps are given below.

First, write down the necessary terms for your calendar in a new worksheet. Here, you can get help from method-1.

As you can see, I have written down the following terms in the worksheet named Dynamic.

Now, write down all the days of the week in a different worksheet. Here, I have written the day’s name in the WeekDays worksheet.

Now, go back to the Dynamic worksheet and select cell G6.
Then, from the Data tab >> go to the Data Tools command >> then select Data Validation… option from the Data Validation feature.

At this time, a window named Data Validation appears.

Now, select List to the Allow box.
Then, select the reference in the Source box. Here, go to the worksheet named WeekDays and choose B4:B10 cells.
Finally, press OK.

As a result, you will see a drop-down arrow beside the G6 cell.

Now, from the Home tab >> do Merge & Center to the cells B2:E2.
Then, write down the following formula in the cell.

="Calendar for " & G6

Here, I have joined the text with G6 cell value using Ampersand operator (&).

Subsequently, press ENTER.

Now, if you select any days from the drop-down arrow, then you will see the title of the calendar has been modified to that day.

Now, select the whole data range.
Then, from the Home tab >> go to the Format as Table feature.
After that, choose your preferred color combination.

At this time, a dialog box named Create Table will appear.

Here, make sure that you have selected your full data range.
Also, check the My table has headers option.
Finally, press OK.

Finally, you will get the dynamic weekly calendar.

3. Using Excel Templates to Create a Weekly Calendar

The most interesting part is that Excel has its own templates for the weekly calendar. You can easily use that. Now, let’s talk about how you will get the template.

Firstly, open an Excel workbook.
Next, go to the File tab from the top ribbon.

Now, from the New menu >> choose Weekly chore schedule.

At this time, a new window named Weekly chore schedule appears.

Here, click on the Create option.

Now, you will get the following weekly chore schedule.

Here, you can modify this as you wish. Each and every term of this template is editable.

Furthermore, I have changed the title to Weekly Calendar and also I have added time for my calendar.

As you can see I deleted the Task column for my calendar.

Creating a Weekly Planner in Excel

In this section, I will create a weekly planner in Excel.

To do so firstly, you have to make a weekly calendar. Here, you can follow method-1.

Now, open a new worksheet.
Then, write your Work List in that worksheet. Here, I have written those in a worksheet named To Do List.

Now, go back to the Planner worksheet and select the data range of the calendar.
Then, from the Data tab >> go to the Data Tools command >> then select Data Validation… option from the Data Validation feature.

At this time, a window named Data Validation appears.

Now, select List to the Allow box.
Then, select the reference in the Source box. Here, I have gone to the worksheet named To Do List and choose B3:B14 cells.
Finally, press OK.

As a result, there will be a drop-down arrow beside all the cells. So, you can select any of your work from that drop-down arrow.

Remember one thing, if you want to add any new work then you have to input that in the To Do List worksheet. Also, you can’t insert any text or values in the cells of your weekly calendar. Perhaps, you must choose the works.

As an example look at the following image, I have filled some cells using the drop down arrow.

Download Practice Workbook

You can download the practice workbook from here:

Weekly Calendar.xlsm

Conclusion

I hope you found this article helpful. Here, I have explained 3 suitable methods to create a Weekly Calendar in Excel. Please, drop comments, suggestions, or queries if you have any in the comment section below.

Get FREE Advanced Excel Exercises with Solutions!