Hessel de Walle's Blog

Power BI In  Power BI  you can put more charts on one page. Next you can choose to make these charts interactive, given a connection between the sources. In this example we can see two charts, created in Power BI . The upper one shows amounts per year; the lower obe amounts per year per month. In this case a click on one of the years in the lower chart hightlights the corresponding data in the upper chart. Excel Could something the like be done in Excel . Visual interacting by clicking part of a chart does not work in Excel (without using VBA). But it is possible to create a similar visual interaction by clicking a year ( G2:G5 )  in the examples below. When we pick another year within the range G2:G5  we get: Etc.. To do this I used the function  CELL("ADDRESS") . To make Excel recalculate whenever the selection changes, we need a little VBA . When we use a cell with data validation based on the years in G2:G5 we need no VBA at...

The question is how can somebody just pick the graph he/she wants to see without seeing the other graphs? Nice to have for your Excel dashboard. Something like this: How did I do this? I created one sheet called graphs with three different graphs. Then I created three names One , Two and Three referring to a range containing the graphs One , Two and Three respectively. Like this: one = graphs!$G$1:$O$16 two = graphs!$G$17:$O$33 three = graphs!$G$33:$O$48 On a second sheet called whichgraphtoshow  in cell A2 I then created a picking list containing one , two and three . Yet another name refers to this cell: graphLookup =INDIRECT(whichgraphtoshow!$A$2) On the same sheet I then inserted a picture. The picture refers to the name graphLookup .  And it is all working! You can download  ExcelWhichGraphToShow.xlsx  via: https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

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...

What to show? Thirty six charts or just one dynamic one? I created I chart which shows the choice of two parameters: cumulatives AVERAGE, MIN or MAX and months JAN through DEC. So, in this case we have thirty six sets of data we can choose from. Take your pick! I needed just one complex name: MONTH: =OFFSET(temp1!$B$2;0;MATCH(temp!$Q$2;monthnames;0)-1;temp!XER1-1705;1) The rest you can easily figure out yourself. You can download  ExcelMoving.xlsx  via: https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

I made a waterfall chart after the model of  +Jon Peltier . After adding another two lines and linking them to the secondary axis I was able to create a double waterfall chart. Here are my data: Values End Before After Values End Before After Initial € 500,00 € 500,00 Initial            100            100 First € 100,00 € 500,00 € 600,00 First               20             100             120 Second € 200,00 € 600,00 € 400,00 Second             -10          ...

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,...

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

I used Data Validation to limit the input of cell F1 . Now I want the choice I make in cell F1 to determine which range of data is shown in my chart. First I created a name based on the range B1:D1 and called it List . Then I created three names Apples , P ears and Bananas based on the columns underneath these lables. So Apples refers to B2:B13 and so on. Then I created another name Chart based on the formula: =INDIRECT($F$1) letting it refer to the outcome of the choice I make in cell F1 , and of course indirectly to the data to which the picked name refers. Next I created a column chart based on B1:B13 . In this column chart I made a couple of changes. First I made the Series name refer to F1 . Secondly I made the Series values refer to the name Chart . And finally the Axis label range should of course refer to A2:A13 . Now you can simply choose the column you want show in your chart by takinig you pick in cell F1 ! There is one flaw. A soon as you are try...

On the Internet, I found several examples of Excel thermometer charts. In all the examples I could find, people use SmartArt i.e. shapes to create the ball of the thermometer. The more thermometers you then create, the more laborious. Moreover, the SmartArt ball is independent of the graph. Once you move the graph or width changes, the ball is not coming with it. In my example, I created ​​the ball using a line chart with markers . The marker you then change into a ball. The size of this ball you can increase. And it's done. You can then smoothly increase the number of thermometers. You can download the example file ExcelThermometer.xlsx via 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...

Got a question from somebody. Charts showing products and revenues in a report grouped by category, can the different charts be shown two by two instead of one below the other? I tried to find a solution on the internet but could not find an answer to this question. I finally succeeded to create this: The example is based on the Northwind database, using the tables, Categories, Products, Orders and Oder_Details. To get the charts side by side instead of one below the other, I used one sub report. Main report and sub report are based on the same data and contain the same charts. I passed a shared variable from the main to the subreport: shared numbervar group; if groupnumber mod 2 = 1 then     group:=groupnumber ELSE     group:=0; group; In the main report I used a formula to suppress the groups alternately: groupnumber mod 2 = 0 In the sub reports I used the formula  to suppress the groups alternately: groupnumber <> {@g...

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 ...

On the occasion of a request from  +Shane Devenshire  I created this Excel chart: My data: Patients Time Since Treatment Response ● Time to Response → Ongoing response Y 10 17 23 11 ● 9 19 7 13 4 ● 8 38 25 15 4 ● 7 48 0 36 ● 6 52 12 27 4 ● 5 59 10 20 4 ● 4 60 23 37 ● 3 89 5 25 4 ● 2 90 16 69 4 ● 1 98 25 24 ● The arrow is created using a label based op the column Ongoing Response, using the font Wingdings. The black circle is created using a label based on the Y column What the orginal version looked like: You can download the file  ExcelShane.xlsx  through https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

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

Gartner Magic Quadrant made easy: simply use a picture as background in a bubbel chart. The given example is even easier to create in Excel 2013. Here are the data: Aap -2 2,6 5 challengers Noot 2 2,4 10 leaders Mies -2 -1 5 visionairies Wim 2 -1 5 niche players Zus  1 1,3 10 Jet -1 -1 15 ability to execute Teun 1 1 5 completeness of vision Vuur -2 0,9 10 Gijs -1 -2 15 Lam 1 -1,1 5 Kees -1 1,4 10 Bok -2 -2 15 You can download the file  ExcelGrafiekKwadrant2010.xlsx or  ExcelGrafiekKwadrant2013.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