Learning Advanced Charting

1
Chapter 17

• Learning Advanced Charting

2
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.

3
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

4
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
  appears.
• Can also open by View ? Toolbars ? Chart
• Only lists the top-level elements in the chart.

5
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.

6
Modifying the Chart Area

• The chart area contains all other elements in the
  chart.
• 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.)

7
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.

8
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.

9
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.

10
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.

11
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.

12
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.

13
Modifying the Axes

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

14
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
  box.
• A selected data series has a small square on each
  element in the series.

15
Data Series

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

16
Adding a Data Series

• To Add a Data Series to a chart
• Activate the chart and select Chart ? Source
  Data.
• Click the Series tab in the Source Data dialog
  box.
• 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
  clipboard
• Activate the chart and choose Edit ? Paste
  Special.
• For Embedded charts only
• Select the data range to add and drag it onto the
  chart.

17
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
  box.
• 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.

18
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
  tab
• 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
  key.

19
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
  types.
• 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.

20
Missing Data

• Select the chart
• Choose Tools ? Options
• Click the Chart tab in the Options dialog box and
  select
• 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.

21
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.

22
Adding Error Bars

• You can add error bars to indicate plus or
  minus information to reflect uncertainty in the
  data.
• 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

23
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
  line.
• To add a trend line
• Select Chart ? Add Trendline
• Select the type of trend line you want
• Select the options you want.

24
Modifying 3-D Charts

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

25
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
  axis.
• 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.

26
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
  data.

27
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.

28
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.

29
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
  options.
• Add the chart to the custom chart type gallery
  (see slide 11).

30
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.