How to Create a Digital Planner in Google Sheets

Creating a digital planner in Google Sheets is not only cost-effective but also highly customizable. Unlike pre-made planners, a Google Sheets planner allows users to design layouts and formats that suit their specific schedules and tasks. Additionally, because Google Sheets is cloud-based, your planner is accessible from any device with an internet connection, ensuring you can update and review your plans on the go.

Why Use Google Sheets for Digital Planning?

Google Sheets is an ideal tool for digital planning due to its accessibility, customization options, and collaborative features. Here are some reasons why it's a great choice:

Accessibility: As a cloud-based platform, Google Sheets is accessible from any device with an internet connection, allowing you to update and view your planner on-the-go.
Customization: Google Sheets offers extensive formatting options, formulas, and data validation rules, enabling you to tailor your planner to your specific needs.
Collaboration: Share and collaborate on your planner in real-time, making it ideal for teams or families to coordinate schedules and tasks.
Cost-Effective: Google Sheets is free to use, making it a budget-friendly alternative to many subscription-based digital planning apps.

How to Create a Digital Planner in Google Sheets?

To plan our year ahead a functional dynamic calendar planner with interactive UI is a must. With the help of Google Sheets, we can easily have one. So here are the steps to do that.

Working on Google Sheets can be confusing sometimes as it offers a lot of shortcuts and formulas, so try to follow my tutorial and do the same, and then you can play around and make a dynamic calendar that matches your aesthetic.

Step 1: Creating the calendar area (Week-wise)

Select a cell (here I have selected F4) and click on

Data > Data Validation > Add rule.

Creating the calendar area

In Add Rule,
- Go to the criteria drop-down and select "Is valid date"
- Then select Reject the Input and click on "Done".
This will give your cell (F4) a calendar drop-down every time you click on it.

select "Is valid date" then select Reject the Input and click on "Done"

Select a date of your choice for F4 from the drop-down calendar.

Select a date of your choice for F4 from the drop-down calendar.

Now, to get 7 consecutive days, select the cell next to F4 and apply the formula "=F4+1" and enter. This will give you the next date.

select the cell next to F4 and apply the formula "=F4+1"

Repeat this to get 7 consecutive days (as we are making week week-wise calendar).
Leave 6 rows below your dated column (to add tasks) and add more dates (as shown in the pictures below).

Leave 6 rows below your dated column

Step 2: Customising the calendar area

To change the way the dates look,

Go to format > Number > Custom Date and Time.

gfg21

Now from Custom date and time select the way you want your date to look.

Now from Custom date and time select the way you want your date to look.

Also, to change the alignment to centre, select the whole table from the corner and change the alignment to centre.
You can also add borders to the dates and the space below them by selecting the dated rows and clicking on the border ( as shown in the picture).

add borders to the dates and the space

Step 3: Adding a row for days

This will increase the functionality of our calendar as it will show the days above the dates.

Select the F2 column write "=F4" and press ENTER. You will get the same date as F4.

Adding a row for days

Now select the whole row of F2 and

Go to Format > Number > Custom Date and Time.

Go to Format > Number > Custom Date and Time.

From Custom date and time select the option to show only the day.

From Custom date and time select the option to show only the day

Drag the next 7 rows to get the same dates as the F4 rows (refer to the picture for a better understanding).

Drag the next 7 rows to get the same dates as the F4 rows

Step 4: Creating the task area

Select column A, press ctrl (command for Mac users) select column E then decrease the width as per your choice.

Creating the task area

Then add labels to columns A, B and C as S.no./#, Date and task respectively.

add labels

For column B i.e. Serial number column, in order to add numbers we can write the formula "=sequence(50,1,1,1)" and this would give us a sequence of 1-50 numbers. You can add more numbers to it as per your need.

write the formula "=sequence(50,1,1,1)" — Entering the formula

For column C i.e. the date column, repeat the same steps to add a drop-down calendar and convert it to the same format as the calendar area.

repeat the same steps to add a drop-down calendar

For column D i.e. the task area, we want to create it in such a way that when we write a task for a respective day it should be displayed in the calendar area. This can be done by:
Go to F5 cell and write "=FILTER(D4:D53, C4:C53 = F4)". This will put a filter to our calendar area stating that range D4:D53 (range of task) will be displayed here if the condition C4:C53 (means from cell C4 to cell C53) is equal to the date/font of F4.
Repeat the same for all the other dates by changing the RHS after equal to the current date cell ( as shown in the picture).

488

In case there is no task for a particular date, N/A will be printed.
Now, if you put a date and a task next to that and hit ENTER in the task area, it will automatically appear in the calendar area.

hit ENTER in the task area

Step 5: Customising the dynamic calendar

Select the task area column and add borders of your choice.
Add colours to your calendar to make it look more attractive.

Add colours to your calendar

You can add more dates as per your requirement and basically play around.
The lines in the spreadsheet can also be removed by clicking on View > gridline.

clicking on View > gridline