Month and Year Sparkline

Month and year sparklines are used for spotting monthly and annual trends in a given set of data. You can showcase the difference in data sets using a color range.

A month sparkline has 6x7 square spaces where a week of the month is displayed in horizontal direction from left to right and the days of the week (from Sunday to Saturday) are displayed in vertical direction from top to bottom. There are white separator lines among days.

A year sparkline has 54x7 square spaces where the weeks of year is displayed in horizontal direction from left to right and The days of the week (from Sunday to Saturday) are displayed in vertical direction from top to bottom.

There are white separator lines among days and black separator lines among months.

The month sparkline formula has the following formats:

=MONTHSPARKLINE(year, month, dataRange, emptyColor, startColor, middleColor, endColor)

or

=MONTHSPARKLINE(year, month, dataRange, colorRange)

The year sparkline formula has the following formats:

=YEARSPARKLINE(year, dataRange, emptyColor, startColor, middleColor, endColor)

or

=YEARSPARKLINE(year, dataRange, colorRange)

The formula options are described below:

Usage Scenario

Consider a scenario where a company receives several query posts in its support forum all around the year. A month and year sparkline are convenient to observe the forum section activity. They can help identify the days with the most and least amount of site traffic in a specific month or a year.

// Get sheet
var worksheet = fpSpread1_Sheet1.AsWorksheet();
            
// Set data
DateTime dates = new DateTime(2020, 1, 1);

// Initialize Random number
Random _rand = new Random();
            
for (var row = 3; row <= worksheet.RowCount; row++)
{
    worksheet.Cells[$"A{row}"].Value = dates.AddDays(row - 3);
    worksheet.Cells[$"B{row}"].Value = Math.Round(_rand.NextDouble() * 50);
    worksheet.Cells[$"C{row}"].Value = colorList[_rand.Next(0, colorList.Length)];
}

// Set month sparkline formula
worksheet.Cells["D3"].Formula2 = "MONTHSPARKLINE(2020,1,A3:B33,C3:C33)";
worksheet.Cells["D9"].Formula2 = "TEXT(DATE(2020,1, 1),\"mmmm\")";
worksheet.Cells["D12"].Formula2 = "MONTHSPARKLINE(2020,2,A34:B62,C34:C62)";
worksheet.Cells["D18"].Formula2 = "TEXT(DATE(2020,2, 1),\"mmmm\")";

// Set year sparkline formula
worksheet.Cells["E3"].Formula2 = $"YearSparkline(2020, A3:B{worksheet.RowCount}, C3:C368)";

'Get sheet
Dim worksheet = FpSpread1_Sheet1.AsWorksheet()
        
'Initialize random number
Dim _rand As New Random()

'Set data
Dim dates As New DateTime(2020, 1, 1)

For row = 3 To worksheet.RowCount
    worksheet.Cells($"A{row}").Value = dates.AddDays(row - 3)
    worksheet.Cells($"B{row}").Value = Math.Round(_rand.NextDouble() * 50)
    worksheet.Cells($"C{row}").Value = colorList(_rand.[Next](0, colorList.Length))
Next

'Set month sparkline formula
worksheet.Cells("D3").Formula2 = "MONTHSPARKLINE(2020,1,A3:B33,C3:C33)"
worksheet.Cells("D9").Formula2 = "TEXT(DATE(2020,1, 1),""mmmm"")"
worksheet.Cells("D12").Formula2 = "MONTHSPARKLINE(2020,2,A34:B62,C34:C62)"
worksheet.Cells("D18").Formula2 = "TEXT(DATE(2020,2, 1),""mmmm"")"

'Set year sparkline formula
worksheet.Cells("E3").Formula2 = $"YearSparkline(2020, A3:B{worksheet.RowCount}, C3:C368)"

Using the Spread Designer

1. Type the Data Range in cells where the first column is a date and the second column is a number.

2. Select a cell for the sparkline.

3. Select the Insert menu.

4. Select a sparkline type.

5. Set the Data Range in the Sparkline Dialog (such as =Sheet1!$E$1:$E$3).

   Alternatively, set the range by selecting the cells in the range using the pointer.

   
   

   
   

   Set the additional sparkline settings as shown in the images above.

6. Select OK.

7. Select Apply and Exit from the File menu to save your changes and close the designer.