Search code examples
excelexcel-2007

Excel: Horizontal Progress bar with 2 x-axes?

I'm trying to make a horizontal progress bar that has two x-axes on either side of the bar, and which shows the current data normalised against the axes. Something that looks like this:

enter image description here

The filled blue area indicates the accumulated earnings till the present week. So for example, if current week is 4, then the blue area in the picture above indicates that earnings are less than 15k, which means we have fallen short of our target of 15k.

Anyone knows how to make this? I looked at some solutions online, but they don't show how to make this exact bar chart that I want. I am using Excel 2007.

EDIT: This is how I have arranged my data as of now. But I am open to changing it, if a newer layout achieves the desired outcome (i.e. the bar above)

enter image description here

Solution

  • Alright, here is my attempt. I started by adding a cumulative earnings column and cumulative target column so that the data looks like this:

    +------+----------+--------+---------------+-------------+
| Week | Earnings | Target | Cum. Earnings | Cum. Target |
+------+----------+--------+---------------+-------------+
| 2    | 4.1      | 5      | 4.1           | 5           |
+------+----------+--------+---------------+-------------+
| 3    | 2.3      | 5      | 6.4           | 10          |
+------+----------+--------+---------------+-------------+
| 4    | 4.6      | 5      | 11            | 15          |
+------+----------+--------+---------------+-------------+
| 5    | 3.9      | 5      | 14.9          | 20          |
+------+----------+--------+---------------+-------------+
| 6    |          |        |               |             |
+------+----------+--------+---------------+-------------+
| 7    |          |        |               |             |
+------+----------+--------+---------------+-------------+

    For me, this table is in range B1:F7. I then added to cells H1:J2 the following values

    +---------+-----------------------------+----------------------------+
| Current | Current Cumulative Earnings | Current Cumulative Target  |
|   Week  |                             |                            |
+---------+-----------------------------+----------------------------+
| 4       | =VLOOKUP(H2,B2:F7,4,FALSE)  | =VLOOKUP(H2,B2:F7,5,FALSE) |
+---------+-----------------------------+----------------------------+

    What this does is allow you to enter the current week in cell H2 and the VLOOKUP formulas will populate the two values for current week cumulative earnings and current week cumulative target.

    So at this point, my sheet looks like this:

    enter image description here

    I then added a blank bar chart to the page. Then follow these steps:

    1. Right click the blank chart --> Select Data...
    2. Add. Name the series whatever you like (or reference a cell) and for series values, select cell I2 click OK
    3. Add another series. Name the series whatever you like / reference a cell and for this series' values select cell B7 (this should be 7, or the maximum week you are using). Click OK

    4. At this point, your bar chart should look something like this:

      enter image description here

    5. Right-click the data series that references Week 7 (or your maximum week, the orange bar in my example) --> Format Data Series...

    6. Under Series Options set the Series Overlap to 100%. Change Plot Series On to Secondary Axis.

    7. Now right click the upper x-axis --> Format Axis. Change the Minimum to 1 and Maximum to 7 (or whatever is your maximum week). Make sure your major units are set to 1.

    8. With this series still selected, under Fill select No fill. Under border select Solid line. I am going to make mine blue. Select the other series and change the fill to a solid color to match whatever you set for the border of the first series (you can use key up and key down to cycle through chart elements if it is too difficult to click). Should look like this now:

      enter image description here

    9. Select the axis on the bottom --> Format Axis.. and change the minimum to 0 and maximum to 30 and your major units to 5. Also under Axis Options --> Number you can place this #,##0"k" in the Format Code box which will add a k after your numbers to display 0k, 5k, 10k, etc.

    10. Insert the axis titles (Design --> Add Chart Element --> Axis Titles. Add titles for both the primary and secondary horizontal axes. I named the top axis week and bottom axis earnings. I also deleted the label on the y-axis (since there is only one bar). We should look like roughly what you asked for now:

      enter image description here

    You can change the fonts, colors, etc. and play with some formatting. If you change the current week in cell H2 the chart should update automatically for you. There are improvements we could make, and perhaps make it even more dynamic, but for now this should get you off to a solid start.