Sales Calendar with Top 3 Highlighting in Google Sheets

The sales calendar with the top 3 highlighting template may help you see and understand your sales data better in Google Sheets.

I’ve earlier shared a Reservation and Booking Status Calendar Template and got good responses from users.

This time, we have a sales calendar sheet with two option boxes (drop-downs) to select the sales year (cell B1) and types of highlighting (cell B2). Please see the image below.

Based on your selection of the sales year in the first drop-down, the sales calendar will refresh.

The second drop-down (types of highlighting) is for processing the sales data in another tab based on your choice. The highlighting happens accordingly.

Template for Sales Calendar with Top 3 Highlighting — screenshot # 1

The types of highlighting are DAYS, WEEKS, and MONTHS.

Assume the selected year (first drop-down) is 2022.

The Sheet (template) should populate a sales calendar for that year.
Then based on the top # 3 highlighting type (second drop-down), the Sheet should aggregate the sales data and highlight the relevant dates on the calendar.

Let’s understand the highlighting types (second drop-down).

DAYS:- To find and highlight the dates with the top # 3 sales in the selected year.
WEEKS:- Option to find the weeks (based on Sunday-Saturday Week numbers) with the top # 3 sales in the whole year and highlight those weeks in the sales calendar.
MONTHS:- Option to highlight the top # 3 sales months in the selected year.

There will be two tabs in our template for the sales calendar with the top 3 highlighting – One for entering your daily sales data and the other for the calendar.

Sales Calendar Template

Sales Calendar with Top 3 Highlighting – Usage Instructions

You can use the above button to make a copy of my Google Sheets for sales calendar with top # 3 highlighting.

There are three tabs in that workbook (file). They are “Sales Calendar,” “Sales,” and “Instructions.”

We require the first two tabs and let’s start with the second tab, i.e., “Sales.”

Sales Tab

You can use columns A to H (eight columns) to enter your sales data.

The field labels in these columns are “Date,” “Item Code,” “Item Desc.,” “Company,” “Qty.,” “P.O. No.,” “Unit Price,” and “Amount.”

Among these, we require the first (A) and last (H) columns for highlighting the top 3 values in the sales calendar.

Feel free to replace my mockup data in these two columns with your original data.

You can use the other columns (B-G) for entering any data. But do not change the data types of the first and last columns.

There are hidden columns after column I. I’ll come to their use in the formula part. Do not modify them.

Sales Calendar Tab

Please scroll up and see screenshot # 1.

It’s the sales calendar with the top 3 value highlighting tab. We can use it to see and understand our sales data.

There are two drop-downs in this tab that you can find in cells B1 and B2.

Select the sales year in B1. It refreshes the sales calendar on the right.

Then select what type of highlighting you require in B2 to control the highlighting on the calendar.

I’ve preset the years 2020, 2021, and 2022 in B1. You can modify it by going to Data > Data validation from within this cell.

Sales Calendar with Top 3 Highlighting – Formulas and their Purposes

I’ve used a few formulas within the “Sales” and “Sales Calendar” tabs.

Here are the formulas in the “Sales” tab and their purposes.

Aggregating the Sales Data (All the Formulas Used in the Sales Tab)

Formula # 1 in J2

To see the formulas, click on the plus button at the top of column I. There are a few columns hidden there due to grouping.

In cell J2, the following formula returns the end of the month dates of column A.

=ArrayFormula(if(A2:A="",,eomonth(A2:A,0)))

How does this formula useful in our sales calendar template for the top 3 value highlighting in Google Sheets?

We will group this column to get the top 3 sales months.

Formula # 2 in K2

The following K2 formula returns the week numbers of the dates in column A.

=ArrayFormula(if(A2:A="",,weeknum(A2:A)))

We have used this for highlighting the top 3 sales weeks in our sales calendar for the top # 3 highlighting.

Yep! We are using week numbers to determine the weeks.

The first week of the year is considered to be the week containing 1st January and numbered week 1.

Further, we have used Sunday as the start day and Saturday as the end day of the week for counting.

Formula # 3 in N1

The following SWITCH is one of the main formulas in our sales calendar with the top 3 value highlighting template in Google Sheets.

=switch('Sales Calendar'!B2,"DAYS",1,"MONTHS",10,"WEEKS",11)

It returns the column number (A [1], J [10], or K [11]) based on your highlighting type (days, months, or weeks) selected in ‘Sales Calendar’!B2.

The following QUERY uses that number to determine the grouping, whether it is based on dates (1), months(10), or weeks (11).

Formula # 4 in O1

=sortn(query({Sales!A2:K},"Select Col"&N1&",sum(Col8) where year(Col1)="&'Sales Calendar'!B1&" group by Col"&N1&" label sum(Col8)''"),3,1,2,0)

The data range is A2:K, not A2:H.

The QUERY part picks the column numbers 1 (days), 10 (months), or 11 (weeks) for grouping from cell N1 returned by the formula # 3 above and aggregates the data.

In other words, it uses columns 1, 10, or 11 and column 8 for aggregating the data.

The outer SORTN sorts the queried/processed data and returns the top 3 values for highlighting in the sales calendar.

Further, it helps return additional values if the third value in the top 3 has duplication. Please check SORTN tie-modes to understand the usage.

Formulas for Populating a Sales Calendar (Sales Calendar Tab)

We can use different formula combinations to make a (sales) calendar in Google Sheets. Here are a few options.

But I am following none of them because they use a single formula to generate the whole month’s calendar.

The issue is that they won’t support inserting rows below dates to key in notes if any.

To see the formulas used for creating the sales calendar for top # 3 highlighting, go to the menu View > Show > Formulae.

To learn the formula please read my tutorial here (coded for Excel, but will equally work in Google Sheets).

Highlight Rules for Sales Calendar with Top 3 Highlighting and Logic

We require only one rule for highlighting the max 3 sales on the calendar.

But to get different colors for top 1 (green), 2 (red), and 3 (orange), I’ve used three rules.

Also, please note that we do not require to match all the dates in the calendar. Only the first date in E2 is enough. Sheets will take care of the rest of the dates.

Here are the rules.

Green:

=if($B$2="DAYS",E2=indirect("Sales!O1"),if($B$2="MONTHS",eomonth(E2,0)=indirect("Sales!O1"),if($B$2="WEEKS",weeknum(E2,1)=indirect("Sales!O1"))))

Red:

=if($B$2="DAYS",E2=indirect("Sales!O2"),if($B$2="MONTHS",eomonth(E2,0)=indirect("Sales!O2"),if($B$2="WEEKS",weeknum(E2,1)=indirect("Sales!O2"))))

Orange:

=if($B$2="DAYS",E2=indirect("Sales!O3"),if($B$2="MONTHS",eomonth(E2,0)=indirect("Sales!O3"),if($B$2="WEEKS",weeknum(E2,1)=indirect("Sales!O3"))))

Logic (first highlight rule):

The IF formula evaluates the highlighting type in cell B2 and applies the highlighting as below.

If B2=”DAYS,” the formula matches the date in E2 (the first cell in the calendar) with the value in Sales!O1.

If B2=”MONTHS,” it matches the end of the month date of E2 with the value in Sales!O1.

Finally, If B2=”WEEKS,” the formula matches the week number of E2 with the value in Sales!O1.

In other rules, O1 becomes O2 and O3.

I hope you find my sales calendar with the top # 3 highlighting template worthy.

Thanks for the stay. Enjoy!