John Walkenbach, aka "Mr. Spreadsheet" has released the long-awaited Excel Charts book. I was in San Diego and spoke to John on the phone as he was working on this book last April. I highly recommend everyone pick up this excellent book at your local bookstore or through Amazon.com.To go along with the release of this book, the December 2002 challenge is to find the most innovative use of Excel related to charts and or graphics. Here are some examples.
BCG Chart by Ed Ferrero
Ed says, "Demonstrates how to create a bubble chart where the bubbles are themselves pie charts. The workbook is useful in its own right for BCG portfolio analysis (Boston Consulting Group). The technique shown here can be used with other types of charts, turning each point in a chart series to an individual pie or other chart. This is one of those 'they said it couldn't be done' charts."
Animated Cannon Chart by Earl Takasaki
Earl writes: While browsing the Microsoft newslists, I ran across a plea from a teacher who wanted to demonstrate simple cannon-ball ballistics. I created an Excel program that uses an “animated chart” to simulate the flight of a cannon ball from a top view on a map. The chart is a simple X-Y chart with a JPEG map of Charlestown bay as the background with points 0,0 centered on the city of Charlestown. I manually scale the X-Y axises so that the scale would equal that of the map in Km. The student enters an amount of powder which linearly translates in muzzle velocity and sets the elevation angle of one of three cannon. (Try Ft Sumter, 7 kg or powder at 35 degrees). After calculating the x-y positions at each point in time, and using translation and rotation to correspond to the cannon and targets on the map, I animate the flight of the cannon towards the target in both a top view and a side view. The “animation” is done by hiding every line of the data source of an X-Y graph, then unhiding each line one by one using VBA. The “explosion” is done by showing, then hiding increasing sizes of orange dots. Hope you like it!
Demographic Indices by Ken Kranz
Ken writes, This is an example of a spreadsheet I designed to bring together (and compare) an Account and a Brand across a series of Demographic Measures
Interactive Graph without VBA by Vinh Nguyen
Vinh writes, I created this interactive graph without using any VBA coding whatsoever.
Animated Warehouse Locator by Phil Johnson
Phil says, "Just a simple diddy, nothing fancy but rather useful". This is very cool. On screen 1, you select a product. Click the button and an animated path draws how to get to that location in the warehouse.
CD Label Maker by Roy Cox
Roy says, "My entry for the competition uses Excel to create Labels for CDs and Jewel cases. It uses Autoshapes and formating to insert pictures. Text boxes, WordArt etc. can be used to personalise Labels for projects. It prints to Neato and Pressit Labels.
Pricing Sensivity by Ken Kranz
Ken writes, Another (simple/interactive) way I've used charting in Excel to enable a user to "see" potential pricing implications… "nothin' fancy, just Kranzy."
AutoCAD for Carton Industry by Ron Carroll
Ron writes, This is a program I have been working on for a while now for the corrugated carton industry. It is an inexpensive autocad program designed for that industry to create production specifications as well as estimates, quotes, Package load tags. As far as corrugated cartons go there are about 30 different carton styles and about 20 different thicknesses of corrugated board that can be used to make each. Depending on what combintation is used it changes the allowances used in designing the carton. Currently the average small business owner buying cartons has no way to design the cartons they need. So this program is an inexpensive way to do it, it is a combintation of excel and vba and as I said it has a long way to go yet, This copy is just a sample I did for one style of carton and it shows how you can import graphics and and printing to the specification. The actual program has all of the carton styles listed in a vb form window so the user can create any style he may need.
Sorted Pareto without VBA by Henrick Wendel
You have to download this one to see how cool it is. All of the formulas in B are =RAND() functions. Click the checkbox to recalc, and a whole series of dynamic range names cause the chart to redisplay sorted values.
Western Canada Grid System Maps by Anonymous
This is a series of maps that were created created using Excel. The maps calculate the theoretical grid systems in Western Canada. The Dominion Land Survey (DLS) in Alberta, Saskatchewan, Manitoba, and the Piece River Block of British Columbia, and the National Topographic System (NTS) in the remainder of British Columbia. I created these routines as a prototype for a WEB GIS application which is currently in use by Enermarket Solutions. I used Excel to test out the calculations by using Excel to plot out the results. If you are interested, you can check out the production version at Enermarket's web site by downloading their EnerMap application. Normally I would fill in the coordinates of the area to plot and then hit the "map" button to generate the mat then I had some navigation buttons to zoom in/out and north/south and east/west. The sheets that I have shown are the output of the routines which remain proprietary.
Alberta Pricing Map by Anonymous
This second file shows a map of Alberta along with the Nova Gas Transmission pipeline system. In this example I used Excel to plot circles at the location of receipt meter stations and I varied the color of the circles to represent different price levels. This gave a quick visual representation of the results of a rate calculation. This work was done in relation to rate design work for the pipeline system. As you can imagine, we went through a lot of rate calculations and this map was very useful in showing the results of those calculations, certainly quicker than the traditional rate schedules which are essentially tables. This particular example just plots the meter stations on the map. But in actuality the outline of the province and the plot of the pipeline system were also created with excel. I originally used the map to display properties of the pipeline system rather than the meter stations in relation to hydraulic simulation results. As you can see in this map as compared with the above map, I included an orthographic projection which makes it more ascetically pleasing. Examples of these maps can be found on TransCanada Pipelines's web site in the regulatory and tolling pages and are referred to as "dot maps"
Schematics Example by Anonymous
This last file overlays information from an excel spreadsheet on top of an existing drawing. The attached file shows one use of this type of mapping, the status of individual pipes in the pipeline system relating to an allocation methodology being developed for cost modeling. The different colors relate to being in-service, retired, out-of-path, etc. In addition to the different line styles to represent different data, I'm also able to include labels directed either at the pipe units or the nodes. This involved a bit of work because of the vast amount of information being represented. I started out by copying in the scanned pages from a pipeline line schematics book into Excel. The attached file only contains one page of the pages. I then drew lines on top of the schematics and recorded the x and y position of all the points in the lines into a separate spreadsheet. This separate spreadsheet then was the cross reference between the unit/pipe name and its location in the excel file i.e. the workbook, sheet, and location. Having this information I could then just take the unit name and attach some information to it such as age. I could then use a routine to go through my list get the unit name and draw it over the correct drawing in the correct sheet and format it according to the Information I provided for it or add a label to the unit with the information I want displayed. As it turned out, this particular mapping has turned out to be very useful. Because in addition to the straight plotting of information on a familiar diagram, this also provided an excellent cross reference of where each of the pipe and node units were located in the schematics. For confidentiality reasons I have had to smudge some of the details on the original drawing.
Interactive Chart by Prashant Nans
This is a nice management reporting tool. Select the product line from the dropdown, and click the blue button to have a small macro re-filter the data and present the chart. Back to top
Excel is a registered trademark of the Microsoft® Corporation.
All contents Copyright 1998-2008 by MrExcel Consulting.