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.
E90E50fx... only formulas
In the pivot table lot of products (column MRDR) appeared for which we have no data in the sales table:
Important to know that not all the products from masterdata table are represented in the sales data table (we have old products, technical Product IDs, products listed in other countries, for which we have no sales).
Our real-life database consists of two main tables: sales data by product (Product ID, turnover, volume, etc.) and product masterdata (Product ID, category, brand, etc.). These two tables are linked by Product ID.
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.
In 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.
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):
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:
Clearly the problem is the -1 constant value at the end of the formula.
I tried with IFERROR too:
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:
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!
if you would like to take a look at the example file, you can download from here.
You can start the animation which will increase the time by 2 hours in 360 steps - simulating approx. one month.
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.
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:
And the link to the panorama-maker tool for those who would like to play with it:
For the chart data points we used this database:
*not always indicate the country within whose borders the summit is situated.
And… here we are!
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.
You can download our file.
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.
Source of “Line 1“ data: http://lazarus.elte.hu/hun/digkonyv/szakdolg/2012-msc/zsiga_erika.pdf p.10
You may also be interested in:
Fraü Blucher: Would the doctor care for a brandy before retiring?
Freddy: No, thank you.
Fraü Blucher: Some warm milk, perhaps?
Freddy: No, thank you very much. No thanks.
Fraü Blucher: Ovaltine?
Freddy: Nothing, thank you. I'm a little tired.
Fraü Blucher: Then I will say good night.
Freddy: Good night.
by The FrankensTeam
We decided to publish a short post when we receive the 100th comment in our Gallery Visitor’s Book. Actually when I found time to write, we already have 102 comments. :-)
Freddy: Reputation. Reputation.
Looking back to the history of the Gallery, we had 40k visitors - well, we are not Louvre, but we are happy with the results. All the comments - and the lot others we received in e-mail - made our days and proved that what we do is interesting, useful and thought-provoking for others.
Unfortunately in the past months we did not have time to work on new charts and new posts…
Inga: Dr. Fronkonsteen, are you all right?
Yes, we are all right, and we still have some ideas. So let’s hope soon we will return to the Excel-life, because we have some great projects in the “Under construction” folder. :-)
At the end of this short note, let me quote from a nice mail - about our team:
“And I say to myself, <<Of course they are. They are from Budapest & Milan. How could they do any less?>>"
Villager: He's a Frankenstein, and they're all the same. It's in their blood, they can't help it. All those scientists, they're all alike. [...]
Freddy: Mr. Hilltop, will you hop up on your feet and stand beside this table. Nice hopping. Mr. Hilltop [...]
by The FrankensTeam
As an answer to a frequently asked question, we would like to share a tutorial about a simple method to copy a chart based on named formulas to your own file and use it according to your needs.
Our charts in E90E50charts gallery are usually built on named formulas. This way we do not use sheet cells to store the calculated background data, so the sheet is clear: the reader only needs to change the base data and labels in the downloadable example file and the chart is automatically updated. Unfortunately the example file is not suitable for those who would like to use the chart in an existing workbook. In this case the simple copy and paste the chart method does not work since the named formulas will not be created in the other file. Because of the complexity manual creation and re-linking the names is usually irksome and time consuming.
So how should you copy the chart? The trick is to copy the whole sheet and replace the link under the Data menu. Here is how to do it step-by-step using the matrix bubble chart as example.
In this tutorial the file we use is My_workbook.xlsx. (It is important you have to use an existing file, alternatively you have to save the file before going to step 4.)
In the example file right-click on the sheet tab and choose Move or Copy
In To book select your workbook and check the Create a copy box.
Now the sheet is in your file, and the named formulas are created accordingly - you can check it in the Name Manager.
The problem is that the chart series are linked to the original file, as you can see in the chart series formula:
You will see as if nothing happened on the Edit links pane, but now the chart series references are correct, showing the name of your file:
After you Save, Close and re-open your file, the link to the original file will disappear from the Edit links pane too.
Now you can use the chart in your workbook without any problem, and you can change the reference of the data table.
Romanian - we received the file from Ciprian Stoian with a formula solution. Thank you Ciprian!
Spanish - we received this file from Carlos Collazos with a VBA solution. Thank you, Carlos!
Russian - we received the russian versions from Ivan Bondarenko. He created 3 version of the formulas: for masculine gender, for feminine gender and for currency. Thank you, Ivan!
Hungarian (Hungarian language post on IngaKris's blog.)
INT. - AMERICAN TRAIN
Wife: Harry, he was at it again.
Harry: So what do you want me to do about it?
Harry: Let him, let him.
Conductor: New York next, everbody out for New York.
INT. - EUROPEAN TRAIN
Wife: Hans, er dachte schon wieder.
Wife: Aber jaden tag.
Hans: Lass ihm, lass ihm.
Conductor: Transylvania nachste. Jeder austeigen fur Transylvania.
by The FrankensTeam
From time to time the question of converting number to words raises on Excel help forums. If you google for the topic, you can easily find VBA solutions, but the problem could also be solved using Excel’s built-in formulas.
Years ago in the Excel Hero LinkedIn group a challenge was opened by Daniel Ferry to create a formula for the English language conversion. The discussion and the “formula development process” was indeed interesting, and with the help of lots of great minds and strict eyes, we managed to create a relatively short formula.
Recently, for another forum request, Igo-r put together the Italian language formula (plus UDF solution too), and just for curiosity, IngaKris created it for Hungarian language.
Do you have formulas for other languages? French? German? Please slip your formula through slot in door, so we can publish it here!
If not - please take a look at our files! Maybe you will be inspired to develop formulas for your language!
Here are the files for download - please note there are some defined names (2 or 3) in the files. If you would like to use the formulas in your own workbook, please do not forget to copy the names!
Ps: In Italian, for reverse conversion (undici --> 11) there is a VBA solution using regular expressions created by Igo-r too.
You may also be interested in:
Month and day names in all languages with Excel formula
Freddy: So then, what we're aiming for is a being approximately seven feet in height, with all features aither congenitally or artificially proportionate in size.
Igor: Something like this.
Freddy: Hello. You've caught something there. Crude yes, primitive yes, perhaps even grotesque. Yet something tells me that this might be our man.
by The FrankensTeam
There are two reasons to write this post. First of all, to share a good news we are really proud of: our dashboard won the Chandoo data visualization contest! We would like to say thank you to all those who voted - either to our or to the other 11 brilliant works! Take some time to analyze them - there is a lot to learn from the masterpieces.
Unfortunately - by definition - the trendline will never connect the data points… unless you have only two data points. So IngaKris added as many 2-point series as the number of values to the chart and added moving average trendline to all the 2-point series. Set the color of the trendlines to the same, format the line according to your needs. The result is more dynamic as the originally suggested one, although it needs lot of manual work, especially if you have several data points.
But it is something, so she sent a picture about it to Igo-r, as a proof that it is possible, asking how it was done.
And then - as usual :) - Igo-r created a way much more clever and easier solution, which could be used with dynamic named ranges too. That’s what you can read here.
IngaKris was a bit sad because she stopped thinking after finding the half-dynamic solution, but… that’s how we work: if one of us discovers something, or finds something new or (seemingly) impossible, he or she will immediately share it with the other. This excited and challenging environment and the team-effort turns a good idea to be something better, to be the best.
But this is not the end of the trendline story.
When IngaKris re-opened her first file with several same-colored trend-lines, she realized that the color formatting was somehow lost...
4) Save the file, close and re-open in Excel 2013. You will see the color changed:
As we see, one can not set the color of the second trendline to be the default color of the first series. The strangest thing came up when we opened the same file in Excel 2010 (hungarian version) - the trendlines has the color as it was set!
Find more FrankensTeam works in our gallery with >150 Excel charts.
(right click on the chart, Select data / Add)
Freddy: Behind the bookcase. Hand me that robe, would you dear? You were right. It's coming from behind this wall. Where is it, where is it?
Freddy: There's always a device. If I could just spot the triggering mechanism. Hello. It seems louder over here. Hand me that candle, will you? Put the candle back. Alright, I think i have it figured out now.
by The FrankensTeam
Have you ever wanted to connect the bubbles of a bubble chart with lines?
For example to create kind of a timeline: how the position of a product changes month by month in a Growth/Profit matrix.
You would like to see something like this:
Bubbles are based on this sort of data table:
Well, it’s a fact that bubble chart can not be combined with any other charts, so the usual “change series chart type” way will not work.
Our out-of-the-box idea is to use trendline.
We started to think about moving average trendline, because this is very simple to calculate based on the Period - as you can read on MS office site:
“For example, if Period is set to 2, the average of the first two data points is used as the first point in the moving average trendline. The average of the second and third data points is used as the second point in the trendline, etc.”
The idea is to create a dummy data series which determines a trendline which equals to the connecting line of the original data points. Only need to reverse the calculation logic of 2 period moving average: now the first data and the moving average (=the original data) is given, and need to calculate the second data.
In other words, you will need a help column, where the average of the first two data is equal to the January Proft (-2%), the average of the second and third data is equal to the February Profit (5%), etc.
The formula behind this dummy series column now goes without saying. Obviously we need one data point more than the original series. This additional data point could be a fixed value or you could use the first data of your original table.
The very-first data (yellow cells) starts with a fixed 0.
2. Add a dummy series to your bubble chart using data from the help column:
3. Add a trendline to the dummy series:
4. Select the trendline and format it according to your needs.
+1. Dynamic model
It is easy to change the model to update automatically when you add new data below the data table. You only need to copy down the dummy series formulas to more rows, add names and use the names in the chart series instead of fix sheet ranges.
In our file you will find the below names:
You can download our example file for better understanding:
In the next post we shared the story of this discovery and a bug we discovered about trendlines. Please read it!
This post is available in Hungarian.
Please share your thoughts with us!
Fraü Blucher: And this is your room. It was your grandfather Victor's room.
by The FrankensTeam
In the last post we shared a vintage chart reproduction found in a great, old book about charts. Today's vintage chart has a totally different story. The below picture is from a (Hungarian) wall calendar illustrated with photos of legendary actors and actresses.
The chart is a stage prop... well, it is not suitable for practical use... it's for theater use only!
Characters: Tivadar Bilicsi (Uncle Bezzeg), Kálmán Latabár (Dániel) and Kamill Feleki (Glauziusz)
Here is the stage prop enlarged:
The title says "Table of competition". The segments in the circle represents store departments, the lines are the performance of those departments in the socialist competition. The best department ("Férfi ruha" = Male clothing) is marked with roman number I.
NOTE: In Excel 2013 it is possible to change the size of the data labels individually,
so the final result with the text labels looks much more better comparing to 2010 version.
It was a funny exercise to create this chart though as you see, it is definitely not for practice, but some tricks could be handy in other projects:
Download the file so you can see the pictures we used and the data behind the xy scatter lines.
Take a look at our gallery with >150 Excel charts.