Hessel de Walle's Blog

On the internet you can find many examples of Excel Gantt charts, either created as a graph or using conditional formatting and how to build them. My example has some additions to the graph type of the Gantt chart: A date line (the black vertical line). Different colors for the task or part of a task before and after a specific date (in this case July 31). Here are the data, from A1:C27 : Task Start Date Duration in days Task 1 3-6-2013 1 Task 2 4-6-2013 1 Task 3 4-6-2013 5 Task 4 11-6-2013 1 Task 5 11-6-2013 12 Task 6 25-6-2013 3 Task 7 28-6-2013 1 Task 8 28-6-2013 4 Task 9 4-7-2013 3 Task 10 9-7-2013 1 Task 11 10-7-2013 3 Task 12 15-7-2013 3 Task 13 18-7-2013 1 Task 14 21-7-2013 5 Task 15 24-7-2013 7 Task 16 27-7-2013 4 Task 17 30-7-2013 3 Ta...

Just an idea. A simple idea. Here are the data from A1:C10: 0-10% 125 500 10-20% -24 -500 20-30% 245 500 30-40% 432 500 40-50% -123 -500 50-60% 235 500 60-70% 53 500 70-80% 67 500 80-90% -87 -500 90-100% 98 500 C1:C10 contains a formula: =IF(B1>0;500;-500) All three series are in the column graph. Series 3 is connected to the secondary axis. Series 3 Overlapped 100% and No Gap 0% and Transparancy 50%. Both Y-axis between -500 and 500. Both series: Invert if negative . Right Y-axis removed. Data labels for the second series switched on. You can also download the file excelbackground.zip through this link: https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

How do you display for each company which one had a total positive profit (revenue minus costs) and which one a negative. My idea: Here are the data from A1:D11: Revenue Costs Profit Company 1  €      500  € 400  €  100 Company 2  €   1.000  € 760  €  240 Company 3  €      890  € 900  €   -10 Company 4  €      640  € 700  €   -60 Company 5  €      570  € 600  €   -30 Company 6  €      875  € 800  €    75 Company 7  €      690  € 600  €    90 Company 8  €      500  € 600 ...

Inspired by my newspaper this morning, here is my idea. And here are the data from A1:D14 : Apples Incomplete Percentage 2001 100 2002 200 2003 200 50% 2004 400 2005 500 2006 600 2007 400 60% 2008 800 2009 900 2010 1000 2011 1100 2012 1200 2013 650 70% What did I do next? Create a clustered column graph based on A1:C14 . Link the second column to the secondary axis. Set the gap width to 300 % twice the percentage of the other one. Format column D2:D14 with a custom number format like this:  0"%" Change the horizontal (Category) axis labels of the second column to D2:D14 . Switch the maximum of the secondary Y-axis to a fixed 1400. Delete the secondary Y-axis. Add data labels to the second column. Switch the data labels to category na...

It was pretty hard to get this one done. Could not do it without a trick I am not satisfied with. You can also download the file graphpropercolors.zip   through this link: https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

No time for explanation at the moment. So just the picture and the file for now: File: www.walmar.nl/zips/grafiekmetgekleurdexas.zip

I have a temperature table  with all the average month temperatures of De Bildt (Netherlands) since the year 1706. So, is it possible to indicate what the highest average month temperature for july was until a specific year and so on? We can use Conditional Formatting to create a rule to mark this moving maximum. In the example I have, I am doing this for the columns B til M starting in cell B2 by using the formula: =B2>=SUBTOTAL(4,B$2,B2) In the Dutch version this would be: =B2>=SUBTOTAAL(4;B$2;B2) In the function SUBTOTAL number 4 indicates we want the maximum value. When we link a background color to this rule, we could get this: For each column we see the month with the highest average temperature until that year marked with the color orange or so. You can also download the file voorbeeld.zip through this link: https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing