Introducing Chart Chooser
By Zach Gemignani
November 20, 2007
Find more about:
charts
excel
powerpoint
tools
Find and Download Great-Looking Excel and PowerPoint Charts
Chart Chooser is an online tool that answers two questions we commonly get:
- What type of chart should I use to show my data?
- How can I make good looking Excel or PowerPoint charts?
Chart Chooser is easy:
- Check the boxes on the left that best describe your objective
- Select the chart that you want to use
- Choose from Excel or PowerPoint downloads to get a formatted chart template
A few notes about Chart Chooser:
- Thanks to Andrew Abela of Extreme Presentations for inspiring Chart Chooser with his “Choosing a Good Chart” post and for working with us to put this tool together.
- We’ve tried to make the charts both Tufte-compliant (i.e. minimal chart-junk) and visually attractive (thanks to Google for the color scheme).
- Feel free to suggest other types of charts that you’d like to see in the Chart Chooser. Send an example to chartchooser@juiceanalytics.com.
- If you’d like a customized version of Chart Chooser for your organization, write us at chartchooser@juiceanalytics.com or call me at 202.251.7750.
Google Presentations and the Right Tool for the Job
By Zach Gemignani
September 24, 2007
Find more about:
business
powerpoint
presentations
productivity
tools
Last week, Google released Presentations to fill out their portfolio of online, collaborative document types (they already offer text documents and spreadsheets). The Google folks were kind enough to include us in a round of beta testing a few weeks back, giving us a chance to preview this application, find bugs, and offer feedback.
If you give Google Presentations a try, you may be struck by its limitations. It doesn't offer much flexibility in creating presentations, especially when compared to Microsoft PowerPoint or Apple Keynote. The best you can do is create simple text slides on a few predefined templates. On the other hand, it offers unique capabilities you don't get with desktop applications. In particular, we were impressed with how easy it was to share a presentation live online.
I have started to wonder whether calling Google Presentations a "web-based competitor to PowerPoint" or "a PowerPoint clone" was simplistic and misguided. Lumping together software tools is a natural reaction to long lists of features and techno-terminology. Software vendors don't make it any easier to distinguish the differences when they attempt to convince us that their solution is the complete, do-everything tool to satisfy all your [presentation/data analysis/communication/networking] needs.
So, we assume our software tools fall into neat buckets. We assume the tool we are using today do everything we need "well enough." And we assume any new tool is a direct competitor to what we use. As a result, we are severely limited in what we can achieve.
For a long time, I was a fan and a heavy user of PowerPoint. It did what I needed. Perhaps I told myself that what it did was all I needed. A while ago, I had to break off this exclusive relationship.
Now, I find myself using a bunch of different tools to communicate information. On the one hand, this has made my life more complicated. There are new applications to learn and the hassle of moving documents around. But in other ways, it's easier. I use tools designed for the task at hand. And I have opened up a whole new realm of what is possible in terms of organization, polish, and audience engagement.
The table below shows the activities involved in business presentations. For each activity, I have a rough assessment of how well PowerPoint, Keynote, and Google Presentation perform. I also list the current Juice toolset.
7 comments | Show all comments only the last 5 are shown
Paul Soldera said:
We've been using Google tools quite a bit recently. They really shine when you need to collaborate remotely, but after that, the functionality really is limited - especially the spreadsheet program. I haven't tried the presentation one, but must give it a go.
The real drawback for me though, is the response time. I am so used to rapid text/data entry and menu response time that I have almost no tolerance of any perceptible delay. I think this is where ajax and other RIA apps hit a wall. Which is a pity.
Dan Keldsen said:
Nice comparison, and like Paul, I've found myself using Google tools a fair amount (why not, they're free - making experimentation so much easier than "enterprise apps").
Lag-time and some inconsistencies across the "product suite" make working with these tools a bit confusing, but using Google Docs and Spreadsheets has been very handy with some remote collaboration lately.
In your comparisons, I agree with most of your commentary, with one exception...
I'm trying to like Numbers, but while it provides beautiful charts and tables for the most part, it's limitations in color schemes, and in a number of frustrating UI interactions, is giving me heartburn. It's quirky, and on my machine (2 month old MacBookPro - the LED variation), is very slow in switching between pages/tables/charts.
Even though it has been nearly 14 years since I last used Illustrator to create charts, I'm about to ready to give it a whirl again - at least the graphical presentation is sure to be solid, although it's baffling to me that all of these tools, whether coming from a spreadsheet or "art" metaphor are still so clunky after all this time.
I guess the "Tufte-ian" revolution hasn't quite intersected with the usability movement yet, eh?
Chris Gemignani said:
I agree that Numbers is a little weird. While the basic charts are pretty good, the inspector-based manipulation is hard to get used to. The good news is an update to Numbers appeared yesterday that is supposed to address performance.
Tony Rose said:
I'm not familiar with Numbers. I will say that I almost snapped my mouse in half trying to use Google Spreadsheets and dealing with the delay. Even the slightest lag between key strokes and data entry won't work for me. Call me high-maintenance... We have a long way to go before Excel is overtaken in the corporate world.
What, no Xcelsius in the "juicebox"?
GleaM said:
I just heard yesterday the name of a web-based suite called Zoho:
http://www.zoho.com/
For what I did heard and the impression that I got from it (looks very google-like), I think it beats Google suite by far.
Just to add somo other solutions...
Regards.
rolo said:
We have set up a 46" LCD to display KPIs, and use powerpoint, but obviously it has not been the best experience.
Can anyone suggest tools for digital signage in corporate offices where a reports and analysis unit has to display, every other day, kpis, tables with data, trendlines, bar charts, etc. But in a professional way and not like a simple power point?.
Thanks in advance!
Noah Iliinsky said:
Hi Zach,
I've got to agree on your choice of the Omni tools; they really are best-of-class.
I'm curious about what you mean when you say storyboarding. Typically, I'd think of frames that define phases, with some illustration and supporting text. How does that work in OmniOutliner?
Best, Noah
Add a comment
Why make 100 charts when one will do?
By Chris Gemignani
May 10, 2007
Find more about:
dashboard
excel
tools
tutorial
Charts are a great way to explore data. Here is some American baseball data showing player salaries over a five year period.
Charting this data with a line chart would allow us to see trends in salaries by team. However, when we use Excel’s default chart, we get something that looks like this:
That’s quite a mess. It would be a lot easier if we could create one chart for each row.
The OFFSET function is going to help. In its simplest form the OFFSET function works like this:
OFFSET(anchor, rows from anchor, columns from anchor)
That is, OFFSET will start with the anchor cell, go down a number of rows from that anchor and over a number of columns and return the value it finds.
We can use the OFFSET function to create cells that pull a single row of data out of the table dynamically. We create a new row atop of our data and create a series of OFFSET functions that all rely on a single cell (the big yellow one) for their row offset. So changing one cell will pull different rows of data into our fixed location.
Now, chart the data that doesn’t move.
After fixing the chart, we’d like to make it easy to change the value in the big yellow cell.
We can use Excel Forms to build a lightweight user interface. Bring up the Excel forms toolbar by rightclicking on any toolbar and choosing Forms. Place a scrollbar beside the chart.
Right clicking on the scrollbar allows you to Format Control. Link the control to the cell that is controlling all the row offsets. Now, moving the scrollbar will update the chart.
Now, the scrollbar controls the chart. Here is the baseball spreadsheet for you to play with: Baseball_offset.xls Have fun!
Note: this post is adapted from a presentation I gave at eMetrics 2007 in San Francisco.
33 comments | Show all comments only the last 5 are shown
derek said:
Very nice, but don't be too quick to discard the value of all the other lines. Why not present them as in the original mess, but in the thinnest strokes of identical lightest grey, against which your chosen line can stand out in bold blue?
Chris Gemignani said:
Thanks, Derek. Man, sometimes the posts just write themselves.
I've used the technique you describe and it can work very well indeed. In the past, I've done this this VBA, but probably a clever use of offset could make this work without the need for all the coding. We'll write this up sometime. Thanks!
derek said:
Clever use of offset? I added the whole lot in with Add Data, turned one line grey, used F4 and the up-arrow key to repeat thirty one times (you soon get into the rhythm :-) ) brought the blue offset line back up to the top of the stack ("Series 33") and finally, fixed the chart title link, that got destroyed by the Add Data stage. <a href="http://www.branta.demon.co.uk/infographics/Baseball_offset_2_derek.xls">See example</a>
Were you thinking that the grey line representing the selected data must be excluded? No need, it's hidden under the blue line.
Chris Gemignani said:
Beautiful. For the record, I was going to hide the grey line under the blue too. ;-)
Madan said:
Brilliant! I've been struggling with understanding the offset function for weeks, this made it finally make sense.
pg said:
Beautiful! And can I copy or export this little lovely Format Control box into PowerPoint, say?
derek said:
Sadly no. If you want to go to a live Excel application in the middle of a presentation, I suggest you design a hyperlink (Ctrl-K in Powerpoint) to a file that you already have open in Excel, and the Excel formatted to be as blank as possible (Full Screen display, Tools>Options>View set to remove all possible Excel features like gridlines and column headers etc.)
Josh said:
Yet again...SWEET tip. Thanks Chris.
ross said:
Yeah real nice,
The only thing i might do differently is place the scroll bar next to the data list, rather than the chat, might be a bit more intuative? line the way the lines is highlighted, good stuff.
Jeff said:
I might be missing something but I cant figure out who you get the chart title to change dynamically with the selection? I can trick it by labeling the series with the team name (and repositioning) but you have me stumped on the header function?
Darrell said:
Jeff, I know what you mean, its tough to now what team you're selecting. This is a minor usability issue, and not what Chris was attempting to demonstrate. The Offset function is mucho powerful for selecting different data sets in a big data table.
It would be better to have the user select from a list of the team names, rather than clicking sequentially through each one. This can be done with either a data validation, combined with a match() function, or a pick-list form. Incidentally I often use the cell underneath the pick-list form so it doesn't show when viewed / printed.
Another Tip:
I noticed that the Yankees 2005 number exceeded the manually set scale (223 vs. scale max of 200). This is a nuisance to detect/correct on one or two charts and a maintenance hassle if you have many charts.
One way that I found to have Excel automatically set a uniform scale among different data sets is to use the Min() & Max() functions over the entire data set and then Plot a fake data series (or two) with the min & max data.
In order to hide the fake data from displaying, you format the fake series by setting the Line & Marker to None (Format Data Series, Pattern: Line: None Marker: None). and then set the Value Axis Scale set to Auto. Excel accounts for the min / max data series when it calculates the Auto Scale setting and will always choose the same scale (from what I tested).
In this manner, the data will have a uniform scale among the different teams, and you don't have to worry about data exceeding the chart scale, or the Arizona Diamond backs looking equally profitable as the New York Yankees :)
Darrell said:
Jeff, a correction. I don't think I answered your question: you wanted to know how the chart title changed.
When you chart something in Excel, Excel guesses the chart title by picking the name of the first data series (the first argument of the first data series). In this case it is the
'Dynamic Charts'!$b$4 that instructs Excel what to call series One and the chart title.
The series in the spreadsheet defined as:
=SERIES('Dynamic Charts'!$B$4,'Dynamic Charts'!$C$3:$G$3,'Dynamic Charts'!$C$4:$G$4,1)
=Series(Arg1, Arg2, Arg3, Arg4)
where:
Arg1 is Series name, and default chart title if its Series #1
Arg2 is the names assigned to the category / X-axis
Arg3 is the data you want to chart, and
Arg4 is the number of the series
darrell said:
Jeff,
I was looking at Chris's file, not Derek's. I know the trick Derek used.
This tip works for any of the text boxes in a chart (that includes Chart Title, or even individual Data Labels). You can customize the text, either by overtyping it (Select box, hit F2 and type text), OR specifying a cell link.
To do this is a minor trick. You select the box, pause, Hit F2 and type the cell link into the formula box. I find it easiest to use the formula bar, rather than the text box. If you do it wrong, it creates an extra text box in the middle of the chart, if you do it right, the text is whatever is in the cell.
In this case, he selected the Chart Title, did the F2 - edit trick, and entered "='Dynamic Charts'!$B$4" .
Note that you cannot do a formula in the text box, you can only do a cell reference or a named range to a single cell. BUT the cell you reference can use any combination of formulas. Its especially useful to use concatenate() to incorporate figures or amounts into a title or data label.
Example:
in cell $b$4, you could enter
="New York Yankees - 5Yr total $"&sum(c4:g4)
and the chart will update have the title:
New York Yankees 5Yr Total $865.936
Better formatting:
="New York Yankees - 5Yr total $"&fixed(sum(c4:g4),0)&"MMs"
New York Yankees 5 Yr Total $866MM
That type of simple stuff wows my boss everytime :)
Tony Rose said:
Impressive! There has been a lot of great comments to help improve and build upon Chris' post. I do think there is some value to showing all of the basic line charts grouped together so you can visually see the differences in one glance, like Derek's example. Also, you may add a 5-year average column to sort from highest to lowest versus alpha. Keep up the great work!
Anyone else shake their head at the Yankees payroll?
derek said:
A recent discussion <a href="http://www.perceptualedge.com/blog/?p=116">referred to in Stephen Few's blog</a> led me to the pdf he wrote (because he couldn't put the graphics he needed to refer to in the comments box) about using Spotfire to create the (stupidly named, but actually quite clever) "parallel coordinates" graph type. This has inspired me to think of using Chris's baseball spreadsheet as the basis for a "Poor Man's Spotfire" using Excel.
I've made a start creating a view that "brushes" (fancy word for "selects") "The Highest/Lowest N baseball salaries in the year nnnn" and highlights those in thick blue against the thin grey of the rest of the teams. I used INDEX/MATCH instead of OFFSET. The next thing I want to do is try to highlight the "N teams whose pay profile looks most like Team T", using a least-squares fit.
When and if I make it a little more robust and add some more of the Spotfire functions Few uses in his article, I'll post it to Chris. Or y'all can start picking up the challenge on your own. How far can you push Excel to mimic exploratory data analysis with parallel coordinates and brushing? :-)
Chris Gemignani said:
Great idea, Derek. We were playing around the other day with another concept in Spotfire "heatmaps" for displaying tabular data. These turn out to be very easy to recreate in HTML tables. We'll post a solution sometime soon.
Fumiko said:
Thank you so much for sharing great ideas as always. It's clean and elegant, and overcomes the mess we could easily fall by using standard chart alternatives provided in original Excel.
I already have a data that I could use this solution. Once again,thank you for sharing this tip with us!!
craig said:
how can you switch the x and y axis when using Add Data?
thanks
Michael said:
How do you dynamically highlight the row item as the graph changes?
Chris Gemignani said:
Michael, I use a formula-based conditional format that looks roughly like: "ROW()=$A$2"
Sasikumar said:
It is really helpful to us to make a good presentation. It is very intresting also. let me know these type of other features in excel graphs
Mark said:
NICE IDEA!!! This would be REALLY useful for comparing multiple scenarios, which would mean I could select up to say 3 or 4 to plot at the same time. I will give that a shot, and thanks for the great start.
N Shivkumar said:
Excellent tip as to how to use control. Thanks a lot and keep posting new ideas.
derek said:
<a href="http://peltiertech.com/">Jon Peltier</a> has created a <a href="http://junkcharts.typepad.com/junk_charts/2008/01/football-rank-2.html">parallel coordinates tool in Excel</a> here, to explore quarteback preformance data. The two chart controls choose which player to highlight in red and which in blue. the rest of the players are pushed into the background as gray lines.
Randy said:
This is great. Is their a way to use the same scrollbar on multiple figures. Specifcally if I want to pictures side by side that flip as I scroll.
andrew said:
I never usually comment on stuff like this (I know, I should)...but this is AMAZING. Thanks!
Andrew said:
Very useful - thanks for sharing this.
Adam said:
This is helpful. I am trying to do something similar, and maybe someone here can help. I would like to create a bar + line graph and have the date range (x-axis) be controlled by a drop-down menu. Ideally, I would like to be able to select a beginning date and an end date and have the graph adjust automatically. Is this possible?
Thanks
Bill said:
This is truly extraordinary. I will build from this and wow everyone. Thank you for sharing the file.
ray said:
great and simply outstanding!!!
Tim said:
This is great!! This is such a time saver. One question, how do you auto scale the axis in case you have a very wide range of data. For example in one graph I need a to scale to 200, in the next one I need to scale to 5000.
Thanks and keep up the great work you're doing.
Sean said:
It sounds like an amazing concept, however, whilst using Excel 2003. It seems that the 'Control' tab on the 'Format Control...' window is missing. Would you be able to provide a sample excel document so that I could look at the VB code behind the scroll bar? or provide the code on here?
Many thanks!
Sean said:
Oops, I didn't see the sample document mid way down the page!
Many thanks!
Add a comment
The Google Analytics relaunch
By Chris Gemignani
May 9, 2007
Find more about:
analytics
bi
innovation
interface
tools
Google Analytics has been rebuilt and the result redefines the frontiers of doing analytics on the web. Avinash Kaushik has the definitive early review.
I had the privilege of attending the launch and playing with the early release. Here are a few things I noticed.
- Speak my language: Google has put a lot of effort into replacing specialized terms with everyday ones. This makes the application usable by a broad base of people and is one way to fight GUI Jock-itis.
- Speed kills: The interface is easily reconfigurable and fast. I've long argued that interface speed is a substitute for configuration options. I'm curious to play with the tool and get a better sense if this is true.
- Flex rules: Much of the componentry for viewing data in Google Analytics is built in Adobe Flex. This is similar to Google Finance, and not at all like GMail or Google Reader, which use the GWT. We believe this has profound implications for analytical tools on the web and will dig into this in later posts.
2 comments
FM said:
Nice and must say timely review, I've been using Analytics.google.com and found it good, some time.
some time it's not an average site owner would like to look at, i mean you may lost your way through it.
Talking about Speed, it's been major sat back, till the day. however if it's improved in relaunch, it's great.
Lynn Cherny said:
I had lunch with a friend recently who told me that the Mindmaps fellow who built the new Google Analytics was just more used to Flex. So it may not be sinister or deep, although I agree with you on the overall benefits of the feel and design :-)
Add a comment
Analytics Roundup: Late April edition
By Chris Gemignani
April 27, 2007
Find more about:
census
data
juicesite
population
powerpoint
presentation
presentations
pubapp
statistics
tools
- Population Estimates Data Sets
- US census data
- Are you generic? / Wilson Miner Live
- Wilson Miners post re: Django's generic views
Earlier writing
Subscribe & Browse
- Visitors Guide Our most popular posts and tools
- Subscribe via RSS or Email
- Browse by Topic, Date or Author
Explore our world
Recent Posts
- Survey Results: Are the Viz-Pundits Really Helping?
- Chart Selection, Art and Science
- Are you using information effectively?
- Predictive Analytics: Interview with Eric Siegel, Ph.D.
- Important Dialogue
- Automated PowerPoint Generation, or Making a "Slide Factory"
- Depth and Discovery: Powering Visualizations with the Google Analytics API
- Juice's Simple Font Framework
- Designing Great Dashboards - Part 3
- Airline and Airport Traffic and Delays: A JuiceKit Visualization Demo
23 comments | Show all comments only the last 5 are shown
Clint said:
Not bad for a v1 guys - I especially like the waterfall chart as a funnel visualization. The comparative column and bar charts seem a bit noisy though - might be as simple as using thicker bars and columns, then again might not.
aaron said:
very cool!
why did you exclude the basic line chart from the 'relationship' category but include the two-axis line-column chart?
Chris Gemignani said:
Clint: Sounds like someone's volunteering...
Aaron: In my experience, the basic line charts are used to show the performance a bunch of similar series over time. The line-column is typically used in business to show two aspects of the same thing over time, sort of like showing prices and volumes in stock charts.
MikeW said:
Excellent tool, thanks guys! I've sent it round the office so now everyone can create clear, uncluttered charts. RIP grey background!
grossu said:
Awesome. It would be great if you add Numbers for ac format for uploading.
Tony said:
Guys, great tool much like the Chart Cleaner! I also understand that you aren't going to be able to please everyone.
The good: Excel downloads that show the table templates (specifically for the waterfall chart, which many people have trouble with), color schemes and formating are excellent, interactive selecting is great, inclusion of bullet charts and pushing less chartjunk is a big plus.
Opportunity: I see some downfalls with the options that are presented to me. Much like some of the dashboard tools, it gives me the option of what's available, but does not indicate what is optimal. For example, by selecting Composition, I get everything from bar charts to waterfall charts, to pie charts, to tables. It may be helpful to possibly rank which are the most effective. Why use a pie chart when a bar chart is a better choice. Or, use a stacked bar chart (I find very ineffective) when a line graph is probably better.
I think the objective here is to show what's possible and appeal to the masses. I just question your design when you have previously voiced that some of these break the fundamental rules of data visualization (pie charts).
Jesse Robbins said:
Outstanding work!
Joe said:
This is awesome! Great time saver. Thanks for the great posts and tips.
zaxl said:
Great tool! With Chart Cleaner you open mi mind to a whole new world of graphics. This is an excel-ent addition to my arsenal. Many thanks!
govi said:
Great!
Using it right now for my scorecards!
arun said:
I really appreciate your work... the charts are really cool... I am going to use them at work... thanks guys
Ed O'Loughlin said:
I'm sure it's a great tool, but http://chartchooser.juiceanalytics.com/ makes Firefox on XP fall over.
Stef said:
Hmmm... is it only available for US users? Doesn't work from my place - Switzerland. Neither in Safari nor in Firefox. Just some kind of legal note appears...
Chris Gemignani said:
Stef: The problems you're having relate the DNS propagation. We only set up the chartchooser.juiceanalytics.com address earlier this week and it takes a while for all the far corners of the Internet to know about that address. Not that Switzerland is that far away these days.
If you wait a few days, it should work for you.
Ed: I'll give it a try on Firefox with XP.
Michael Vu said:
thank you for this! it's going to be very, very useful for entrepreneurs and business owners.
Kelly O'Day said:
Nice job!
The line chart uses a legend to identify the 4 data series in the example. Legends add an extra step for chart readers, they have to move their eye back and forth between the lines and the legend. This can interfere with quick, easy chart interpretation.
Why not use series labels to make it easier for the reader? I've modified your line chart file to add a procedure which adds series labels instead of the legend.
You can see it <ahref="http://processtrends.com/toc_chart_doctor.htm#Replace_Legend_with_Series_labels"> here</a>.
Kelly
Kelly O'Day said:
Here's the link again. It didn't work when I used <a href="... <.a>
http://processtrends.com/toc_chart_doctor.htm#Replace_Legend_with_Series_labels
Tony said:
Kelly - Nice job! The link in your post above didn't work, so I just went to your site to find the example. I am a big fan of your changes. I would always opt for series labels versus a legend. They take up less space and make it more visually appealing.
Kelly O'Day said:
This is my 3rd and hopefully final try at getting the link to work.
http://processtrends.com/toc_chart_doctor.htm
Stef said:
Hey there, the website is still not visible from Switzerland! Gush....
Mike said:
Hi guys!
This is fu&%$ awesome! Thank you very much for this!
Tom said:
I love your site and have used several graphs to make myself 'look good' at work. Thanks.
I want to use the Waterfall chart but for the life of me I can not figure out how you remove/hide the color fill from the data points after the first one and leave it in for this one.
Thanks.
Priya said:
Hey thanks for this useful site... I was wondering if there is a write up for different type of charts displayed here, as in what type of data or steps / FAQs etc.
If I am missing something here, let me know
said:
Add a comment