Forum Discussion

pferdig's avatar
pferdig
Occasional Reader
Sep 30, 2025

Custom Sparkline Formula

I am in the process of moving to Excel from Google sheets.  I am trying to build a project sheet and add a formula that will provide a "sparkline" for quick reference of the projected start and end dates, and then based on the status of the task (not started, in progress or complete) and today's date in relation to the start and end dates of the task, change color (Green, yellow, red, etc).   

Here is the formula I use in Google Sheets, but I cannot get it to work in Excel.  

 

=SPARKLINE(if(OR(today()<D5,today()>E5),{int(D5)-int($D$2),if(D5=E5,1,int(E5)-int(D5))},{int(D5)-int($D$2),today()-int(D5),1,int(E5)-today()}),{"charttype","bar";"color1","white";"color2",if(G5="Complete","grey",if(today()>E5,"red",if(AND(today()>D5,today()<E5,G5="Not Started Yet"),"darkred",if(AND(today()>D5,today()<E5),"orange",if(G5="In Progress","orange",)))));"max",int($E$2)-int($D$2)})

 

In this formula, Column D represents the start date and Column E represents the specific task end date.  D2 and E2 represent the Project start and end date.

 

Any Advice?

Charting
Excel on Mac
Formulas and Functions

1 Reply

  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Oct 02, 2025

    How about this:

     

    1. Insert Basic Sparkline

    Use the built-in Sparkline feature:

    • Select the cell where you want the sparkline.
    • Go to Insert > Sparklines > Bar.
    • Choose the data range (e.g., a helper row with calculated values).
    • Choose the location range (where the sparkline will appear).
    1. Create Helper Columns

    Since Excel sparklines can't take arrays directly like Google Sheets, you’ll need to create helper columns to simulate the bar segments:

    • Start Offset: =INT(D5)-INT($D$2)
    • Duration: =IF(D5=E5,1,INT(E5)-INT(D5))
    • Progress: =IF(TODAY()<D5,0,IF(TODAY()>E5,DURATION,TODAY()-D5))

    You can then use these values to build a row of data that the sparkline references.

    1.  Color Coding

    Excel sparklines don’t support conditional colors directly. But you can simulate it using Conditional Formatting on the sparkline cell itself:

    • Select the sparkline cell.
    • Go to Home > Conditional Formatting > New Rule.
    • Use a formula like:
    =G5="Complete"

    and set the fill color to grey.

    • Add additional rules for "In Progress", "Not Started Yet", and overdue tasks.
    1. Max Value

    Set the max value manually in the Sparkline Tools:

    • Click the sparkline cell.
    • Go to Sparkline > Axis > Vertical Axis Minimum/Maximum Value Options.
    • Set the max to =INT($E$2)-INT($D$2).

Resources