Cool tool. I built a geocoding tool for the web off of Google Maps and Yahoo Geocoding API, hosted here:
http://www.bazily.com/freegeo

It'll do thousands of geocodings in a snap!

Recently I was asked to use that experience to figure the distance between 5,000 retail locations and 30,000 competitors. Awesome.

Doh.

F-Secure thinks the Excel-file contains an unknown virus.

I submitted the file for whitelisting, or whatever it is that they do to it, and the next f-secure database-update will fix the erroneous detection.

This is an excellent tool that offers me a quick way to visualize my data. However, the location names do not seem to load into the Google Earth file. I'm using Excel 2003 on XP.

Cool file. I have a long list of zip codes that I would like to experiment with. I wonder if there is a way to tweak the file so that it will geocode a zip code if the other address cells are left blank.

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?

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.

Brilliant! I've been struggling with understanding the offset function for weeks, this made it finally make sense.

Beautiful! And can I copy or export this little lovely Format Control box into PowerPoint, say?

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

Yet again...SWEET tip. Thanks Chris.

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.

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?

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

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

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

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?

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? :-)

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

how can you switch the x and y axis when using Add Data?
thanks

How do you dynamically highlight the row item as the graph changes?

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

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.

Excellent tip as to how to use control. Thanks a lot and keep posting new ideas.

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

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.

I never usually comment on stuff like this (I know, I should)...but this is AMAZING. Thanks!

Very useful - thanks for sharing this.

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

This is truly extraordinary. I will build from this and wow everyone. Thank you for sharing the file.

According to Steve dashboards and FADs serve two distinct purposes. In Steve's words:
The greatest clarification that is needed today is a distinction between dashboards, which are used for monitoring what’s going on, and displays that combine several charts on a screen for the purpose of analysis.

So dashboards are for monitoring and FADs for analysis.
A dashboard displays predefined measures that may come from a multitude of data sets.
A FAD will usually display different views of one dataset.
A dashboard is for displaying answers to existing questions.
A FAD is for discovering new things of interest in a data set. Manipulating a FAD leads to new questions and (hopefully) answers to these questions.

So why would one be satisfied with a mere dashboard? A dashboard can display measures covering a great number of areas of interest using data from a large number of sources. The value of having all the measures that are important for your work available in one display may outweigh the value of being able to directly manipulate the display to slice, dice, drill up/down/left/right etc. I think having a seperate tool for analysis makes more sense then forcing your dashboard to do a job it is not designed for or trying to make your analysis tool be a dashboard as well. Use the right tool for the job.

Also analysis might not be the most effective way to spend your time on or even be your job. I reckon a senior manager is more likely to put an assistant onto finding an answer to a question raised by a dashboard measure than performing the analysis himself. In operational monitoring you may just need to keep track of things and inform the relevant team if the value of a measure reaches a threshold. Finding out why the threshold is reached would not be your job.

Nice blog post. I think you made a pretty strong point. There is another angle here but I'm not sure how it fits in. The niche that I am personally interested in is the fact that many dashboards as well as FADs are backwards looking. They rely on the individual to extrapolate what they see, into where sales might end up for the month or the direction sales are headed for the year. With the amount of predictive modeling and simulation analysis that is available, I would really like to see dashboards incorporate much more sophisticated analysis. This doesn't mean that the display of information should be less intuitive, but that the underlying drivers that create the information to be displayed, could be so much more useful.

For example: I know that as of May 7 we have closed 20k in sales. I may also know that historically, in May, we have had 30k in sales booked. I also know that our plan says we should have 35k in sales by the 7th. Of course, there is always uncertainty in any forecast and sales could pick up significantly over the next couple of weeks. Maybe a monte carlo simulation could be run in the background and surfaced to the dashboard in such as way that the indicator tells us that based on current data our expected month end results are:
85% chance of hitting 35k
90% chance of hitting 30k
98% chance of hitting 25k
etc.

Dashboards are fun to implement when you have a company with 60,000 employees and 130 executives who have differing ideas of what is important.

But if you can get past that, you can really teach leaders what is important via a dashboard.

Are you serious? This is an extremely rudimentary tutorial for this blog.

I find these quick snapshot overviews very useful. After 20 years in analytics I am never too old to learn or be reminded of tips and techniques

I appreciated it, although I prefer to use an array function to do the same thing. jeez, I didn't even know you could do that with Excel Boolean functions, I thought they only took parameters separated by commas, like CONCATENATE.

If only CONCATENATE did the same thing, it would actually have a use. Seriously, does anyone know what you can do with CONCATENATE(a,b,c...) that you can't do with a&b&c...?

(the first version of thos comment died without error message and was lost, so that I thought it had just gone somewhere for moderation; this may just be a problem at my end, or it may have been my failure to type "juice")

I appreciated it, although I prefer to use an array function to do the same thing. jeez, I didn't even know you could do that with Excel Boolean functions, I thought they only took parameters separated by commas, like CONCATENATE.

If only CONCATENATE did the same thing, it would actually have a use. Seriously, does anyone know what you can do with CONCATENATE(a,b,c...) that you can't do with a&b&c...?

(earlier versions of this comment died without error message and were lost, even though I typed "juice"; it may just be a problem at my end)

Wonderful. The first attempt disappears permanently, even though I waited for it, so the second attempt only temporarily fails to appear, so that I double-post with the third attempt :-(

Great new design. I love it.

I noticed that the links to state data are not working on this page:
http://juiceanalytics.com/writing/2006/03/census-data-in-google-earth/

Specifically, I'm looking for the Pennsylvania data.

I often use a similar technique to make sure that all the items in one list are in both. The problem is that if I just do =a2=b2, if I insert cells to add items from one list to the other, Excel automatically adjusts the references in the boolean cells and they get all screwed up. The solution I've found is to use "OFFSET" -- even if you insert cells, the references stay static, so the boolean variable is looking in the right place.

I have this problem all the time as well. Any suggestions on how to line up the rows so that they then match? I usually have to do this by hand .. inserting cells and moving the text in order to manually line the rows up.

Oh man, I'm the newest employee and my cube was recently moved across from the GUI jock (frequent audible sighs, grumbles and guffaws, smokers cough/cackle). And I've been learning how to run various sales reports from various databases on various days to send to various people.

At first I was happy to be learning something new, and I was feeling more like a productive member of the team, so thanks for clueing me in/bursting my bubble. But I've just finished my first year of my MBA, so there may be hope.

Juice- yeah, I've been doing this for years, but it is still news to some folks. There are some neat tricks with ones and zeroes, and even pivot tables to find missing items.

Derek- CONCATENATE() and the ampersand have very similar functions... the only benefit that I have seen to the CONCATENATE command is that it makes it easier to just click away at cells, as opposed to interrupting with an ampersand.

Jason- It's all over for you, man.

(just kidding)

Great tip, but it's by no means fool-proof. If just one pair doesn't match it could cause the rest of your list to come up as false - which makes it difficult to figure out which items are new.

Example:

1001 1001
1002 1002
1003 1004
1004 1005
... ...
1999 2000
2000 3000

Your entire list will be off, but you won't be able to provide a listing of what has changed - which is what I would expect the point of this exercise to be.

Wouldn't it be better to insert a column and use a combination of INDEX and MATCH to give a true comparison list that you could sort on?

If it's just numbers, I would subtract one from the other and sort by the formula column.

All of the non-matching numbers would be grouped (at the top or bottom of the list). Simple

I usually use the function, countif to handle this..
Countif(B:B, C3)