MrExcel.com - Your One Stop for Excel Tips & Solutions

Coolest Use of Graphics in Excel

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.

  • I used to believe that Excel was strictly for pushing numbers around. One of my most innovative clients had me design a product catalog in Excel. While the sales rep is meeting with the buyer at the store, the rep can run a report of the top 10 selling bracelets or necklaces or watches. They can either get the report as a bunch of numbers, or request pictures of the items. Up comes a form with 10 pictures. Click a picture to enlarge it, click again to place an order. I give credit to my client for coming up with this innovative use of Excel. It is a very powerful sales tool.
  • Another member of the MrExcel team, Mala Singh, has developed incredible graphics programs using XY Charts. If you haven't seen Mala's work, check it out at the MrExcel Engineering page.
  • During our Oct 2002 Challenge, Matt Nauman's Screen Soother was a cool use of graphics in Excel.
  • One of my earliest clients was a lady named Bonnie Datta. Bonnie would use Excel to create the most incredible plans for her weaving.

These are just a few examples. Your entry can be anything, either an Excel chart or anything else created in Excel. Send screen shots or examples of your coolest use of graphics in Excel to challenge@MrExcel.com. Twice during the contest, I will post screen shots of the entries so far. The deadline for entries will be December 26th, with judging to follow. The winner will receive an autographed copy of JWalk's Excel Charts book.

Results

Here are the entries for this challenge. I asked a celebrity panel of judges (John Walkenbach, Mala Singh and myself) to vote for their top 3. After combining these votes, I am pleased to congratulate Phil Johnson and his animated warehouse locator. Phil wins an autographed copy of John's Excel Charts book.

Entry 1: 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."

Entry 2: 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!

Entry 3: 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

Entry 4: Interactive Graph without VBA by Vinh Nguyen

Vinh writes, I created this interactive graph without using any VBA coding whatsoever.

Entry 5: 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.

Entry 6: 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.

Entry 7: 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."

Entry 8: 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.

Entry 9: 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.

Entry 10: 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.

Entry 11: 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".

Entry 12: 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.

Entry 13: 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.