Skip to main content

Year Sparklines

A year sparkline is a compact calendar-style chart that shows data trends across an entire year. It consists of a 54×7 grid where each square represents a day. Columns represent weeks (1 to 54, left to right), and rows represent weekdays (Sunday to Saturday, top to bottom). Cell colors reflect value intensity, from minimum to maximum, using a gradient based on selected colors.

When to use year sparklines​

  • Tracking yearly trends: Visualize data over all 12 months to highlight seasonal patterns, growth, or declines.
  • Comparing year-to-year performance: Show how metrics such as sales or site traffic change from one year to the next.
  • Summarizing long-term behavior: Present large datasets in a clear, compressed view without showing daily details.

Create a year sparkline​

  1. On the Insert tab, select Sparklines.
  2. Under Calendar Sparkline, select Insert Year Sparkline. The YearSparkline Dialog opens.
  3. Set the parameters.
  4. Click Ok. The sparkline is inserted into the selected cell.

Edit a year sparkline​

  1. Select the year sparkline in your workbook.
  2. On the Sparkline tab, select Settings. The YearSparkline Dialog opens.
  3. Adjust the parameters, then click Ok.
  4. Copy and paste the sparkline cell to duplicate the configuration in another location, as needed.

Year sparkline parameters​

The year sparkline formula supports the following formats:
=YEARSPARKLINE(year, dataRange, emptyColor, startColor, middleColor, endColor)
=YEARSPARKLINE(year, dataRange, colorRange)

ParameterDescription
Data RangeSpecifies a range where the first column contains dates and the second column contains values, such as A1:B400.
Location RangeSets the cell where the sparkline will appear.
YearDefines the year (four-digit format), such as 2024.
MonthAlways sets the month to 1 (January). This value cannot be changed.
Empty ColorSets the fill color for days with no value or a value of zero.
Start ColorSets the fill color for the lowest data value in the year.
Middle ColorSets the fill color for the midpoint between the minimum and maximum values.
End ColorSets the fill color for the highest data value in the year.
Range ColorAccepts a cell range that defines color values directly.