Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 788
Default Outlier data point for Chart in Excel

I have created a bar graph with 5 bars. One of the data points has a
disproportionately high value, and it is hard to view the other data points
in the chart because their value is so low.

If I fix the axis to a lower value, the data point label for the large value
will only show up on the graph if I center it. If I try to move the label,
it disappears.

This is hard to explain, but in other graphs I have seen similar outliers
represented as a bar with a zigzag near the end and a data label.

Is there a way to format a chart so it can properly represent the outlier,
without having to make the axis long enough to include the whole bar?
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Outlier data point for Chart in Excel

Yes, there is a way to format a chart in Excel to properly represent an outlier data point without having to make the axis long enough to include the whole bar. One way to do this is by using a combination chart, which allows you to display different types of data on the same chart.

Here are the steps to create a combination chart with a zigzag bar for the outlier data point:
  1. Select the data range for your chart, including the outlier data point.
  2. Click on the Insert tab in the ribbon and select the Combo chart option.
  3. In the Combo chart dialog box, select the Clustered Column chart type for the first data series (the one with the low values) and the Line chart type for the second data series (the one with the outlier value).
  4. Click on the OK button to create the chart.
  5. Now you should have a chart with two data series, one represented by columns and the other by a line. The line represents the outlier data point.
  6. To add the zigzag to the line, right-click on the line and select Format Data Series.
  7. In the Format Data Series pane, go to the Line Style tab and select the Dash type.
  8. In the Dash type options, select the pattern that looks like a zigzag.
  9. Adjust the other formatting options as desired, such as line color and thickness.
  10. Finally, adjust the axis scaling as needed to make the chart more readable. You can set a fixed minimum and maximum value for the axis, or use automatic scaling with a maximum value that is higher than the outlier value.

By using a combination chart with a zigzag line for the outlier data point, you can create a chart that properly represents all the data without sacrificing readability.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 66
Default Outlier data point for Chart in Excel

One option is to use a log scale.

Another is to move the outlier to the secondary axis.

Yet another is to simulate a break in the axis. See
Broken axis
http://www.tushar-mehta.com/excel/ne...axis/tutorial/

or Jon Peltier's http://peltiertech.com/Excel/Charts/BrokenYAxis.html or
Andy Pope's http://andypope.info/charts/brokencolumn.htm

--
Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu


"Chris" wrote:

I have created a bar graph with 5 bars. One of the data points has a
disproportionately high value, and it is hard to view the other data points
in the chart because their value is so low.

If I fix the axis to a lower value, the data point label for the large value
will only show up on the graph if I center it. If I try to move the label,
it disappears.

This is hard to explain, but in other graphs I have seen similar outliers
represented as a bar with a zigzag near the end and a data label.

Is there a way to format a chart so it can properly represent the outlier,
without having to make the axis long enough to include the whole bar?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Moving a line chart data point revises data table value in Excel ' Ed Smith Charts and Charting in Excel 2 November 16th 12 02:03 PM
Formatting Data point in Bar Chart Athena[_2_] Charts and Charting in Excel 1 January 30th 09 06:28 PM
chart formatted data point Pippa M Charts and Charting in Excel 1 September 4th 08 01:25 PM
How do I add a comment to a data point in a chart? erin_taylor Charts and Charting in Excel 2 June 14th 06 08:52 PM
How to remove a data point in a chart? Vicky Charts and Charting in Excel 6 July 15th 05 05:27 PM


All times are GMT +1. The time now is 07:57 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"