Excel – Create progress bar or milestone type graphs in Excel 2007

chartsmicrosoft excel

I want to display project progress in Excel 2007. Is there a way to show a progress bar like graph filled for completed percentage and empty until end date for a milestone or the whole project? It seems I cannot find this functionality.

I used conditional formatting to get the following, but am not satisfied:

conditional formatting version

The number stands for percentage.

I made another example where the number now stands for the completed percentage of a project. If a milestone is passed it should be colored. Milestones should be passed on a certain date or period.

milestone version

Best Answer

You could get really hacky with this and use sparkline columns.

Here's how you'd structure your sheet:

Column E is where you enter the completion % for each milestone. Column C simply has references to column E with conditional formatting applied to them to show just the icons.

In cell F2, enter =IF($E2>=F$1,1,0), then drag down and right to cell Y10 (alternatively, select from F2 to Y10 and hit Ctrl+D and Ctrl+R).

This creates a table of ones and zeroes for completion at those steps. You can get as granular as you want with this table, but I figured steps of 5 would suffice.

With F2:Y10 selected, go to Insert > Column (in the Sparklines group in the ribbon), or type Alt N S O. Pick the empty slot in column D (D2:D9) as your location range. Resize column D to make it look like a progress bar:

Next I'd either hide or group columns F:Y or change their number format to ;;; so as to hide their value. If you choose to hide or group the columns, you will need to change the sparkline settings for Hidden and Empty Cells (click on the sparklines, then Alt J D D H). You will also probably want to set the minimum and maximum axis values (Alt J D A then C or V)

Related Solutions

Excel Conditional Formatting Multiple Data Bars and Data Icons in one cell

Well, after many hours searching the internet I was able to find an Office Excel Blog that had my answer. http://blogs.office.com/b/microsoft-excel/archive/2006/02/24/conditional-formatting-trick-1-multi-coloured-data-bars.aspx

The idea is to provide a formula for each conditional format to tell it which cells to format and which cells not to format. There is no interface for this built-in to excel so we need to turn to VBA to apply the formulas.

selection.FormatConditions(1).formula = "enter formula here"  
    -- Quotes are mandatory and the Conditional Formats start with "1"
       at the top of the list   
    -- If you make a mistake selection.FormatConditions(1).formula = "=TRUE"

Adding formulas to excel conditional formats

I wasn't sure if this format will persist from one session to the next. I decided to open the Excel file and take a look at its XML contents. In the worksheets folder (after you add the .zip extension to the file), click on the spreadsheet "Sheet1" and at the bottom is the information for the conditional formats. The XML on the left is from before I applied the conditional formats formula and the XML on the right is after.

enter image description here

Excel – Displaying data in Excel graph only up to current date

You can achieve this by:

adding a calculated column where you insert whether the date is in the past:
(=TODAY()>A2)
insert a pivot chart and set:
- Legend (series): series and past (column D)
- axis (categories): date
- values: value
- What you can't do is to make the line continuous across time, Excel consider them as different series

Best Answer

Related Solutions

Excel Conditional Formatting Multiple Data Bars and Data Icons in one cell

Excel – Displaying data in Excel graph only up to current date

Related Question