Solved

How to build an excel bar chart to reflect percentage completed

Posted on 2010-11-11
22
2,595 Views
Last Modified: 2012-05-10
Hi experts

I'm not very experienced at creating excel charts. I need to create a chart that will display how complete a project is for the month.

I really have no idea where to start with this except the obvious

                      Jan      Feb    March   April ...
% complete                                         70%

See attached image below. Basically it is a rectangle and the red fill represents the amount completed. The amount completed (eg 70%) is displayed at the end of the bar.

Can someone please guide me on how I would go about setting this up?
graph.jpg
0
Comment
Question by:Fi69
  • 8
  • 7
  • 5
  • +1
22 Comments
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34118033
Put your % complete in one cell (say cell A1), then put =100-A1 in cell A2.
Select them and insert a horizontal stacked bar chart.
Tell it the series are in rows.
Right click on the white part and hit 'format chart area' change the background and border to nothing.
Right click on the gray part and hit 'format plot area' change stuff to none again.
Right click on the chart hit 'chart options' turn everything off.
Right click on each colored bar and hit 'format series' and make it the color you want.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34118041
See the sample. progress-bar.xls
0
 

Author Comment

by:Fi69
ID: 34118118
Thanks Tommy

How do I make it so the progress bar is in months for instance. Different users are going to be updating their chart each month.

jan      5% finished
feb      10% finished
mar      15
apr      25
may      25
jun      50
jul      60
aug      75
sep      
oct      
nov      
dec      

I put the data in the xls file you sent (attached). put in a stacked bar, not sure how to put the "series in rows". Maybe if I could get that bit to work I could manipulate the data, but...then again... progress-bar.xls
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34118144
Like this?

For reference, I added the =100-x formulas
I did all the formatting suggested in my first post except I left the chart area white and the Y axis on.
I right clicked on the Y axis and turned off all the lines and tick marks and on the scale tab put the 'categories in reverse order'.
progress-bar2.xls
0
 

Author Comment

by:Fi69
ID: 34118187
Hi Tommy

Sorry to be annoying. What I want to have is all of the months there where the users can enter their percentage complete, but I only want to show one progress line.

Sorry to be annoying. Thanks for adding that formula and for formatting the one you sent back. See how the chart has all the months displayed... so January would appear as is, then february would appear over the top of january as further, then march would show over the top of february, etc, etc, So the bar is all set up for the year and all the user has to do is pop in their data each month and the progress bar will reflect that.

So it will be a running record in the data file as well (whereas I think with your first example, it is just the updated figure for each month (ie you lose the last month's figure when updated).

hope this makes sense.

In that one that you sent me bac
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34118263
I'm not sure exactly what you mean then. I tried a couple more ideas. If those still aren't right, try explaining exactly what you want to see or sketch it in MS Paint or something.
progress-bar3.xls
0
 
LVL 50

Expert Comment

by:teylyn
ID: 34118414
Hello,

it's a bit hard to create a chart without your data.

I've mocked up a scenario that tries to mirror the screenshot.

You need a stacked horizontal bar chart, and use the XY Chart labeler to display the completed percentage.

I'm happy to elaborate on the details if you feel that this is something you want to pursue.

cheers, teylyn
Book1.xlsx
0
 
LVL 50

Expert Comment

by:teylyn
ID: 34118419
sorry, I didn't mean to post this in orange. There's currently a bug in the system, defaulting to administrative replies for badgers. Please read the comment as a "blue" one.

cheers, teylyn
0
 
LVL 50

Expert Comment

by:teylyn
ID: 34118435
Also, please disregard the X axis labels. If required, they can be set to month names, although that will need a bit more work.

I'm happy to guide you through it, though. I've done lots of Gantt charts in that fashion, and am fairly confident that what you want to show can be achieved with a basic Excel chart, if the data is laid out to suit your needs.

cheers, teylyn
0
 
LVL 50

Expert Comment

by:teylyn
ID: 34126030
Thanks, friendly mod!
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:Fi69
ID: 34126054
Hi teylyn, yes, this is the sort of thing I'm after. I didn't realise how complicated this would be!
Is it possible to have it in the format of actual months.

At this stage I don't have any data I've just been asked to create the chart so that the users can fill in their progress (percentage) completed month by month with the flexibility to be started and ended wherever required. So, from what I can see your chart does this, but is it possible to show the horizonat axis by month eg Jan, Feb?

I've attached your file back with a couple of comments and a Months complete for week ending bit where I'd imagine the user would enter the date.
 Progress-Chart.xlsx
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34126073
Teylyn's online and her graphs were closer to what you wanted so I'll bow out of the discussion for now. I leave you in capable hands.
0
 

Author Comment

by:Fi69
ID: 34126097
Thanks Tommy - your chart would be good if we could put an if statement in the chart segment "if zero than chart segment fill = nothing, else fill chart segment fill = red" but can't do that as far as I know.
0
 
LVL 50

Accepted Solution

by:
teylyn earned 500 total points
ID: 34126250
Hello Fi69,

To build a Gantt chart in Excel, you need these elements:

1.a the start date (of the project)
1.b the end date (of the project)
2. the number of days completed
3. the number of days still remaining

Items 2 and 3 can be calculated by using

for 2: either a manual input for the number of days completed, or a formula to calculate the difference between a date (e.g. today, or any other given date) and the start date
for 3: a formula to calculate the number of days still remaining based on the difference between the end date and the days already completed

You then build a stacked horizontal bar chart, with 1.a, 2, and 3 as the chart series. Set the X axis minimum to the desired date and format the 1st series (start date) with no fill and no lines to make it invisible.

These are the general principles.

In your attached file you suggest you want to use a date to calculate the days completed. This will mean that all project bars for "number of days completed" will end at the same date, effectively only showing days elapsed since project start. Is that what you want to show?

Alternatively, you can also manually enter the days _actually_ completed, which may differ from the current date, but might give a more accurate picture of the project's progress.

In the attached, I've followed your suggestion and used the "week ending" date to calculate the progress, hence, all project "days completed" are aligned to the same date.

To build a month grid overlaying the chart, you create a separate table in your sheet, listing the 1st day of the months you want to chart. Add this as a data series to the chart, then send it to the secondary axis and change it to a XY chart. Add error bars to create vertical lines that determine the month ends and format the data markers to have no fill and no line. Use the XY Chart labeller tool, (download here: http://www.appspro.com/Utilities/ChartLabeler.htm) to create labels for that data series to the right of the data points.

Finally, calculate the percentage completed and use the XY chart labeller to insert that number as a data label in the "completed" series.

see attached.

There is a bit of work involved setting this up, but once you have it in place, it's really not all that hard to maintain.

cheers, teylyn





Copy-of-Progress-Chart.xlsx
0
 

Author Comment

by:Fi69
ID: 34126284
Thanks Teylyn:

Thanks for pointing out the manually versus current date accuracy issue - I hadn't thought of that - but you are very much correct.

I shall now have a look, a read and a 'fiddle' with chart and try and digest and make sense of it all!
0
 
LVL 50

Expert Comment

by:teylyn
ID: 34126884
You can, of course, add data to compare actual vs. plan.

See attached for a rather crude version. Can be fine-tuned. Enter actual progress in the yellow cells of the data table

cheers, teylyn
Copy-of-Progress-Chart2.xlsx
0
 

Author Closing Comment

by:Fi69
ID: 34153909
Brilliant explanation
0
 

Author Comment

by:Fi69
ID: 34153918
Hi Teylyn

Thank you so much for your wonderful instructions. Through your instructions I was able to recreate the Gantt Chart, so I'm very happy.

Thanks again!

0
 
LVL 50

Expert Comment

by:teylyn
ID: 34153958
Glad to help. Thanks for the grade!
0
 

Expert Comment

by:Cjtrou
ID: 37298404
The comment on 11/11/10  regarding the chart.  The chart was helpful but is there a way I can get step by step details so I can duplicate for my purposes?
0
 
LVL 50

Expert Comment

by:teylyn
ID: 37300526
Hello, you should start a new question. Link to this one and please refer to the post number. With my regional settings, I see no comment that was posted on 11/11.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now