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.
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.
- Now enter the number of days require to capture your project in this In-Cell Gantt Chart.
- Merge the cells below the day numbers for each stage.
- 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
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.
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
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.
- Copy & Apply Formatting from One Chart to Another Using Paste Special
- How To Add Secondary Axis In Excel Charts
- How To Create Gauge Chart