|
Excel Books
| |
Books that I own and use while developing in Excel
|
|
|
Chart Formatting Techniques and Tricks
Excel Dashboards by ExcelUser.com - Instructional eBook and Ready-to-Use Workbooks.
|
Set up conditional formatting for your chart. Change color and marker style depending on the value of a point.
Top of Page |
|
Set up conditional formatting for your column chart. Color a column blue between 0 and 2, green between 2 and 4, yellow between 4 and 6, orange between 6 and 8, and red above 8.
Top of Page |
|
Ever want to apply special formatting to just a certain point? This example shows you how to highlight the minimum and maximum values of a series by using a different marker for each, and data labels. We'll accomplish this with two extra series in the chart, one for minimum and one for maximum.
Top of Page
|
|
People often want to use an arrow or other symbol to indicate a point in a chart. If you draw an arrow, or any AutoShape, in a chart, it is not in any way tied to the data or to the chart axes, so it will not move to keep up with a point as the axes change or the chart resizes. Even if the chart does not change, an AutoShape is not guaranteed to be in exactly the same position the next time the file is opened. This technique shows how to attach an indicator (arrow) to a point by creating custom markers for the Min and Max series.
Top of Page
|
|
A chart could be made more informative by selectively shading regions of the background with different colors. For example, a run chart may show colored bands to indicate standard deviations of a process value from the mean. Excel only provides the ability to add one color to the background, but multiple colors can be added by creating a combination chart with added area chart series colored as desired. This tutorial shows how to construct such a chart.
Top of Page |
|
I was recently asked, "I have an XY scatter chart that gets divided into 4 quadrants and each quadrant needs a different color. Any ideas?" I love a good challenge, so I came up with the procedure in this page.
Top of Page
|
|
This page shows how to put a vertical band on a chart, highlighting a certain region. The technique can be used, for example, to highlight a certain time period in the background of a gantt chart.
Top of Page
|
|
Did you ever want to fill the area under an XY series? You tried an Area chart, but that didn't work; the X axis didn't scale properly, and you couldn't get the lines and markers you wanted. You can fill the area under an XY series by using a combination XY - Area chart, plus a little creative treatment of the X values of the Area series and of the scaling of the secondary X axis.
Top of Page |
|
Did you ever want to fill the area between two XY series? You tried Stacked Area charts, but that didn't work; the X axis didn't scale properly, and you couldn't get the lines and markers you wanted. You can fill the area between XY series by using a combination XY - Stacked Area chart, plus a little creative treatment of the X values of the Area series and of the scaling of the secondary X axis.
Top of Page |
|
One of the most commonly used chart types is the line chart. People often ask, "How can I fill the region between two lines with a particular color?" This article describes how to create a line chart with a shaded region between the lines.
Top of Page
|
|
Excel charts offer a wide variety of formats, but you can use Excel's drawing tools to enable even more formatting choices. It's possible to draw shapes on the chart to produce these formats, using the polygon drawing tool. This allows more line formats, by enabling more choices of line thickness and by making it easier to read dashed lines. More fill possibilities are made possible than merely filling below a series, as in an area chart: the fill can go below or to the side of the series, and in fact, an enclosed region in the chart can be filled. The fill can be made transparent too, allowing gridlines to show through the shape.
 
 
This article presents VBA procedures that automate the polygon drawing tool, and gives hints about the kinds of formatting which may be achieved. A sample procedure has been recently added to show how to use this technique for charts that have multiple series.
Top of Page
|
|
Excel charts offer a wide variety of formats, but you can use Excel's drawing tools to enable even more formatting choices. Line and Fill Effects in Excel Charts Using VBA shows how to draw shapes on an XY chart to produce these formats, using the polygon drawing tool. This allows more line formats, by enabling more choices of line thickness and by making it easier to read dashed lines. More fill possibilities are made possible, including transparent fills, allowing gridlines and series to show through the shape.
This article extends VBA polygon drawing procedures to radar charts.
Top of Page
|
|
In Microsoft Excel's bubble charts, bubble sizes are fixed according to the largest bubble in the chart. This is a problem when comparing multiple charts that have dissimilar bubble size data. This article shows an easy way to link the bubble size scales of charts with different bubble size data.
Top of Page
|
|
Suppose you want to chart the relative frequency of numbers in a list. Suppose further that instead of a bland column chart, you want to put an X in the histogram for every occurrence of a value. You can do this with a scatter chart, using a procedure offered by Excel MVP Debra Dalgleish in the Microsoft Charting news group.
Top of Page
|
|
In a column or bar chart in Excel, the columns or bars are generally all the same width. Sometimes, it helps to emphasize some data by changing the width of some of the columns. This example makes use of the Custom Series Formatting technique described elsewhere on this site.
Top of Page |
|
The pages listed here show how to make a more informative column chart, where the height of the columns indicates one value, and the width indicates another.
Top of Page |
|
How do you make a combination Clustered-Stacked chart? This page has links to several explanations.
Top of Page |
|
Excel doesn't offer a built-in custom Columns On Two Axes chart type. Trying to construct your own from a regular column chart is unsatisfactory. In a column chart, the columns on a given axis are centered over each category. Hence the single column on the secondary axis has obscured the center column on the primary axis. You can get around this problem if you use dummy series in your chart.
Top of Page
|
|
If you create a clustered column chart that has some blank values, the blanks will result in gaps between columns in the chart. Excel doesn't allow you to squeeze the existing columns together, but you can rearrange your data and use dummy series to fix the category axis.
Top of Page
|
|
Some kinds of data look very nice and are easily understood in the form of a "floating" column or bar, in which the column floats in the chart, spanning a region from a minimum value to a maximum value.
Top of Page
|
|
A tornado chart is a bar chart commonly used to compare characteristics of two populations. A common use is to show the distribution of males and females among different age groups in the general population. Males would be shown in bars stretching to the left of the central line; females reaching to the right. This page shows how to make simple and more elaborate tornado charts.
Top of Page
|
|
Waterfall charts are a special type of Floating Column Charts. A typical waterfall chart shows how an initial value is increased and decreased by a series of intermediate values, leading to a final value. An invisible column keeps the increases and decreases linked to the heights of the previous columns.
This page shows how to arrange your data and create a waterfall chart. At the end of the page you'll find a link to a utility for creating waterfall charts easily. The utility has recently been updated to provide optional labels above columns in the chart, and to correct problems experienced by some non-US users.
Top of Page
|
|
This page describes construction of a fancy Waterfall Chart. This chart shows how three classes of revenue increased from 2002 to 2003 because of Volume, Price, and other factors.
Top of Page
|
|
Waterfall charts are a graphical approach to show how an initial value is increased and decreased by intermediate values, to reach its final value. When the intermediate value drops below the X axis, the stacked column chart approach no longer accurately displays these values. This page shows how to combine the Waterfall Charts technique with the Stacked Column Charts that Cross the X Axis approach to correct the chart's appearance. The page has been updated to show how to add labels above the bars in the chart.
Top of Page
|
|
I was perusing the Sunday paper recently, and came across a chart which summarized the past month's temperatures. It's an informative chart, which compares each day's minimum and maximum temperature with the normal range and the historical minimum and maximum temperatures for each date. I've seen similar charts many times, including this same design in the local paper, but this time the chart called out to me, and I had to build it myself. This page has links to three versions of this chart.
Top of Page
|
|
Box and Whisker charts are commonly used in the display of statistical analyses. Unfortunately, Microsoft Excel does not have a built in Box and Whisker chart type. You can create your own custom Box and Whisker charts, using stacked bar or column charts and error bars, in combination with line or XY scatter chart series to show additional data. The procedures in these tutorials have been updated to show how to add additional series (means of other populations, perhaps, or sets of target values). This page also links to a utility which can be used to generate Box and Whisker charts directly from population data. The Box Plot utility has recently been upgraded to provide more professional output, to correct treatment of outliers in horizontally oriented charts, to run tenfold faster, and to fix a few small bugs. The utility was previously updated to provide additional chart styles, and to correct problems experienced by some non-US users.
I have developed a Box and Whisker Chart Utility that allows the user to construct box and whisker charts directly from the raw data. The utility builds the intermediate summary table and then creates and formats the chart from this table. The utility is designed to work in Excel versions 2000 through 2007. There is a professional version with an extensive feature list, and a free demo version which makes simple box plots.
Top of Page
|
|
Hate the way Excel mucks up your chart font sizes when you resize the chart?
So do I, and here's how I counteract the default font scaling.
Top of Page |
|
A common question people ask is "How do I format my chart so its gridlines make a square pattern?" Excel has no setting that forces equally spaced horizontal and vertical gridlines or horizontal and vertical axis ticks, but you can achieve this effect using VBA. Below is a chart after making the appropriate scale adjustment.
Top of Page
|
|
|
Waterfall Chart
Box and Whisker
