Hessel de Walle's Blog

I was struggling with a simple column graph. I was just wondering if there is a formula in Excel which can indicate whether a row is hidden or not. Without using VBA of course. Here are my data: Year Apples 2001 10 2002 11 2003 12 2004 13 2005 14 2006 15 2007 16 2008 17 2009 18 2010 19 2011 20 2012 21 2013 22 2014 23 2015 24 What I wanted to achieve is a graph like this which does not show the rows I hid, in this example the years 2005-2008, but where I still can see the X-axis values. In this chart I checked the box Show data in hidden rows and columns Otherwise I would not get the years 2005-2008 on the X-axis. So I needed a function to check wether a row is hidden or not in order to manipulate the data in column B:B. Here is my solution. I turned my data list into a table. Then I added a third column with this form...

Via my newspaper we discovered these chart on  http://www.people-press.org/2014/06/12/political-polarization-in-the-american-public/ We just wondered how to do this in Excel. Here is what we created: We did not have the right data, so we just estimated the figures: 1994 2014 Democrats Republicans Democrats Republicans Liberal11 0,4 0,0 4,1 0,0 Liberal10 1,4 0,1 5,3 0,4 Liberal09 1,6 0,2 5,8 0,6 Liberal08 2,8 1,3 6,8 0,8 Liberal07 3,2 1,9 7,0 1,2 Liberal06 5,6 2,3 7,2 1,6 Liberal05 6,2 3,0 7,4 2,4 Liberal04 7,6 4,2 7,8 9 4,0 Liberal03 8,0 4,9 7,6 4,4 Liberal02 8,4 6,1 7,2 4,8 Liberal01 8,2 9 6,4 6,0 5,...

Nice idea of the designer of my newspaper Trouw :  to present the category  Neutral   apart from the others. Also nice to make a chart like this in Excel : On the content I have no comments. You can download  Excel20141211English.xlsx  via: https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

Leuk idee van de vormgever van de Trouw om de categorie Neutraal los te koppelen van de andere. Ook altijd leuk om zoiets na te maken in Excel : Over de inhoud heb ik verder geen mening. Bijbehorende bestand  Excel20141211.xlsx  is te downloaden via: https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing Verdere tips: je kunt het bestand  exceltips.pdf  (laatst gewijzigd (12-9-2014) hier downloaden : https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

Ebola is not something to make fun about, but my mind was triggered by a chart I saw in a newspaper. I just wondered how to present the data in Excel. This is what I created using Excel 2013: The data I found in Wikipedia. I had to rearrange some: Ebola Virus: Cases and Victims Number Year Virus Country Human Deaths Cases Rate 9 1976 SUDV Sudan 151 284 53% 3 1976 EBOV Zaire 280 318 88% 3 1977 EBOV Zaire 1 1 100% 9 1979 SUDV Sudan 22 34 65% 11 1988 EBOV United Kingdom 0 1 0% 2 1994 TAFV Côte d'Ivoire 0 1 0% 5 1995 EBOV Gabon 32 52 62% 3 1995 EBOV Zaire 245 317 77% 1 1996 EBOV Congo 46 62 74% 5 1996 EBOV Gabon 21 31 68% 7 1996 EBOV Russia 1 1 100% 10 2000...

I could not make it as nice as the original but it looks OK to me. Circumplex - model of relations among different value groups (following Schwartz 1992) And the original from  http://www.rwlnetwork.org/news/balance-%E2%80%93-an-example-for-using-frames-in-real-world-learning.aspx Here are the data: Inner Outer Benevolence 1 Intrinsic Values 3 Tradition 1 Conservation 2 Security 1 Extrinsic Values 2 Power 1 Openness to Change 2 Achievement 1 Hedonism 1 Simulation 1 Self-Direction 1 Universalism 1 You can download the file  ExcelCircumplex.xlsx  through https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

Downloaden a milestones template from the Microsoft Office website: http://office.microsoft.com/en-001/templates/timeline-with-milestones-TC102930035.aspx To my opinion it needed some improvement. This is what I created: Compared to their version: I used the same data as they did for their example, with the last column changed and one added: DATE MILESTONE POSITION BASELINE BELOW BASELINE ABOVE 23-jan Project Start 25 0 #N/B 14-feb Milestone 1 10 0 #N/B 24-feb Milestone 2 -10 #N/B 0 1-mrt Milestone 3 15 0 #N/B 15-mrt Milestone 4 -15 #N/B 0 15-mei Milestone 5 15 0 #N/B 15-jun Milestone 6 -15 #N/B 0 30-jun Milestone 7 15 0 #N/B 15-jul Milestone 8 -20 #N/B 0 30-jul Milestone 9 20 0 #N/B 23-okt Milestone 10 -15 #N/B 0 31-dec Project End 15 ...

Bubble or pie chart or both? With the help of Excel VBA everything is possible! Here are the data: Bubble X Y Size A 1 3 3 B 3 3 3 C 3 1 4 D 1 1 2 Pie 1 Pie 2 Pie 3 Pie 4 8 5 3 8 5 1 7 2 6 2 4 3 4 4 3 3 5 5 3 2 You can download the file  ExcelGrafiekBel.xlsm  through https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

This chart shows thumbs up when the values are above average and thumbs down when the values are below: Here are the data: Month Values Qualification Average Above Average Below Average jan 130 C 79 130 0 feb 80 C 79 80 0 mar 56 D 79 0 56 apr 91 C 79 91 0 may 98 C 79 98 0 jun 59 D 79 0 59 jul 70 D 79 0 70 aug 86 C 79 86 0 sep 90 C 79 90 0 oct 38 D 79 0 38 nov 81 C 79 81 0 dec 69 D 79 0 69 You can download the file  ExcelThumbsUpDown.xlsx  through https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

Pretty easy to create: Crystal Reports line chart with banded background, indicating good, average and bad: For this example I used the noordenwind database, the Dutch equivalent of the Northwind database. For the bands I used three formula fields: Good: 50000 Bad: 30000 Average: 40000 I created a stacked bar chart based on the three formula fields. The line chart presents the total sales volume per customer, limited to the country Germany. You can download the file  Graphbands.rpt  through https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

Can you base a target line in a chart on just one value? Yes, you can! And will it expand dynamically? Yes, it will! Here are the data and no, there is nothing hidden: Month Value Target jan 60 60 feb 65 mrt 70 apr 75 mei 80 jun 85 jul 90 aug 95 sep 100 okt 105 nov 110 dec 115 You can download the file  ExcelGrafiekHerhaling.xlsx  through https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

At a course I got the question about a line in a chart. Isn't it possible to get a line from one side of the other? He was talking about the lower line which is an ordinary line chart. So I used some tricks to change it. Now the upper line is like he asked. You can download the file  ExcelGrafiekdoorgetrokkenlijn.xlsx  through https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

Gebaseerd op een voorbeeld uit Graphic Presentation van Willard C. Brinton uit 1939, pagina 245. Hoe geef je veranderende rangorde in de tijd weer? In onderstaande voorbeeld heb ik dat toegepast op de verandering in aantal inwoners van de Nederlandse provincies sinds 1850. Het voorbeeld is te downloaden als ExcelProvincies.xlsx via https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

Based om an example in a Dutch newspaper with two charts I created this one: Here are the data from A1:D11: Length Per Km2 Rank Investment: Euro Per Citizen The Netherlands 78 Rank 6  €                                       137 Belgium 58 Rank 7  €                                       130 Germany 37 Rank 5  €                                    ...

Inspired by a nice graph from the Volkskrant of last Saturday: The data from A1:E15: In procenten Helemaal natuur Beetje natuur Geen natuur Tropisch regenwoud 95 4 1 De Veluwe 93 7 0 De Waddenzee 91 8 1 Damherten 83 15 2 Schone lucht 78 18 4 Merels 75 23 2 Schapen in de wei 68 30 2 Graanvelden 52 41 7 Een boomrijk stadspark 40 56 4 Medicinale planten 32 49 19 Een klimop in de achtertuin 24 59 17 Muntthee 11 38 51 Bedrijventerrein met bomen 5 41 54 Een hondenuitlaatplaats 3 18 79 You can download the file  excelnatuur.xlsx  through this link: https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

A simple but nice idea. I just could not work out the idea to give the line above different colors when above or below the target line. Myabe somebody has a better idea? Here are the data from A1:E14: Year Real Target Above Below 2001 130 100 130 #N/A 2002 120 100 120 #N/A 2003 80 100 #N/A 80 2004 110 100 110 #N/A 2005 90 100 #N/A 90 2006 120 100 120 #N/A 2007 76 100 #N/A 76 2008 111 100 111 #N/A 2009 112 100 112 #N/A 2010 97 100 #N/A 97 2011 95 100 #N/A 95 2012 104 100 104 #N/A 2013 106 100 106 #N/A You can download the file  lijngrafiek.xlsx  through this link: https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

When you combine a bar and a column graph you can create this nice example: Here are the data from A1:C10: Year Amount Bands 2001  € 120,00 0 2002  € 134,00 8 2003  € 100,00 0 2004  € 123,00 8 2005  € 154,00 0 2006  € 111,00 8 2007  €   98,00 0 2008  € 145,00 8 0 You can download the file  ExcelBanded.xlsx  through this link: https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

Saw a graph with two Y-axis in the Dutch magazine Elsevier. This is what I made of it: The orginal looked like this: Here are the data from A1:C8 Dec. 2013 Compared to 2012 Germany 1,74 % 0,36 % Netherlands 2,06 % 0,45 % France 2,17 % 0,13 % Italy 4,08 % -0,42 % Spain 4,15 % -1,16 % Portugal 5,87 % -1,70 % Greece 8,54 % -7,39 % You can download the file  ExcelTwoYAxis.zip  through this link: https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

In Excel it looked impossible two create a pie chart with two different pies in one chart. And of course I did not want to paste two pies together. By using the type Pie of Pie I managed to do it. Could not yet find an example of this on the internet. Here are the data from A1:D7: Fruit Vegetables Apples 1 Lettuce 7 Pear 2 Cabbage 8 Bananas 3 Spinach 9 Grapes 4 Endive 10 Cherries 5 Carrots 11 Strawberries 6 Sprouts 12 You can download the file  ExcelTwoCircles.zip  through this link: https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

Very nice graph in a Dutch newspaper (de Trouw) which I remade in Excel. Here are the data from A1:D5 : Liquor Price (%) Liquor Sales (%) tussen 2012 2,4 -4,0 -1,5 2013/1 3,8 -10,4 -1,5 2013/2 3,7 -9,6 -1,5 2013/3 3,8 -8,1 -1,5 Create a Stacked Bar Diagram  based on the range A1:D5 . Set the color of the fourth part to No fill . Add a line graph based on column two. Add labels for this line. Choose Above for the labels. Set the color of the line graph to No color . Create a name label2 : =Blad1!$C$2:$C$5+Blad1!$D$2:$D$5 Add this name to the graph as a line graph. Add labels for this line. Choose Below  for the labels. Set the color of the line graph to No color. Add a another line graph based on column four. Add this line to the Secondary Axis . Change the secondary Y-axis in a way that the horizontal axis crosses at -1,5. Both axes should run from 6 to -14. Remov...