Loading Bar Chart (incell chart)
In this tutorial, we are going to make an incell loading bar chart. It will fill dynamically as we input data and change it’s color as it passes certain points. I am going to show you 4 alternative ways to do it. You can use whichever suits you best.
We are going to track our gas expense over a period by inputting very purchase we make. As we input our purchases, total cell will increase and our horizontal bar will grow to right. This is why I decided to call this chart a loading bar chart. As total value increases, our bar will start growing with green color, then at 300$ it will become yellow and finally at 400$ it will become red. Here is the table we are going to use for this tutorial:
Using REPT() Function:
We need 3 cells for 3 color for this alternative. One for green, one for yellow and one for red. I am not going to get into specifics about how to use REPT() function here. You may find them in incell chart post.
In green cell input this formula and set font color to green (cell D21 has total valueİ):
Likewise in yellow cell input following formula and set font color to yellow:
And in red cell input following formula and set font color to red:
When done, increase font size if you want bar to fill the cell (I used 18pt) and you are done.
Since there are small empty spaces between colors in first chart, you may want to try another way. This time we are going to fill our 3 cell with green, yellow and red colors to ensure there is no gap in between. Than find a bar shape that will travel through these colorod cells as total increases.
In order to achieve this, we will use below formula. Idea here is to have an empty space repeating infront of a bar figure. So as empty spaces increases, bar will move to right accordingly.
Input below formula to green cell and you are done.
Using Conditional Formatting:
This time we are going to use conditional formatting to make our bar chart. This one is the cleanest looking one of all 4 alternatives. For this to work, we need 100 cells to achieve this dynamically fill effect.
So select 100 cells on the row 4 and set column width for this 100 cells to 3 pixels. Input numbers from 10 to 1000 into cells below these (row 5). Now we are going to set conditional formatting for these cells based on formula. Assuming our total value is in cell C21, we are going to use following formulas in conditional formatting rule:
2nd to 30th cells:
Basically, check total with the cell on row 5. If total is bigger, color that cell. Set color to green for cells 2 to 30, yellow for cells 31 to 40 and red for cells 41 to 100.
Now as you input your gas purchases, your bar wil fill nicely with color.
For this alternative, we are going to do something very similar to altenative 3. But instead of using custom conditional formatting, we are going to let excel handle that part. Here is how it is done:
We are going to set 100 cells with 3 pixels width again. We are also going to set cells with values from 10 to 1000 in accordance with our chart cells (this time I put them above). We are going to input following formula into our first chart cell and copy it to remaining 99 chart cells (total is in cell D21):
This means “if total is bigger than the value in the cell above, set value equal to the above cells value, else set blank (“”)”. So our chart cells will get values from 10 to 1000 as our total value increases.
Now select all 100 chart cells and click on conditional formatting. And select color scales ( red on top, yellow in the middle and green at the bottom). Then while all 100 cells are selected, click on conditional formatting/manage rules. From here edit your rule as shown below.
This means your cells will be green until 300, than yellow until 400 and than red up until 1000. Colors also change smoothly as value increases. So you won’t get clean green to yellow change. Instead green will turn to yellow cell by cell.
I have prepared an example workbook that contains all alternatives. Feel free to download and check them yourself.