Use the “SPARKLINE” formula to create an in-cell Gantt Chart in Google Sheets
Credit where credit is due: I didn’t create this formula. It comes via my equally-nerdy colleague Dan, who sent me this little trick under the subject line “MIC DROP”.
If you’ve done any project management work, you’ve certainly seen a Gantt Chart - horizontal bars representing different activities within a broader work plan. Historically, I’ve created these in one of two ways: either I create a whole bunch of columns, representing weeks, and then use conditional formatting to mark cells black if the activity falls within that week; or, I create a horizontal stacked bar chart using the native “Charts” feature within Google Sheets. Both of those solutions are OK.
This is WAY better:
SPARKLINE ( { INT( startDate ) - INT( overallStartDate ) , INT ( endDate ) - INT( startDate ) } , { "charttype" , "bar" ; "color1" , "white" ; "color2" , "black" ; "max" , INT ( overallEndDate ) - INT ( overallStartDate ) } )
Place this formula next to the descriptions of the task and a perfectly sized bar will appear WITHIN the cell. It's not quite magic… but close to it.
What this formula actually does is it creates a standalone chart within the cell [via the “SPARKLINE” formula] and designates the chart type as a stacked horizontal bar chart ["charttype" , "bar"]. We color the first bar white ["color1" , "white"] to hide a certain amount of the visible bar. That “certain amount” is the time between the start of the overall chart and the start of that particular activity [INT( start_date ) - INT( overall_start_date]. We then color the second bar black** ["color2" , "black"] and set its value as the time between the start of the overall chart and end of that particular activity [INT ( end_date ) - INT( start_date )], but because that second bar is sitting behind the first bar, which is white, we only see the value amount which is the start of that particular activity to the end of that activity. Lastly we set the max value for the horizontal axis as the end of the overall timeline ["max" , INT ( overall_end_date ) - INT ( overall_start_date )].
** Note: in setting the color of the chart you can use color names (you can see these if you hover over the options within the color panel) or you can use HEX (e.g., “#6d9eeb”).
In the above “Life Plan” project example, you can see the formula in action in column F. Here’s the visual output:
Microphone: DROPPED.
Feedback or suggestions? Leave comments below. And please “Like” if this was helpful.