|
Related links:
| |
Excel Charting Tips:
Bar Chart Tips
Chart tip #1: Excel
2007 vs. Excel 2003
Chart tip
#2: Pie Chart tips
Chart tip #3: When to
use Cone, Radar, and Pyramid Charts
Chart tip #4: Bar Chart Tips
Chart tip #5: Times Series Charts
Chart tip #6: Scatterplots
Chart tip #7: Boxplots
Chart tip #8: Where to go for more help
Chart tip #9: Printing and copying charts
Chart tip #10: Downloading different data formats
[Note: click on images to open Excel files]
Chart tips #4:Bar
Charts
Rotated bars
in cells:
 |
The problem:
Often
with rotated bar charts, excel will not display all the category labels
unless you make the fonts really small or stretch the chart out beyond
height of the paper you would print it on. This more of a problem
with Excel 2003 than 2007.
The solution:
Construct the bars in the excel spreadsheet.
[click on any examples to link to example spreadsheet]
The key to this is that
the a string of the letters: ggggg, when formatted with the
Webding font, appear as a bar,
for example:
- gggggggggggggg
<== Normal font
- ggggggggggggg
<=== Webding font
The second key to
constructing the bars is to get the right numbers of the letter "g" into
the appropriate cells. This can be done with the REPT function.
In the example on the
left, the REPT function is shown in cell C2. The bars are scaled by
dividing the repeat value by seven (B2/7). Just copy the formula
shown in cell C2 down and paste it into the cells below. Other
adjustments in scaling can be done by changing the font size.
To copy the chart into
another document, you should first hide the gridlines. This can be
done (in 2007) with the function:
View | Show\Hide | uncheck gridlines
or by using white cell borders.
|
 |
Which gives us the final
chart:
Note that it
is possible to label the bars on the right hand side, but
this requires that each cell be formatted. To do this,
copy the cells with the formula and paste them to the same
cells with the Paste special | Values function.
Add the additional text at the end of the cells
and format it with a regular font.
gggggggggggggggg (88)
gggggggggggggggg (88)
|
Note: The same principle applies
to unrotated, or column, bar charts, just change the text alignment as in the
example below. Note also, one thing you can do with these charts that you
can't with a regular bar chart is to change the font for one of the labels.
Another variation: (using the
formula =REPT("-",B2/3)&"X"&" "&B2
X-axis-
Two levels:
It is possible to have two
levels of X-axis category labels on a bar chart. To do this, just
expand the range for the X-axis labels. In the chart below,
this was done by highlighting the data range shown below the chart
before selecting:
Insert | Chart.
Overlapping bars: Placing a smaller bar inside a large one.
The chart on the left was
created by using a secondary Y-axis for the "inside" bar.
On the secondary axis no markers are displayed and the axis scaled
the same as the primary axis.
To adjust the size of the
inside bar, adjust the gap between the bars.
[Note this is a revision of
figure 5.11] Thanks to Jon Peltier, Microsoft Excel MVP
for showing me this.
An alternative:
The chart below done with Excel 2007,
taking advantage of the thicker lines on the bar borders, it can be
done, but not as well, in 2003.
- The two series are
plotted with 100% overlap and a relatively large gap between the
bars.
- Then add a thick border
around the data series that defines the larger bar (5.5 pt. in
the example below).
- Color the border the
same as the data series.
- The smaller bar should
have no border.
- It may be necessary to
reorder the two series using Design | Select data and the
up or down arrow,
if the smaller bar is hidden behind the large bar.
- select join-type
"miter" for the border to make the bar flat at the end.
- Note, however, that the
larger border actually makes the bar taller (wider), and you may
have to adjust the data by subtracting the values of the added
bar height.
Note this is an alternative to the scatterplot used in
figure 5.9
08/03/2008
 |
