Learning Advanced Charting - PowerPoint PPT Presentation


PPT – Learning Advanced Charting PowerPoint presentation | free to view - id: 6e5b4e-OTMzM


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation

Learning Advanced Charting


Chapter 17 Learning Advanced Charting – PowerPoint PPT presentation

Number of Views:13
Avg rating:3.0/5.0
Slides: 31
Provided by: viscalc


Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: Learning Advanced Charting

Chapter 17
  • Learning Advanced Charting

Understanding Chart Customization
  • Customization includes
  • Changing the appearance of a chart
  • Colors
  • Line widths
  • Chart background, etc.
  • Adding new elements to a chart
  • New data series
  • Data table
  • A trend line
  • Error bars, etc.

Selecting Chart Elements
  • Use the mouse
  • Position the pointer on the area and click it.
  • Make sure you have the correct element selected
    by checking the Name box entry to the left of the
    formula bar.
  • Some chart elements consist of multiple items
    (data series, etc.) To select a particular item
    in a multi-item element
  • Click once to select the multiple element
  • Click twice to select an item in the element

Selecting Chart Elements
  • Use the keyboard
  • When a chart is active, use the arrow keys to
    cycle among the charts elements.
  • Use the Name box to determine when the proper
    item is selected.
  • Use the Chart Toolbar
  • When you select a chart, the chart toolbar
  • Can also open by View ? Toolbars ? Chart
  • Only lists the top-level elements in the chart.

Using the Format Dialog Box
  • Once a chart element is selected, you can set its
    options using the tabbed Format Dialog box.
  • Select the Format ? selected element command
  • Double-click a chart element.
  • Select the chart element and press Ctrll
  • Right-click on a chart element and choose the
    Format command from the shortcut menu.

Modifying the Chart Area
  • The chart area contains all other elements in the
  • Patterns changes the chart areas color and
    patterns (fill effects) or add a border.
  • Font changes the properties of all fonts used in
    the chart.
  • Properties allows you to specify how the chart
    is moved or sized with respect to the underlying
    cells. (for embedded charts only.)

Modifying the Plot Area
  • The plot area contains the actual chart.
  • Patterns changes the plot areas color and
    patterns (fill effects) or modify its borders.
  • Can reposition the plot area by dragging on one
    of its borders.
  • Can resize the plot area by dragging on one of
    the corner handles.
  • The plot area is resized automatically when other
    elements of the chart are changed.

Chart Titles
  • A chart can have as many as 5 titles
  • Chart title
  • Category (X) axis title
  • Value (Y) axis title
  • Secondary category (X) axis title
  • Secondary value (Y) axis title
  • A chart titles pattern, font alignment
    properties can be formatted.
  • Can add free-floating text to a chart using a
    text box.

Chart Legend
  • A charts legend consists of text and keys that
    help identify the data series.
  • A Key is a small graphic that corresponds to the
    charts series.
  • To add a legend to your chart
  • Select Chart ? Chart Options
  • Click on the Legend tab in the Chart Options
    dialog box
  • Place a check mark in the Show Legend check box.
  • Determine the legend placement using the
    Placement option buttons.
  • To delete a legend
  • Select the legend and press the Delete key.
  • To move a legend
  • Click on one of its borders and drag it to the
    desired position.

Copying Chart Formatting
  • Copy the formatting
  • Select your original chart
  • Choose Edit ? Copy
  • Select your new chart
  • Choose Edit ? Paste Special
  • Select Formats from the Paste Special dialog box
    (this also changes the title!)
  • Copy the chart and change the data sources
  • Press Ctrl while you select the original chart
    and drag.
  • Use the Chart ? Source Data dialog box to specify
    the data for the new chart.

Copying Chart Formatting
  • Create a user-defined chart type
  • Select your chart
  • Choose Chart ? Chart Type
  • Click the Custom Types tab
  • Select the User-defined option
  • Click the Add button
  • Provide a name and description
  • When you create your next chart, use this custom
    chart type.

Chart Gridlines
  • Gridlines help determine what the chart series
    represents numerically by extending the tick
    marks on the axes.
  • To add or remove gridlines
  • Choose Chart ? Chart Options
  • Select the Gridlines tab
  • Major units display a label
  • Minor units are located between labels.
  • A 3-D chart has three sets of gridlines.
  • Can adjust the patterns and scale of the
    gridlines using the Format Gridlines dialog box.

Modifying the Axes
  • Depending on the chart type, the number of axes
    can vary.
  • Can modify an axis using its Format Axis dialog
  • Patterns (pg. 382)
  • Scale (pgs. 382-384)
  • Font
  • Number
  • Alignment
  • If your data is time-based, Excel automatically
    creates a time-series chart.

Data Series
  • Every chart consists of one or more data series.
  • These are the lines, columns, pie slices, etc. on
    the chart.
  • Can customize a charts data series by using the
    Format Data Series dialog box.
  • Select the data series (tip use the chart
    objects tool)
  • Make sure the entire series is selected (not just
    one data point) by noting the entry in the Name
  • A selected data series has a small square on each
    element in the series.

Data Series
  • Can modify a data series
  • Pattern
  • Axis
  • Y Error Bars
  • Data Labels
  • Series Order
  • Options
  • Shape
  • X Error Bars

Adding a Data Series
  • To Add a Data Series to a chart
  • Activate the chart and select Chart ? Source
  • Click the Series tab in the Source Data dialog
  • Click the Add button
  • Specify the data range in the Values box.
  • Use the clipboard
  • Select the range to add and copy it to the
  • Activate the chart and choose Edit ? Paste
  • For Embedded charts only
  • Select the data range to add and drag it onto the

Modifying a Data Series
  • Option 1
  • Select the data range.
  • Drag the small white dot in the lower-right
    corner of the range to extend or contract the
    data range.
  • Option 2
  • Select Chart ? Source Data.
  • Click the Series tab in the Source Data dialog
  • Select the series from the Series list box
  • Adjust the range in the Values box.
  • Option 3
  • Select the data range.
  • Edit the series formula in the formula bar (uses
    absolute cell references)
  • Option 4
  • Use named ranges to automatically adjust the
    chart based on the range definition.

Deleting a Data Series
  • Use the Series tab of the Source Data dialog box.
  • Select the chart
  • Choose Chart, Source Data and select the Series
  • Select the series name in the Series list and
    click the Remove button.
  • Alternatively, remove a data series by selecting
    the charted data series and pressing the Delete

Displaying Data Labels
  • Specify the data labels in the Data Labels tab of
    the Chart Options dialog box.
  • Not all options are available for all chart
  • Data labels are linked to the worksheet
  • If your data changes, the labels also change.
  • To enter your own text, select the label and
    enter the new text (or cell reference) in the
    Formula bar.

Missing Data
  • Select the chart
  • Choose Tools ? Options
  • Click the Chart tab in the Options dialog box and
  • Not Plotted (leave gaps)
  • Zero (treat missing data as a zero value)
  • Interpolated missing data is calculated based on
    the values on either side of the missing points.

Hiding Data
  • Excel doesnt plot data that is in a hidden row
    or column.
  • Can override this behavior by
  • Selecting Tools ? Options
  • Click the Chart tab in the Options dialog box
  • Remove the check mark from the check box labeled
    Plot Visible Cells Only.

Adding Error Bars
  • You can add error bars to indicate plus or
    minus information to reflect uncertainty in the
  • Only used for certain chart types area, bar,
    column, line XY
  • Types of error bar types available
  • Fixed value
  • Percentage
  • Standard Deviation(s)
  • Standard Error
  • Custom

Adding a Trend Line
  • Used for data that is plotted over time.
  • Points out general trends in your data.
  • Allows you to forecast future data in some cases.
  • A single series can have more than one trend
  • To add a trend line
  • Select Chart ? Add Trendline
  • Select the type of trend line you want
  • Select the options you want.

Modifying 3-D Charts
  • 3-D charts have additional options to customize
  • Floors
  • Walls
  • Axis
  • Perspective
  • Rotation

Formatting a Surface Chart
  • Cannot select any of the series in the chart.
  • Colors are based on the values
  • The number of colored bands used in the chart
    depends on the major unit setting for the value
  • To adjust the colors
  • Make sure the chart displays a legend.
  • Select the legend and double click the color key
    you want to change.
  • Use the Patterns tab on the Format Legend Key
    dialog box to change the color.

Creating and Printing a Combination Chart
  • A combination chart includes two or more chart
    types, such as showing one data series as a
    column and another as a line.
  • Must have at least two data series.
  • Use if the values in the data series vary widely
    or if you want to emphasize differences in the

Creating a Combination Chart
  • Change one or more of the data series to a
    different chart type.
  • Select the data series
  • Choose Chart ? Chart Type
  • In the Chart Type dialog box, select the chart
    type you want to apply to the selected series.
  • Use a secondary axes for the second data series
    if the data have drastically different scales.
  • Double-click the data series in the chart
  • In the Format Data Series dialog box, click the
    Axis tab
  • Choose the Secondary axis option.

Displaying a Data Table
  • Displays the charts data in tabular form
    directly on the chart.
  • Choose Chart ? Chart Options
  • Select the Data Table tab in the Chart Options
    dialog box
  • Place a check mark next to the option labeled
    Show Data Table.
  • Double-click the data table to access the Format
    Data Table dialog box to format font used in the
    data table.

Creating Custom Chart Types
  • Also known as user-defined chart types.
  • Generally are standard charts that have been
    formatted or customized in various ways.
  • To create your own custom chart type
  • Create a chart
  • Customize it by applying formatting and adding
  • Add the chart to the custom chart type gallery
    (see slide 11).

Chart Making Tips
  • Your book (and CD) offer tips for creating the
    following types of custom charts
  • picture chart
  • thermometer chart
  • gauge chart
  • comparative histogram
  • Gantt chart
  • Automatically updating chart
  • Plotting specific mathematical functions
  • FAQs see page 406-417.
About PowerShow.com