Forum Discussion
pferdig
Sep 30, 2025Occasional Reader
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 d...
Kidd_Ip
Oct 02, 2025MVP
How about this:
- 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).
- 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.
- 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.
- 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).