Skip to content

Countdown to Christmas in Excel with Christmas Tree & Lights

    A few years’ ago I created a video showing you how to create an animated Christmas tree.  See the video here https://www.bluepecantraining.com/portfolio/created-a-christmas-tree-with-sparkling-lights-in-excel/

    It’s Christmas time again and I thought it was time to create version 2.0 to include an automated countdown to Christmas and animated tree and stars, all on a Christmassy background.  Watch the video below and read the tutorial with all the formula and VBA code that follows.

    You can download the version I created here, but if you want to create your own here are some pointers. If you are going to download the file, I would recommend saving and closing any open workbooks before opening this one.

    Creating the Sparkling Christmas Tree

    This video will take you through how to create the Christmas tree with sparkling lights.

    Adding the Background

    On Excel’s Page Layout tab, click the Background button and choose your own background picture. 


    Adding Sparkling Stars

    You can do this using the star icons in conditional formatting. Use this formula in each cell that you want a star in:

    =RANDBETWEEEN(0,1)

    Then use the rules shown below.

    Countdown Text

    There are the three formulas for the countdown to Christmas text:

    The Countdown Formula

    =IF(TODAY()=DATE(YEAR(TODAY()),12,25),”It’s Christmas!”,IFERROR(DATEDIF(TODAY(),DATE(YEAR(TODAY()),12,24),”d”)&” Days “&HOUR(DATE(YEAR(TODAY()),12,25)+TIME(0,0,0)-NOW())&” Hours “&MINUTE(DATE(YEAR(TODAY()),12,25)+TIME(0,0,0)-NOW())&” Minutes “&SECOND(DATE(YEAR(TODAY()),12,25)+TIME(0,0,0)-NOW())&” Seconds”,DATEDIF(TODAY(),DATE(YEAR(TODAY())+1,12,24),”d”)&” Days “&HOUR(DATE(YEAR(TODAY())+1,12,25)+TIME(0,0,0)-NOW())&” Hours “&MINUTE(DATE(YEAR(TODAY())+1,12,25)+TIME(0,0,0)-NOW())&” Minutes “&SECOND(DATE(YEAR(TODAY())+1,12,25)+TIME(0,0,0)-NOW())&” Seconds”))

    The “Until Christmas Day …” Formula

    =IF(AND(TODAY()>DATE(YEAR(TODAY()),12,25),TODAY()<=DATE(YEAR(TODAY()),12,31)),”Until Christmas Day ” & YEAR(TODAY())+1,IF(TODAY()=DATE(YEAR(TODAY()),12,25),”Happy Christmas”,”Until Christmas Day ” & YEAR(TODAY())))

    The “Sleeps Left” Formula

    =IF(TODAY()=DATE(YEAR(TODAY()),12,25),”No Sleeps Left”,IFERROR(“(“&DATEDIF(TODAY(),DATE(YEAR(TODAY()),12,24),”d”)+1&” Sleeps)”,”(“&DATEDIF(TODAY(),DATE(YEAR(TODAY())+1,12,24),”d”)+1&” Sleeps)”))

    VBA to Automate the Countdown and Animation

    I WOULD RECOMMEND CLOSING ANY OTHER WORKBOOKS BEFORE RUNNING THIS CODE.

    Enter the following code in the ThisWorkbook module (follow these steps)

    1. Press ALT F11 to open the Visual Basic Editor (VBE)

    2. In the Project Explorer on the left of your screen (CTRL R if it is not visible), double click on ThisWorkbook.

    3. Enter the code below in the code window on the right of your screen.

    Private Sub Workbook_Open()
    'when the workbook opens run the SparkleXmasTree procedure
    Call SparkleXmasTree
    With Application
        'hide ribbon, formula bar, status bar and set window dimensions
        .ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
        .DisplayFormulaBar = False
        .DisplayStatusBar = Not Application.DisplayStatusBar
        .WindowState = xlNormal
        .Width = 1100
        .Height = 650
        .Caption = "Merry Christmas From Blue Pecan Computer Training Ltd"
    End With   
    With ActiveWindow
        'hide scrollbars, gridlines and sheet tabs.
        .DisplayHorizontalScrollBar = False
        .DisplayVerticalScrollBar = False
        .DisplayGridlines = False
        .DisplayWorkbookTabs = False 
    End With
    End Sub
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'before the workbook closes make the formula bar visible
        Application.DisplayFormulaBar = True
    End Sub

    Enter the following code in a standard module.  To create a standard module, follow these steps.

    1. Press ALT F11 to open the Visual Basic Editor (VBE)

    2. Make sure the Project Explorer is visible on the left of your screen as below – CTRL R if it is not visible

    3. Click Insert | Module

    4. In the Project Explorer, double-click on the new module you have created and enter the code in the code window on the right of your screen.

    Sub SparkleXmasTree()
    With Worksheets("Sheet1")
        'recalculate worksheet
        .Calculate
        'prevent scrolling
        .ScrollArea = "A1"
        'protect sheet
        .Protect
    End With
    'reset zoom to 60% if user changes zoom
    ActiveWindow.Zoom = 60
    'run this procedure every second
     Application.OnTime Now + TimeValue("00:00:01"), "SparkleXmasTree"
    End Sub

    When you save your Excel workbook make sure it is saved as a macro-enabled workbook.