Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

Worth $20, Absolutely Free

How To Create An In-Cell Gantt Chart With REPT Function

Creating In-Cell Charts with REPT Function is a smart move. You can use these charts in your dashboards to give a quick view to your user. I have used this text repeating formula to create an In-Cell Column Chart & a Star Rating Template. But, here I have created an In-Cell Gantt Chart for the sake of my laziness. Let me show you this lite weight In Cell Gantt Chart.

In Cell CHart With REPT Functions In Cell Gantt Chart

I have used formulas in conditional formatting to give a different color to Actual Days bar & Planned Days Bar.

Steps To Create An In-Cell Gantt Chart Using Rept Functions

  • Just captured your data in a table with Planned Days & Target Days.

Data To Create An In Cell Chart With REPT Functions In-Cell Gantt Chart

  • Now enter the number of days require to capture your project in this In-Cell Gantt Chart.

Captured Days To Create An In Cell Chart In-Cell Gantt Chart

  • Merge the cells below the day numbers for each stage.

Merge Cells To Create An In Cell Chart In-Cell Gantt Chart

  • Change font style to “Haettenschweiler – Font Size 14”. You can also other font styles like Playbill, Stencil or Britannica Bold.
  • Change text alignment to left & middle.
  • Now enter this formula REPT(“|”,C5*10.6) in the cell corresponding to the Stage A
  • Enter REPT(” “,SUM(C$5:C5)*11)&REPT(“|”,C6*10.6) in Stage B cell & drag it to the rest of the cells.

How Formulas Work in this In-Cell Gantt Chart

REPT(“|”,C5*10.6)

This formula in first stage cell will give you a simple bar against the days planned. I have multiplied the days planned with 10.6 to properly adjust the bar in days cell.

REPT(” “,SUM(C$5:C5)*11)&REPT(“|”,C6*10.6)

This formula will enter the blank space for the days taken by the previous stage & create a bar for the day’s taken by the current stage. Here also, I have multiplied the days with 11 & 10.6 for adjustment. You can change these number as per your cell width.

Things To Remember For Creating In Cell Gantt Chart

  • You have to consider cell width before creating this In Cell Gantt Chart.
  • I recommend you to use Haettenschweiler font style.

Download Sample File

Download Sample File

Conclusion

Using REPT Function to Create an In Cell Gantt Chart in is a time saver compare to other ways for Gantt Chart. It has some limitation but you can use it if you don’t have some high demands. I really want you to give suggestions for improvement in this.

Related Tips