| Current Challenge Past Challenges |
 |
 |
| About Mr.Excel |
| Consulting Services |
| Learn About Excel |
| Challenge of the Month |
| Online Store |
| Search |
| Contact |
| Home |
 |
Nov/Dec 2006 Challenge - Innovative Excel Charts
|
||||||||||||
|
I am writing a book about charting in Excel.
Entry 1: Brian Connolly  Brian says: "This is the screen shot of a chart I developed for our Water/Sewer Department. It shows actual revenue to target to billed. I simply used a hi low close chart for this one. It may not be that innovative, but the public sector has a awful time explaining actions versus goals. This visual aid was a VAST improvement to what was here before I arrived." Entry 2: Greg Brauer  Greg says: I have a boss who likes ‘sexy' charts, meaning aesthetically pleasing. One thing he wanted was a thermometer chart to show progress toward a goal, Excel does not have a chart like this readily available so I played around with the stacked bar chart, the formulas in the source data and the color scheme to make boss happy. The fill effects highlight the targeted area that needs to be seen on the ‘Thermometer Chart.’ Entry 3: Joe Leslie  Often X-Y plots/charts would be easier to explain if areas could be coloured / shaded. This is a “cut to fill” chart used for earthworks. Normally all drawn by hand and when someone wants to change earthworks sequence you do a lot of swearing and then start again. The graphed line is only useful for colouring if the data points are closely spaced. I can then use the same X-Y data to colour in between the lines. In the sample attached this is visually easy for users to see that the same coloured excavated material is used in the adjacent fill areas of our road project and data labels used to define location and quantities. To demonstrate this on a simpler graph – eg a Normal Distribution curve, I have shown how this “colouring” is done. (See joe.zip) I have used column C & D to create the colour shading and the actual data plot is in column E so that these shaded areas appear at the back and don’t obscure the other information I may have on the graph. If equations are used to set the shading limits (as in attached example) then a simple change of the numbers in cells J8 and J10 can adjust the width of the shaded area. Note if the points are evenly spaced around the curve a better result is achieved with the colouring. Sheet "160points" was created using 160 points but they are not evenly spaced along the curve whereas sheet "300points" was created using 300 points and the colouring is more complete. Entry 4: Jerome Sullivan  Jerome adjusts the overlap function to get one series directly in front of the other. Entry 5: Dawn Horton  Here is a screenshot of my chart. The company I work for was merging into another company and I had to explain to employees the difference in their sick/vacation time but they could not digest the difference because they were going from a non sick time vacation plan to one that dictates how much sick time they were allowed. Once they saw this chart they were able to identify the difference immediately. Entry 6: Scott Ratliff  This chart is the result of a function I wrote that is part of a larger program that gives information of a production run. At the end of the run, each batch contains three stacks. These stacks contain 50 or so products. The charts below represent those products and the colors represent any deformities that may be in the products themselves. The chart is 3-D Stacked and each product is represented by a series and the deformity by the series color. It has been a very useful tool in comparing the location of deformities to one another. Entry 7: Daniel Feldman  Many users don’t know the value of a “combination” chart such as this. We track help desk calls monthly, and, we frequently create these charts. One of the problems with a “conventional” line chart for all the categories is that the line that has the “upper limit” (in this case, the category totals) dictates the Y-axis scale. When you include the trend for totals together with individual monthly scales, this is a common problem. Thus, you have this huge “gap” in the lines between the total and the category lines. Instead, I took the category totals, and defined just that series as a column chart, and I used a secondary axis. Additionally, I adjusted the scales on both Y axes so it was more readable and sensible. In viewing the chart, you can better see the relationships of all the category line trends, as well as understand their relationships to the monthly total by viewing the columns. Additionally, I have placed the values on the column totals so the height of the columns is a bit clearer. Entry 8: Brett Bernardo  OK, so it’s not a chart in the Excel sense, but it is a chart. These are graphical representations of numeric data. Specifically, these are to-scale bundles of steel decking in the configuration that they are to be loaded on a truck. Where I work, orders come in for lengths of deck anywhere from a few feet long to upwards of 50 feet long. I developed this program to allow those who are responsible for loading trucks to load things quickly. Building designs change at the last minute all the time. Larger jobs may have well over 20 trucks to load, and if the bundles are all of different lengths, it can take hours to determine safe and efficient loading configurations. These hours can be lost when the order changes at the last second or the customer requests that certain pieces arrive first instead of last. This program – and most specifically this “Graphics” tab turn those hours into minutes. Furthermore notes scratched on paper are now replaced by easy to read printouts that can convey exactly what needs done quickly and concisely. The program reads the deck bundle data, combines bundles if possible, then, using a sometimes recursive algorithm, determines a configuration for loading as many trucks as necessary. The innovative part comes in here. The pictures are not static – by dragging the names of the bundles (ex: A13 or A23) from one cell to another and clicking “redraw” the isometric views are updated instantly. Color coordination between the text and the picture is always maintained. Full bundle information can be obtained by right-clicking near the word-art bundle names. Lots of information is conveyed through these automatically generated diagrams…er…charts. Different colors mean different bundle conditions. Different shapes (notice that some look like wedges…scratch that, all of the bundles in this particular sample are wedges) indicate bundles with different lengths of deck. The positioning of the bundles on the truck is accurate. All this allows users to generate loading configurations in seconds and tweak those configurations to accommodate the myriad of exceptions that seem to come with nearly every order. Things can get tricky when trying to visualize things of different lengths and heights all fitting together – especially when a bundle is topped off with a short piece of deck – the bundle itself may be 30 feet long, but if its top piece is only 8 feet long, you cannot stack another 30 foot long bundle on top of it. This illustration provides instant and accurate feedback that is especially useful when making these complex changes. Entry 9: Brian Gaither  I have created a function that runs a loop a certain number of times depending on the percentage value passed to it. The function prints an asterisk each time the loop is executed to give the illusion of a bar chart. This is used because it is certain to be formatted correctly no matter how many rows of data are added, just copy the formula down. It’s also a quick way to see where trouble spots are during the selected period of time the report represents. To give you a little understanding of what the figures are, this represents the availability of a fleet of machines over a period of time. Thank you to everyone who submitted a chart! MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.
|
![[tally]](http://www.mrexcel.com/vote/images/blue.jpg){kind=small}
22