Forum Discussion
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?
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).
2 Replies
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).
That worked. Thank you
