Do you know that you collapse or expand excel charts? Don’t believe me? Me neither. When I first realized that we can collapse / expand charts without writing any macros or lengthy formulas, I couldn’t wait to share it with all of you. This is such a simple yet powerful trick. See it for yourself.
If you want to collapse / expand an excel chart like this, Just follow the below steps.
1. Place your data in rows
Place your data like this.
Make sure you have an empty column next to each series of data. You can also place your data in columns instead of rows. Also summary row (in our case – yearly total) is added and calculated using a formula.
2. Make charts and Position them in the extra column
Select data for each year and make one chart. Since the data is in rows, select a bar chart. Make sure you position the charts in blank columns. Remove any chart axis, grid lines etc if you feel like.
At this point our set up should look like this:
3. Now select the detail rows and Group them
Just select all the rows with detailed data (in our case, the monthly sale values) and group them.
To group rows, go to Data Ribbon > Group in Excel 2007+ or
Data Menu > Group and Outline > Group in Excel 2003. See the below to understand.
| Group Data – Excel 2003
|
Group Data – Excel 2007+
|
4. Finally Adjust Chart Positions so that when you Group the Chart Collapses
This is the tricky part. Depending on excel version, you need to carefully adjust the chart’s size and position (top, left) and data series gap so that when you press “collapse” button from grouping area on left, the chart also collapses neatly.
This step is very straight forward in Excel 2007, but in Excel 2003 it takes some patience. Once you finish it, the collapsible chart is ready.
Go ahead and show it off to your boss or colleague, just wow them.
Download Collapsible Excel Chart Template
Click here to download collapsible excel chart template. This file is tested in Excel 2007, but should work with some minor hitches in Excel 2003 as well.
Learn more:
Show one chart from many – the easiest excel dynamic chart trick
More excel dynamic chart – tutorials and templates
Where would you use Collapsible Charts?
Even though this technique is a bit shaky on earlier versions of excel, I find good uses for this in dashboard reports, financial models etc. where lots of data is the norm. You can also use incell charts instead of regular charts and this technique works just as well.
What about you? Where would you use this trick?
21 Responses to “Red vs. Blue – 35 Cool Visualizations on 2008 US Presidential Election”
[...] Read the rest of this great post here [...]
[...] post by WP-AutoBlog Import var AdBrite_Title_Color = '0000FF'; var AdBrite_Text_Color = '000000'; var [...]
Impressive list, though a few of these clearly qualify as junk (the second one with the hairy circle segments, for example).
Also, that McCain vs. Obama tax plan comparison is wildly distorted, for a debunking and redesign see here: http://chartjunk.karmanaut.com/taxplans/
Holy information/data overload. There are some great visualizations here, but also that are not so good. This list may have been better in small chunks.
[...] Haired Dilbert has some pretty cool visualizations for the ‘08 Election featured on his blog. I really liked this [...]
Cool list!
I know another widget that might have your interest.
It shows the progression of polls and uses data from electoral-vote.com.
I think you might like it:-)
http://www.youcalc.com/apps/1221747067033
... and its easy to put on your blog and fits in your sidebar!
Make a difference, keep on voting!
@Robert .. Agree, few of the charts are not really great. thanks for link, I have updated the post with the link.
@Tony ... That was the point. I wanted to compile a huge list with all the visualizations worth a look.
@Michael .. Welcome to PHD blog 🙂 thanks for sharing that link.
[...] Check the rest out here. [...]
[...] has progressed. With one look you can see on what issues candidates debated most. Also see these 35 different visualizations on 2008 US Elections [via Information [...]
[...] Red vs. Blue - 35 Cool Visualizations on 2008 US Presidential Election Perspctv - another Election Tracking Site. Presidential Watch - what various websites are saying. The Economist’s pole - Economists prefer Obama over McCain. NYTimes - Poll Tracker. Gallup poll tracker… Google Maps Projections Tracker … [...]
[...] Aqui pode igualmente encontrar uma compilação de 35 [...]
[...] 35 Cool Visualizations on 2008 US Presidential Election - Obama vs. McCain [...]
[...] Also see these 35 visualizations from on Obama vs. McCain in US Polls. [...]
First let me say that I love this blog. I have been scouring the Internet and more than likely overlooking the obvious. Can someone lead me to the OFFICIAL source of elections results? I am looking for voter data by county or even town if possible.
The reason I ask is because on Boston.com, they listed the results by town, and have to assume that there is an offical source.
Anyway, any help you can provide will be greatly appreciated!
@Brock: Thank you so much. I guess fec.gov should put up the results as soon as all counties report the results officially. I dont know but I guess it should take a few days before the data is compiled and released to public.
Alternatively did you see what nytimes.com has to offer? They have a county level breakup of results and majority figures in visualization form.
Thanks for your help!
[...] 35 Cool Visualizations on 2008 US Presidential Election - Obama vs…. [...]
@Brock: You can get the data from USAToday site : http://www.usatoday.com/news/politics/election2008/president.htm
just scroll down and select the state name to see its county results in tabular form.
Thanks again. I also stumbled upon this. http://general-election-2008-data.googlecode.com/svn/trunk/json/votes/2008/. It appears as if there was a Google project with the data. I do not know a web programming language, but I am sure there is an easy way to catch the data and put it into a database.
[...] to give a deeper insight into the elections. A top 35 of those visualizations are listed in the Chandoo.org website. B. Shneiderman’s very interesting network analysis of the Senatorial voting patterns is [...]
ts not cool or notx