E90E50fx... only formulas

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

PowerPivot: Constant in calculated field results blank rows

posted Jan 12, 2016, 8:55 AM by Krisztina Szabó   [ updated Jan 15, 2016, 9:58 AM ]

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.


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

The problem

In the pivot table lot of products (column MRDR) appeared for which we have no data in the sales table:

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

Here is the product masterdata table:

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 )

And here is the pivot table showing Growth % 1 by Product groups:

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:

Growth % 2:=  DIVIDE( [turnover 2015] , [turnover 2014] , 1 ) - 1

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:

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!


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

posted Oct 19, 2015, 1:36 PM by Krisztina Szabó   [ updated Oct 19, 2015, 1:38 PM ]

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 start the animation which will increase the time by 2 hours in 360 steps - simulating approx. one month.
You can download our file to play with it.

Meeting in Milano, trip to the Alps and an Excel chart

posted Mar 16, 2015, 11:26 AM by Krisztina Szabó   [ updated Mar 16, 2015, 11:42 AM ]

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.

The FrankensTeam in Milano

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!

The FrankensTeam

Leave a comment

Area chart with levels - Excel-Croissant

posted Feb 1, 2015, 11:32 AM by Gábor Madács   [ updated Feb 1, 2015, 11:53 AM by Krisztina Szabó ]

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.
You can download our file.

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:

Leave a comment

100 - 101 - 102 …

posted Jan 19, 2015, 3:21 AM by Krisztina Szabó   [ updated Jan 19, 2015, 4:12 AM ]

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

Leave a comment

How to copy charts based on named formulas to other workbook?

posted Sep 23, 2014, 9:17 AM by Krisztina Szabó   [ updated Sep 24, 2014, 3:57 AM ]

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.

1. Open the downloaded example file.

In the Name Manager (Ctrl+F3) you will see there are some named formulas of which rng is the dynamic named range of the data table.

2. Open your file where you would like to use the chart.

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

3. Move the sheet with chart to your workbook.

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:

4. Change the data connection link source to your file

Under Data menu / Connections group / Edit Links click on Change Source and choose your own file from the Change Source dialogue box. (in the example it is My_workbook.xlsx)

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.

Some examples where the trick could be useful:

Leave a comment

Convert numbers to words with Excel formula - language collection

posted Aug 18, 2014, 8:43 AM by roberto mensa   [ updated Oct 21, 2015, 8:46 AM by Krisztina Szabó ]

Wife: Harry, he was at it again.
Harry: So what do you want me to do about it?
Wife: Everyday.
Harry: Let him, let him.
Conductor: New York next, everbody out for New York.


Wife: Hans, er dachte schon wieder.
Hans: So?
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!

Hungarian   (Hungarian language post on IngaKris's blog.)
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!
Spanish - we received this file from Carlos Collazos with a VBA solution. Thank you, Carlos!
Romanian - we received the file from Ciprian Stoian with a formula solution. Thank you Ciprian!

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

Leave a comment

Trendline formatting bug in Excel 2013 and the secret of The FrankensTeam

posted Jul 15, 2014, 3:15 PM by Gábor Madács   [ updated Jul 16, 2014, 6:23 AM by Krisztina Szabó ]

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.


The secret - Teamwork!

The beginning of the story was that IngaKris had seen a post somewhere suggesting to create a bubble+line chart combo starting from a normal line chart and adjusting the marker size manually. Because this way the result is totally static, she started to think if it was possible in a dynamic way. Bubbles could be done dynamically only on a bubble chart, so this must be the base chart. But bubble chart could not be combined with any other chart types, so we could only use the elements of the bubble chart itself. Scrolling over the “Add chart element” option, the trendline seemed to be suitable.
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...

Trendline formatting bug in Excel 2013

To reproduce the problem, let's see step by step what you should do:
  1. Create a chart with two data series, using the default colors. Can be any chart type on which you can use trendline.
  2. Add trendline (any type) to both of the data series.
  3. Change the color of the second trendline to the same as the trendline of the first series.
You have to see something like:

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.

Leave a comment

Combine Bubble and xy scatter line chart

posted Jul 8, 2014, 8:39 AM by Krisztina Szabó   [ updated Aug 26, 2014, 2:35 AM ]

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?
Inga: What?
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.

Let’s see step by step.

1. Build up the help columns.
The very-first data (yellow cells) starts with a fixed 0.
  • Growth-help: from the second data is equal to the original growth data (this is the x values of the series)
  • Profit-help: from the second data has a simple formula: the original Profit multiplied by 2, minus the data above. So for example the dummy 8% for March is the average of the original March and February data: (11%+5%)/2 = 8%
  • Size-help contains fixed 0 values.

2. Add a dummy series to your bubble chart using data from the help column:
(right click on the chart, Select data / Add)

3. Add a trendline to the dummy series:
  • in Excel 2010: Select the dummy series under Chart tools / Layout, and add a moving average under Trendline
  • in Excel 2013: Select the dummy series under Chart tools / Format, and add a Trendline with moving average under Chart tools / Design / Add chart element / Trendline.

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:

x =booble_linea!$C$4:INDEX(booble_linea!$C$4:$C$102,COUNTA(booble_linea!$C$4:$C$102))
y =OFFSET(x,,1)
size =OFFSET(x,,1)
x_dummy =OFFSET(booble_linea!$G$3,,,ROWS(x)+1)
y_dummy =OFFSET(x_dummy,,1)
size_dummy =OFFSET(x_dummy,,2)

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!

Please share your thoughts with us!

Leave a comment

Vintage charts in Excel: arrows from one point

posted Jun 27, 2014, 5:55 AM by Krisztina Szabó   [ updated Jul 16, 2014, 6:21 AM ]

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!

Play: State Department Store by Tibor Barabás and Béla Gádor. 1952
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.

The Excel reproduction looks like:

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:
  • The chart type is a combination of xy scatter, pie and doughnut.
  • We used a pie chart filled with a black circle drawing to shape the circle border.
  • The text labels were added using a doughnut chart.
  • We rotated the labels using our Rotate chart label tool.
  • The marker on the best department is a roman number I - it was added as manually rotated picture.
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.

Leave a comment

1-10 of 115