Welcome to the E90E50fx site! This blog-site is a collection of articles about Excel. We share here our ideas, discoveries and some teaching materials about Excel related topics. The posts here are focused on formula-based solution. You can find lot of charts built with formulas. If you are interested in other Excel realated topics, you can check out the E90E50 site or take a look at our E90E50charts collection. Please respect the work of the authors. All of the publications are our own work. Feel free to use it, share it - but please always mention the source. Have fun, The FrankensTeam |
E90E50fx... only formulas
Visualize parts and whole - combine clustered column and stacked column charts
Inga: Disa what? Igor: -ppeared. Really it was 3 years ago we posted our last article?
Well, yes. It was about some chart-hacking to visualize underlying parts and total using a dirty trick with up-down bars. And now we continue where we left: for the same problem Igo-r will show you a better solution. Way much easier and smarter.
Below is a picture to show you the result. The 3 darker columns stand for let's say 3 products, and show the performance in 4 quarters. The lighter, higher column in the background visualized the total of the 3, so the total performance. You can easily follow the trend of the individual products, compare it to each other within the quarter AND you can see the trend of the total too, also you can see the products as part of the whole. All-in-one.
The base idea came from Igor's colleague, Giulia. Thank you for the inspiration!
Just create the chart with the series (3 product series + the same 3 series for the stacked column) and change the data series type. You cannot? Oh, yes. You need the trick: use secondary axis for the clustered columns. (Check the Secondary axis first, change the type after.) Do you see the secondary axis will have different scale?
Something like this:
You need one more trick: add one more series with the total of the 3 data. Put it to the same axis were your clustered columns are - so in the above case, the secondary axis. Set this as line chart. Format it to invisible on the chart.
All the rest is just formatting. :-) And here is our sample file with two versions.
But before you go... take a look at our chart gallery and leave a comment in our visitor's book... we already have 300+ comments there... and when we retire, we will read them all again and again. :-) Frau Blücher: Would the doctor care for a... brandy before retiring? |
200 - 201 - 202
Inga: You know, there's something I've been meaning to ask you. In the transference, the monster got part of your wonderful brain. But what did you ever get from him? Freddy: Mmm. by The FrankensTeam |
Visualize underlying parts and total value - alternative column chart
Freddy: Damn your eyes. Igor: [to camera] Too late. by The FrankensTeam Well, when I had sent my solution to Igo-r about the combined column chart I made to visualize total number and underlying data, he sent me back an alternative one which... well, easier to set up, while visually almost the same. Here the "thin" columns are added as column chart - each row of the original data table is one data series. (You can easily do it: select the underlying data part as rectangular range, add column chart. Then go to the Select data menu and click Switch Row/Column button.) The trick is that: total columns are created using up-down bars. You have to add two more data series to the chart: one for the category total values, and another with 0 values. Both are changed to line chart type. Now add up-down bars to on of the line. Up-down bars appear as columns between the two line data series. Unfortunately up-down bar is always in the foreground of the chart, so it will cover the thin columns. To step over this problem, you can set the color of the bar with transparency, or use borders only. In our solution we chose gradient color. Width of the up-down bar columns can be set under the settings of the line series of which the bars belong to. (Unfortunately this is not available in Excel 2007 - you will need a row of VBA code to do it in this version.) You can download our file to see how it looks like. |
Use Slicer to select value fields for Pivot table or PowerPivot!
Igor: What is this? Freddy: Schwartzwalder Kirschtorte. The Monster: [off-screen] MMMMMMM! Freddy: Oh, do you like it? I'm not partial to desserts myself, but this is excellent. by The FrankensTeam This post is not about formulas. No charts. And it contains VBA. Sorry for that... it's a unique case, but I'm sure it can be useful for many readers... So, in the same time we publish it in Hungarian, I take the opportunity to make it available for those who do not speak my native language. :-) In our example file and also on the below picture you can see a small pivot table showing monthly trend of Profit. We would like to give the possibility to the users to see the trend of another measure. Using slicer it's not necessary to open the Field List pane and look for the measure there - they can easily choose it by one click. If you choose more measures on the slicer, all will appear under Value fields in the pivot. Another advantage of this soultion is that you can limit the measures appearing on the slicer: for example you can offer only a sub-set of measures where the above pivot table with monthly trend can be interesting for analyze. Obviously slicers are for filtering a field and not for choosing field names... So to build up this solution, we will need a short and simple VBA code and an additional pivot table. Let's see how to do it step by step. First: the original data table, base of the above pivot. On the below picture you can see all the measure names highlighted by blue: Step 0: Create your pivot table. You will need the name of this particular pivot. You can find it under Pivottable Tools / Analyze on the left hand side. In the example the name is PivotTable1. You will also need the name of the worksheet where this pivot table sits. In the example it is named Pivot. Step 1: On a blank, new worksheet create a list of measure names (not necessary to include all the measures) and insert a pivot table on this list. Put measure into Row lables: Name the sheet Slicer. Step 2: Define Name choice with reference to the first cell under Row Labels. (Scope of the name should be workbook.) Step 3: Insert a slicer for measure, and move it to the worksheet where your original pivot table is. (Pivot sheet) Step 4: Open VBA Editor (Alt+F11). Find your workbook under Project explorer, and open the code module of the worksheet where the measure pivot table can be found - in the example it is the Slicer sheet. Step 5 Code for normal pivot tables: Follow this step if you use normal pivot table. In case of PowerPivot, go to Step 5 Code for PowerPivot! Put this VBA code into the code module of the Slicer worksheet. Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) Dim ptMain As PivotTable Dim pfMeasure As PivotField Dim i As Long On Error GoTo Errorhandler Set ptMain = Worksheets("Pivot").PivotTables("PivotTable1") For Each pfMeasure In ptMain.DataFields pfMeasure.Orientation = xlHidden Next i = 0 Do While [choice].Offset(i, 0).Value <> "" ptMain.AddDataField ptMain.PivotFields([choice].Offset(i, 0).Value) i = i + 1 Loop Exit Sub Errorhandler: Debug.Print Now(), Err.Description End Sub If you would like to use the solution in your own file, you only have to change the worksheet name "Pivot", the pivot table name "PivotTable1" and the named range [choice] - pay attention this latest appears in two rows! Step 5 Code for PowerPivot: Put this VBA code into the code module of the Slicer worksheet. Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) Dim ptMain As PivotTable Dim pfMeasure As CubeField Dim i As Long On Error GoTo Errorhandler Set ptMain = ThisWorkbook.Worksheets("Pivot").PivotTables("PivotTable1") For Each pfMeasure In ptMain.CubeFields If pfMeasure.Orientation = xlDataField Then pfMeasure.Orientation = xlHidden End If Next i = 0 Do While [choice].Offset(i, 0).Value <> "" ptMain.AddDataField ptMain.CubeFields("[Measures].[" & [choice].Offset(i, 0).Value & "]") i = i + 1 Loop Exit Sub Errorhandler: Debug.Print Now(), Err.Description End Sub Instead of PivotFields we use CubeFields, and a different string expression for AddDataField method. |
Visualize underlying parts and total value using column chart
Freddy: For the experiment to be a success, all of the body parts must be enlarged. Inga: In other words: his veins, his feet, his hands, his organs vould all have to be increased in size. Freddy: Exactly. Inga: He would have an enormous schwanzstucker. Freddy: That goes without saying. Inga: Voof. Igor: He's going to be very popular. by The FrankensTeam Few days ago a colleague of mine showed me a chart in Tableau which he put together to visualize total category value and underlying data (eg. sub-categories). It was a nice and useful example to see the details behind summarized values. I replicated his example in Excel using column chart and error bars (those thin dark-blue lines): It is a combination of column and xy scatter charts where I hide the scatter points (no marker) and add error bars with sub-category values. The base data table looks like: Because this is just a top view, I put the categories into columns with sorted sub-category values below. Using "dots start" and "dots end" cells you can position the error bars according to the width of the columns. All the calculations behind the chart is performed in named formulas. If you need more columns or rows, you can change the name "my_data". Update: Here you can read about another method to create similare chart. This post can help to use the template in your own workbook: How to copy charts based on named formulas to other workbook? |
Pie (doughnut) and bar chart animated comparison in Excel
Igor: Oh, that'll be Inga. Herr Falkstein thought you might need a laboratory assistant temporarily. Freddy: Oh. Inga: Oh, hello, would you like to have a roll in the hay? It's fun. Roll, roll, roll in the hay. Roll, roll, roll in the hay. Roll, roll- by The FrankensTeam There is always a debate if pie-doughnut charts are useful or not. Well, technically this is an xy scatter chart. :-) You can download the file and try it with your data. The animation is created based on the pi-visualization animated chart: https://sites.google.com/site/e90e50fx/home/visualize-maths-in-excel |
PowerPivot: Constant in calculated field results blank rows
Medical Student: But wasn't that the whole basis of your grandfather's work, sir, the reanimation of dead tissue? Freddy: My grandfather was a very sick man. by The FrankensTeam While building up my first PowerPiovot database, I realized a strange problem: tons of blank rows appeared in the final pivot table. Here I would like to share with you the details and how we can avoid it. Hungarian version of this post is available here. DatabaseThe problemIn the particular case it means thousands of rows mixed with rows with values. Obviously I did not want to give this "sick" report with "dead" codes to the users. InvestigationI realized the report contains data for these "dead" codes only in calculated columns, where I used a constant value in the DAX formula. (In the above picutre TMI% column is a calculated one.)I put together a small example to make it clear. And the sales data table: I loaded both tables to PowerPivot and joined with the Product ID field. You can see products number 7,8,9,10 are not represented in the data table. These products (and only these) belong to Product group x and y. I created the first calculated field for turnover growth (I used summarized measures): Growth % 1:= DIVIDE( [turnover 2015] , [Turnover 2014] , 0 ) Looks to be correct. But in our company the definition of Growth is different: we show only the value above 100%, so I have to subtract one: Using this new growth rate the pivot shows the groups without data too:Growth % 2:= DIVIDE( [turnover 2015] , [turnover 2014] , 1 ) - 1 Clearly the problem is the -1 constant value at the end of the formula. I tried with IFERROR too: Growth % 3:= IFERROR( [turnover 2015] / [turnover 2014] -1 , 0 ) The result is the same: pivot contains "dead" products. The only way I managed to eliminate the unnecessary rows is to change the calculation formula this way, using no constant value: Growth % 4:= IFERROR( ([turnover 2015]-[turnover 2014]) / [turnover 2014] , 0 ) What happened?Based on the join between the Product IDs, Excel builds up the calculation background table, containing ALL the Product IDs, but BLANK rows (without data) will not appear in the final pivot table.The problem is generated by the calculated field: in PowerPivot BLANK + constant = constant, so the calculation gives a result for the BLANK rows (these are non-BLANKs anymore) and they will appear in the pivot table. DottorGábor: if we think with SQL codes, it seems as if the JOIN type had been changed: now it is OUTER JOIN instead of INNER JOIN. It's creepy because our pivot shows data which does not exists - only as a result of the way we built up the calcuation formula! SolutionWell... I do not know what is the reason behind this strange way of using BLANK, it only makes problem for me. Fortunately the measures we use can be changed in a way without constant - this is the only solution I found for the problem.if you would like to take a look at the example file, you can download from here. |
La Luna - Moon phases animated chart in Excel
Freddy: Gone, gone, we've got to find him, do you understand? by The FrankensTeam It was just an experiment… First I tried with radar chart, but I realized it could not work. So finally we used stacked area chart combined with pie chart to make splot area perfect square. The calculation behind the chart is not complicated - we used the equation of ellipse to model the border of lighted and shaded parts.You can download our file to play with it. |
Meeting in Milano, trip to the Alps and an Excel chart
Inga: Oh, I'm so happy to meet you at last! by The FrankensTeam Our readers may know that the members of The FrankensTeam live in two different countries. Igo-r is Italian from Milano, IngaKris and DottorGábor are Hungarians from Budapest. We started to write together this blog-site 3 years ago, but have never met personally… until the last week. Milano was always on our bucket-list: Il Duomo di Milano, the fashion streets, The Scala and Leonardo’s Last Supper… must see! So we (Kris and Gábor) bought two tickets, booked an apartment and on 6th of March flying over the Alps we arrived to Milano in the early morning hours. The first impression about Italy was the Alps: the snowy peaks of the mountains were clearly visible lightened by the rising sun. The next day we met with Igo-r, his family and some friends and took a small trip in the mountains. We went up to a rest-house following a steam and ate great local specialities and drunk great wine and grappa there. All were very delicious! On the way down we followed another track where we had seen lots of nice wood statues: kobolds, turtle, drunken donkey, dragonfly, birds, love couple... labirints for the kids and a small museum too, with climbable wall. We spent a really pleasant day together! It was exciting to get to know each other personally, after this long virtual friendship! OK, but we are not a travel-blog, so before our readers escape, let’s change topic and talk about a special Excel chart: The Alps mountain chart. Technically it’s a simple xy scatter chart based on coordinates of the peaks (more than 21k points!). We used 4 series with different color according to the height of summits. On sheet DATA you can set the limits of the series. Also you can filter for the countries*, series and blocks to check where they are on the chart. The summit we had seen from the rest-house is Corni di Canzo - you can find it in the downloadable file too. :-) Here is a link to a dynamically generated panorama view from our viewpoint, from rest-house, where you can also see Corni di Canzo: http://goo.gl/ZQ9Nkj And the link to the panorama-maker tool for those who would like to play with it: http://www.udeuschle.selfhost.pro/panoramas/makepanoramas_en.htm For the chart data points we used this database: http://www.viewfinderpanoramas.org/ALPS.txt *not always indicate the country within whose borders the summit is situated. And… here we are! Ciao, The FrankensTeam |
Area chart with levels - Excel-Croissant
Freddy: [...] Oh, I uh, you know I don't mean to embarrass you, but I'm rather a brilliant surgeon. Perhaps I can help you with that hump. Igor: What hump? by The FrankensTeam The inspiration for this post came from Visualizing MBTA data project by Mike Barry and Brian Card where we found a great interactive chart under chapter How People and Trains Affect Each Other. Here is a static screenshot about the chart: Quote from the original study: "The gray bands show the total number of entries into the all stations per minute over time for each day of the week." Each row represents one day, where the darker grey means more people. This graphic could be a good choice to visualize small and large data together, especially if you need to compare more data series. To replicate the chart in Excel is very easy. The chart type is area chart with more series - as many as the number of grey belts you want. You simply need to shift the original data by a chosen number called limit (according to the number of grey belts). For example imagine that your original data is scaled from 0 to 2000. Draw the original chart (the lightest-grey) and choose 500 as limit - so you will have 4 belts. Cut the chart to 4 pieces: below 500, from 500 to 1000, from 1000 to 1500 and above 1500. Now put the 4 pieces on top of each other. This is what you see in the orange frame below: In Excel you can realize it by shifting the original data down by 0, 500, 1000, 1500 and put the 4 series to the chart. Simply create a data table and minus these numbers from the original data. Create the area chart with 4 series and set the axis limit accordingly: minimum 0 maximum 500. Note: This way the slope of the area-parts will be correct. If we changed the data above 500 to 500 and below 0 to 0, the slope would not be correct because the lines would start from incorrect point on the horizontal axis. We created an example using traffic data about the famous Hungarian “Line 1” which is the second oldest underground railway in the world (opened in 1896) and listed as UNESCO World Heritage. Data (col. C) and chart series (E-H) of Station 8 (Bajza utca - Hősök tere): and chart of Station 8: (enlargement of the above orange frame) As you can see, this kind of chart clearly displays the details for the whole range of values. The color emphasized 'wrapping' allows the easy visual identification of the higher value sections. The final chart showing all the stations together looks like this: We simply copied the charts to one sheet, and put on oversized chart with gridlines, legend and horizontal scale to the background. Now you are wondering why Croissant is in the title? Gábor named the chart because its layers are similar to this bakery product (in Hungarian we name it “kifli”) Kris published a tutorial in Hungarian too. You may also be interested in: |
1-10 of 121