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
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.
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.
Medical Student: But aren't you the grandson of the famous Dr. Victor Frankenstein who went into graveyards, dug up freshly buried corpses, and transformed dead components into...
by The FrankensTeam
It is always exciting to see vintage charts and infographics - how many good chart and formatting ideas were discovered before the Excel-era. Interesting challenge to re-create these charts with our modern techniques - so with Excel.
In Graphic presentation by Willard C. Brinton from 1939 we found a bunch of great chart examples.
This one is on page 152:
The book tells:
<<The chart represents occupational distribution in 1930 of 134 MIT graduates of the classes of 1917 1929 inclusive. The emphasis of the area of "Major Executive" tends to make the comparison a vertical one resulting in area comparison.>>
To supress the small (below 2) values in data labels, we use custom number format with code: [>2]0,0;""
Here is the Excel-reproduction:
How we did it?
The chart type is stacked bar chart, the gap width was set to 30%. We added Series Lines to the chart - these thin lines to connect the data series. (Chart tools / Design / Add Chart Element / Lines)
The widest data series (Major Executive, in column F in the below data table) was formatted individually using pattern fill. As you can see on the original picture, this series is highlighted with stronger series lines.
Unfortunately it is not possible to set the format of these lines individually, so we used a small trick: two more columns were added to the data table: before and after the highlighted series you can see 0,2 values (column E and G). This very thin series is formatted with solid black fill.
Download the Excel file.
You can find another interesting vintage chart reproduction here.
Or take a look at our gallery with >150 Excel charts.
Inga: Oh, Doctor. I'm sorry
Freddy: No, no. Be of good cheer. If science teaches us anything, it teaches us to accept our failures as well as our successes, with quiet dignity and grace. Son of a bitch bastard, I'll get you for this. What did you do to me? What did you do to me?
by The FrankensTeam
We participate on a great challenge opened by Chandoo: visualize US State to State migration trends. Now all the 49 entries are visible here. Honestly, it took a bit much time to publish the entries, we were not able to wait and few weeks ago we shared a little trick we used in our work: use slicers "without" pivot table.
But now we are happy to see all the great works and ideas together - thanks Chandoo for sharing it, and good luck for all the participants!
You can download our file from Chandoo's site or from this link too.
Here is a screenshot about our work with Leaving view, for California as selected state:
Please tell us if you like it!
It - could - work!The picture will be updated when the formula is calculated, so when you change the value in the cell with link to be encoded (A9 on the below picture) you will immediately see the picture.
We would like to share a tricky UDF to generate QR codes on your worksheet.
The technique is simple: you can create a new QR picture or update an existing one by using URL_qrCode_SERIES() formula. The picture itself is generated by Google chart API, so your system needs to have internet connection.
Download the file.
The base concept of this UDF technique is the fact that UDFs could modify, delete and add shapes to worksheet.
We use Google chart API to generate the QR code itself. Our UDF composes the URL for the API, and use this URL as the first argument of the Shapes.AddPicture method.
You can use our example file, or you can copy the code to your own files.
You may also be interested: