+'&title='+encodeURIComponent(document.title)+'&overview='+encodeURIComponent(document.title)+'&urllanguage=en&urlaffiliate=32003&encoding=UTF-8&urlpicture=http://www.viadeo.com/v_img14/shareit/viadeoflower.gif')
I stumbled across these intriguing charts at the Lawrence Livermore National Laboratory's website:
These are not "flow charts." Instead they are "energy flow" diagrams. They tell a rich story regarding how much energy the USA uses; in what form; and for what purpose. I don't particularly like their super saturated colors, but the information is great. From what I understand, the lab puts an enormous number of resources into gathering the data and producing these charts.
I decided to replicate the chart in Excel 2007. Here's an image of out it turned out:
This was seriously tedious work, as Excel has no native chart type to do what is required automatically. Each flow pipe is proportional in it's thickness to how much energy it represents. The thickness of a line can be easily adjusted in a chart dialog, but not by formula. Another difficulty is that a number of flow pipes need to stack on top of each other to serve as a proportionately sized aggregate input to an energy category.
I lightened the colors on the input boxes (had to do it), but otherwise I think my rendition is faithful to the original. I may have stayed too true to flow pipe proportionality. Some of them are so thin they do not print well. This should be addressed. While my pipes are seemingly lined up, they will not survive the chart being resized vertically without some small errors, either gaps in a pipe stack, or overlap.
From this chart I learned a great deal about our country's energy budget. I had no idea that electrical production and transmission was so wasteful (as in 69% waste!), nor did I realize that natural gas was so versatile. Likewise I did not realize that coal produced such a large percentage of our electricity while oil produced virtually none.
So how much is 99.2 Quads of energy? A Quad is a quadrillion BTUs, or 1,000 trillion BTU. So basically, the US uses a hundred million billion BTUs of energy each year! And 57% of that is wasted! I think we can do better.
I'm not an environmentalist, but all of this world's energy comes from the sun at some point, save nuclear, so it's amazing that more of our energy is not directly solar. Solar is currently less than 1/10 of 1 percent! If I were in charge of energy policy, I would move our focus to solar power generated on as many homes and buildings as possible to eliminate the transmission waste and fill the gaps with natural gas for all other energy requirements.
An interesting project would be to create an Excel addin that would allow you to specify category box locations and have VBA do all of the grunt work in lining up the flow pipes, automatically creating the chart.
However, in it's current form, dissecting the chart may prove instructive, as quite a few advanced techniques are used to create it. In it's current form no VBA was used. But it's definitely a chart. You can move the location of everything on it by just altering the data.
Here is the chart:
![Reblog this post [with Zemanta]](http://img.zemanta.com/reblog_e.png?x-id=32e93b7c-db39-4e12-aa3f-ad6df806df3e)
If you liked this article, please share it!
These are also called Sankey diagrams, and much more can be done in Visio. If you like them, check out: http://www.visguy.com/category/visio-content/shapes/sankey-diagrams/
@Joe-
Thanks for that. I did not know their official name. They certainly would be easier in Visio!
Part of the reason I did the chart in Excel was to demonstrate various techniques that can be put to good use in more conventional charts.
Welcome to my blog. I hope to hear from you again.
Regards,
Daniel Ferry
excelhero.com/blog
Daniel,
First of all wonderful work. I was wondering if I can work with you to develop the Excel Add-Ins for the Flow Chart.I thank you in advance for your consideration.
Sincerely,
nico
rnfc.org/ivey/
@Nico-
Thanks for the offer and welcome to my blog!
I'll send you a PM.
Regards,
Daniel Ferry
excelhero.com/blog
Daniel,
Wow. Every one of your posts continues to amaze me with things that I didn't think were possible in Excel. Keep up the great work!
Michael Pennington
@Michael-
You just hit the nail on the head. My whole point for doing the blog is to expand people's mindset of what is possible in Excel.
So I'm very glad to hear that I'm succeeding!
Welcome to my blog. Please feel free to comment as much as you like...
Regards,
Daniel Ferry
excelhero.com/blog
P.S. This Friday's Optical Illusion chart is going to be spectacular (in my opinion).
Nice blog you have here. I've enjoyed having a good look around :)
Daniel,
I want to know how to figure it out in Excel 2003. Is it possible to make it as beautiful as the one you made in 07?
@minjiwie-
I have not tried, but I think the answer is yes for the most part.
With the colors of the pipes themselves, its definitely doable as those are all standard colors.
The boxes (they are really lines themselves) are lighter colors and those precise ones may not be available in previous versions of Excel. But there will be some other lighter colors that are available and you could choose those.
Welcome to my blog and thanks for reading!
Regards,
Daniel Ferry
excelhero.com/blog
@Nico-
I sent you a private email, but did not hear back. It's possible I had your email wrong, so here's mine:
daniel dot ferry at gmail dot com
I look forward to hearing from you.
Regards,
Daniel Ferry
excelhero.com/blog
Like a Newbie, I'm constantly seeking on-line for articles that may assist me. Give gives thanks you It was fascinating.
Finally, a good post about this subject! I can not believe I had to go thru like 20 blogs just to come across this excellent post. All their content together still can not equal the content of this article. I have learned a lot and will bookmark your web page. Maintain up the good job!
First off, your site is incredible. These are far and away the most impressive and polished examples of visualization I have seen in Excel. I can't wait to begin working through some of these examples.
Regarding the Sankey diagram, I have been using Excel to visualize crosstabs and use a macro to set line widths and labels to limit some of the tedium in putting these together.
http://pushindatalikeweight.com/2010/04/23/visualizing-crosstabs-or-pivot-tables-with-sankey-diagrams/
@pompadour -
Thanks for the compliment and welcome!
I looked at your blog. Nice. I'd like to take you up on your offer and get a copy of your workbook. Can you send it to me?
daniel (dot) ferry (at) gmail (dot) com
Thanks,
Daniel Ferry
excelhero.com
Sent. Thanks.
Dude. This is awesome. I've been thinking about a excel chart like this for other uses - not that I could make it on my own. Thank you. Great site.
@Pablo -
You are welcome. Hope to see you around here more often!
Regards,
Daniel Ferry
excelhero.com
Thanks for sharing such a nice info.This diagram is very useful.
solar panels
My friend recommended to this blog.... you have some awesome articles shear. keep it up the great work
ipad applications | how to develop for android