Catégorie : Excel VBA Course

Continuing to enhance the example related to the activity of computer clubs, let us improve the automatic chart construction. Now, the user can add or delete any number of months in the overall report table located on the Vedomost worksheet. After changing the number of months, it is sufficient to click the club list, and the chart will automatically be rebuilt.

To implement this task, modify the Click event procedure of the Club list as shown:

Controlling chart type and legend. Vedomost worksheet module

Private Sub Club_Click()
    Dim r As Integer
    ActiveSheet.ChartObjects(1).Activate
    r = Club.ListIndex + 1
    Dim rgn As Range
    Dim rgnTitle As Range
    Set rgn = Range("A3").CurrentRegion
    Set rgnTitle = rgn.Rows(1)
    Set rgnTitle = rgnTitle.Offset(0, 1)
    Set rgnTitle = rgnTitle.Resize(ColumnSize:=rgnTitle.Columns.Count - 2)
    Set rgn = rgn.Offset(1, 1)
    Set rgn = rgn.Resize(rgn.Rows.Count - 2, rgn.Columns.Count - 2)
    With ActiveChart
        .SetSourceData Source:=rgn.Rows(r), PlotBy:=xlRows
        .SeriesCollection(1).XValues = rgnTitle
    End With
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = Club.Text
    End With
End Sub

Now let’s modify the previous example and add another list on the worksheet so that the user can control both the chart type and the legend display.

• On the Vedomost worksheet, create a second list. In the Properties window, set its Name property to DType.
• In the worksheet module Vedomost, additionally enter the procedure FillDType. In the Workbook_Open event procedure, add a call to FillDType as well.

Controlling the chart type and legend. ThisWorkbook module

Private Sub Workbook_Open()
    DeleteCharts
    ChartBuilder
    FilllstCategory
    FillDType
End Sub

Private Sub FillDType()
    Dim tb(6, 1) As Variant
    tb(0, 0) = "Column":     tb(0, 1) = xlColumnClustered
    tb(1, 0) = "Line":       tb(1, 1) = xlLine
    tb(2, 0) = "Pie":        tb(2, 1) = xlPie
    tb(3, 0) = "Doughnut":   tb(3, 1) = xlDoughnut
    tb(4, 0) = "Area":       tb(4, 1) = xlArea
    tb(5, 0) = "Bar":        tb(5, 1) = xlBarClustered
    tb(6, 0) = "Cone":       tb(6, 1) = xlConeColStacked
    With Worksheets("Vedomost").DType
        .ColumnCount = 2
        .TextColumn = 2
        .ColumnWidths = "70;0"
        .List = tb
        .ListIndex = 0
    End With
End Sub

• In the Vedomost worksheet module, enter the following event-handling procedure for the Click event of the list. This will rebuild the chart whenever a chart type is selected (Listing 6.4).

Selecting the chart type. Vedomost worksheet module

Private Sub DType_Click()
    ActiveSheet.ChartObjects(1).Activate
    ActiveChart.ChartType = DType.Text
    If DType.Text = xlPie Or DType.Text = xlDoughnut Then
        ActiveChart.HasLegend = True
    Else
        ActiveChart.HasLegend = False
    End If
End Sub

Notes

• Two-column list design. Charts have both names (e.g., “Column”, “Pie”) and constants (e.g., xlColumnClustered, xlPie) that define chart types. The list should display only the chart type names, but the result of the selection must be the constant. To solve this, the code creates a two-column list:
  • the first column contains the names of the chart types,
  • the second column contains the constants defining the chart types.
    Since we don’t need to display the second column, its width is set to zero. However, because the list’s TextColumn property is set to 2, the selected item’s Text property returns the constant (second column), not the name.
• Legend necessity. Depending on the chart type, the legend may be necessary or redundant. For example:
  • For a column chart, the legend is unnecessary in the built application because the chart already includes clear labels.
  • For pie or doughnut charts, the legend is indispensable.
    Therefore, in the DType_Click procedure, the legend is either shown or hidden depending on the selected chart type.

Let’s consider a report sheet summarizing the performance of a network of computer clubs, placed on the worksheet Vedomost in an Excel workbook. When the workbook opens, there should be a chart next to the data that provides a visual representation of the performance dynamics of a specific club. This must be achieved by allowing the user to select a particular club from a list of clubs. Moreover, selecting a club from the list should change the data range on which the chart is based and trigger the chart to update accordingly.

To implement this project, perform the following steps.

• Assign, for example, the name Vedomost to the first worksheet.
• On this worksheet, prepare a tabular report summarizing the performance results of the computer clubs.
• Create a list on the worksheet and, using the Properties window, set its Name property to Club.
• In the ThisWorkbook module, enter the required code: when the workbook opens, the Workbook_Open procedure runs, which deletes all charts from the Vedomost worksheet, builds a 3-D column chart based on the performance results of the “Altair” computer club, and positions it so that it occupies the range G7:P26. This ensures that the chart does not overlap the data table. The procedure also populates the list based on the header row of the data table and selects the first item in this list.
• In the code module of the Vedomost worksheet, enter an event procedure to handle the list’s Click event so that when a club is selected from the list, the chart is rebuilt.

Now let’s look at an example of creating a chart with VBA. On a worksheet of the Excel workbook there are four buttons: Create Chart, Delete Chart, Add Data Labels, Delete Data Labels. When you click Create Chart, a chart is created on the active worksheet, and its title will match the contents of cell B1.

The Delete Chart button removes the chart. The remaining two buttons allow you to add or remove a series of data labels on the created chart, respectively.

Place four CommandButton controls on the worksheet. Note that on the Developer tab of the ribbon, in the Controls group, the Design Mode button is active.

For the Caption property of the added buttons (select a button and use the Properties command in the Controls group on the Developer tab), enter the following values respectively: Create Chart, Delete Chart, Add Data Labels, and Delete Data Labels. If you wish, also change the Font property.

Successively click the added buttons and add program code to the Sheet1 module according to:

Building a chart. Sheet1 module

Private Sub CommandButton1_Click()
    ChartCreate
End Sub

Private Sub CommandButton2_Click()
    ChartDelete
End Sub

Private Sub CommandButton3_Click()
    DataLabAdd
End Sub

Private Sub CommandButton4_Click()
    DataLabDelete
End Sub

Thus, clicking the corresponding buttons should run the following procedures: ChartCreate — add a chart to the worksheet; ChartDelete — remove the chart from the worksheet; DataLabAdd — add data labels to the chart; DataLabDelete — remove data labels from the chart.

To implement these procedures, add a standard module in the VBA editor (Insert | Module) and enter the relevant code.

Note that the ChartCreate procedure for adding a chart is implemented as follows. A new chart is created with the Add method. The ChartType property sets the chart type. The SetSourceData method provides a reference to the range whose values are plotted on the value (Y) axis. In this case, it is the range B2:B12 of the active worksheet. The SeriesCollection method sets a reference to the range whose values are plotted on the category (X) axis. In our case, this is the range A2:A12 of the active worksheet. Then the Location method specifies the chart location; here it will be embedded on the worksheet with the specified name, which matches the contents of cell B1. After that, the chart’s elements are defined. Using the ChartTitle property and the Axes method, we set the title (which matches the worksheet name) and the axis titles (note that the method .Axes(xlSeries).Delete removes data labels for the second axis located at the base of the chart). Then HasLegend removes the legend, after which properties are set to format the walls, floor, data series, and the plot area. The Top, Left, Width, and Height properties position the chart at a specified place on the worksheet. Thus, this procedure allows you to build a chart on any worksheet.

Deleting the chart is done with ChartObjects.Delete.

The DataLabAdd procedure for adding data labels to the chart is based on the ApplyDataLabels method for SeriesCollection(1). In turn, the DataLabDelete procedure sets HasDataLabels = False to remove data labels.

Building a chart. Standard module

' Procedure for building a chart
Sub ChartCreate()
    Dim rx As Range
    Dim ry As Range
    Dim nameX As String
    Dim nameY As String
    Dim title As String
    Dim nameSh As String
    nameX = "Volume"
    nameY = "Year"
    nameSh = ActiveSheet.Name
    title = Sheets(nameSh).Range("B1")
    Set ry = Sheets(nameSh).Range("B2:B12")
    Set rx = Sheets(nameSh).Range("A2:A12")
    ' Add a chart
    Charts.Add
    ActiveChart.ChartType = xlCylinderCol
    ActiveChart.SetSourceData Source:=ry, PlotBy:=xlColumns
    ActiveChart.SeriesCollection(1).XValues = _
        "=" & rx.Address(ReferenceStyle:=xlR1C1, external:=True)
    ActiveChart.Location Where:=xlLocationAsObject, Name:=nameSh
    ' Define chart elements
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = title
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = nameX
        .Axes(xlValue,   xlPrimary).HasTitle = True
        .Axes(xlValue,   xlPrimary).AxisTitle.Characters.Text = nameY
        .Axes(xlSeries).Delete
    End With
    ActiveChart.HasLegend = False
    ' Format the back wall
    With ActiveChart.BackWall.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorBackground1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = -0.150000006
        .Transparency = 0
        .Solid
    End With
    ' Format the side walls

    With ActiveChart.Walls.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorBackground1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = -0.050000007
        .Transparency = 0
        .Solid
    End With
    ' Format the floor
    With ActiveChart.Floor.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorBackground1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = -0.5
        .Transparency = 0
        .Solid
    End With
    ' Format the data series (3-D bevel)
    With ActiveChart.SeriesCollection(1).Format.ThreeD
        .BevelTopType = msoBevelCoolSlant
        .BevelTopInset = 13
        .BevelTopDepth = 6
    End With
    ' Format the plot area (shape fill)
    With Worksheets(nameSh).Shapes("Диаграмма 1").Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent1
        .ForeColor.TintAndShade = 0.3399999738
        .ForeColor.Brightness = 0
        .BackColor.ObjectThemeColor = msoThemeColorAccent1
        .BackColor.TintAndShade = 0.7649999857
        .BackColor.Brightness = 0
        .TwoColorGradient msoGradientHorizontal, 1
    End With
    ' Position the chart on the worksheet
    With Worksheets(nameSh).ChartObjects(1)
        .Top = Range("G5").Top
        .Left = Range("G5").Left
        .Width = Range("G1:R34").Width
        .Height = Range("C1:R34").Height
    End With
End Sub

' Procedure for deleting the chart
Sub ChartDelete()
    ActiveSheet.ChartObjects.Delete
End Sub

' Procedure for adding data labels to the chart
Sub DataLabAdd()
    Dim Rng As Range
    Dim Ct As Chart
    Dim i As Integer, K As Integer
    ' Identify the chart
    Set Ct = ActiveSheet.ChartObjects(1).Chart
    ' Prompt for the range to use as data labels
    On Error Resume Next
    Set Rng = Application.InputBox( _
        prompt:="Enter the range for the series' data labels", Type:=8)
    If Rng Is Nothing Then Exit Sub
    On Error GoTo 0
    ' Add data labels
    Ct.SeriesCollection(1).ApplyDataLabels _
        Type:=xlDataLabelsShowValue, AutoText:=True, LegendKey:=False
    ' Identify points and assign labels
    K = Ct.SeriesCollection(1).Points.Count
    For i = 1 To K
        Ct.SeriesCollection(1).Points(i).DataLabel.Text = _
            "=" & "'" & Rng.Parent.Name & "'!" & _
            Rng(i).Address(ReferenceStyle:=xlR1C1)
    Next i
End Sub

' Procedure for deleting data labels from the chart
Sub DataLabDelete()
    Dim Ct As Chart
    Set Ct = ActiveSheet.ChartObjects(1).Chart
    Ct.SeriesCollection(1).HasDataLabels = False
End Sub

In MS Excel, you can create different chart types and format them appropriately. From the VBA point of view, the workbook’s Sheets collection includes two families of sheets: Worksheets (worksheets) and Charts (chart sheets). The Charts family contains charts created on chart sheets. This family does not include charts embedded directly on worksheets. Such charts belong to the ChartObjects family. Thus, a ChartObject is embedded in a Worksheet, whereas a Chart is embedded in a Workbook.

The Workbook and Application objects share the ActiveChart property, which returns the active chart in the workbook, regardless of which family it belongs to. The Chart object has a number of child objects listed in Table.

Table. Objects subordinate to the Chart object

Object	Description
ChartArea	The area in which the chart is drawn
PlotArea	The chart plotting area
Floor	The horizontal plane (floor) of a 3-D chart
Walls (BackWall, Walls)	The vertical planes (walls) of a 3-D chart
Corners	The corners of a 3-D chart
PageSetup	Page setup parameters
ChartTitle	The chart title
SeriesCollection	The range of data plotted on the value (y) axis
Trendlines	Trendline(s)
Axis	Chart axes
AxisTitle	Axis titles
DisplayUnitLabel	Axis display unit label
Gridlines	Gridlines
TickLabels	Tick labels on the axes
DataTable	The chart’s data table
Legend	The legend
Shapes	The drawing shapes within the chart
SeriesCollection	Data series
DataLabels	Data labels
Points	Data points

If a chart is located on a worksheet, then the object hierarchy—for example, to address the chart title—can be represented as:

Application

  Workbook

    Worksheet

      ChartObject

        Chart

          ChartTitle

For charts that are on chart sheets, the object hierarchy is slightly different:

Application

  Workbook

    Chart

      ChartTitle

Adding a new element to the ChartObjects and Charts families

The ChartObjects and Charts families have the methods Add (create a new family element) and Delete (remove a family element), and the Count property (return the number of elements in the family).

Add method of the ChartObjects family:

Add(Left, Top, Width, Height)

• Left, Top — set the coordinates on the worksheet of the chart’s upper-left corner.
• Width, Height — set the chart’s width and height.
  All parameters are optional.

Add method of the Charts family:

Add(Before, After, Count)

• Before — specifies before which sheet the chart is added.
• After — specifies after which sheet the chart is added.
• Count — specifies how many charts to add.
  All parameters are also optional.

Properties of the Chart object

The Chart object has more than 50 properties that determine the chart’s appearance (see Excel Help for detailed descriptions). The main properties are shown in Table, and the main chart types (values of the ChartType property) are shown in Table.

Table. Key properties of the Chart object

Property	Description
Area3DGroup	Returns a ChartGroup object encapsulating information about a 3-D area
AutoScaling	Enables automatic scaling for 3-D charts
Bar3DGroup	Returns a ChartGroup object for a 3-D bar chart
ChartArea	Returns a ChartArea object
ChartTitle	Returns a ChartTitle object
ChartType	Sets the chart type (valid values in Table 6.4)
Column3DGroup	Returns a ChartGroup object for 3-D columns
Corners	Returns a Corners object
DataTable	Returns a DataTable object
DepthPercent	Sets the depth percentage for a 3-D chart
DisplayBlanksAs	Specifies how empty cells are interpreted: xlNotPlotted, xlInterpolated, xlZero
Elevation	Sets the viewing angle for a 3-D chart
Floor	Returns a Floor object
GapDepth	Sets the gap between series in a 3-D chart
HasAxis	Specifies whether the chart has axes
HasDataTable	Specifies whether the chart has a data table
HasLegend	Checks whether the chart has a legend
HasTitle	Checks whether the chart has a title
HeightPercent	Sets chart height as a percentage of its width
Hyperlinks	Returns the Hyperlinks collection
Index	Returns the index value within Charts
Legend	Returns a Legend object
PageSetup	Returns a PageSetup object
Perspective	Sets perspective for a 3-D chart
PlotArea	Returns a PlotArea object
PlotBy	Defines how data is laid out: xlColumns or xlRows
PlotVisibleOnly	Specifies whether to include hidden cells
ProtectContents, ProtectData, ProtectDrawingObjects, ProtectFormatting, ProtectionSelection, ProtectGoalSeek	Boolean properties indicating whether protection is enabled for the corresponding chart element
Rotation	Returns the rotation angle of a 3-D chart around the z-axis
Visible	Controls chart visibility
Walls	Returns a Walls object

Table. Valid values of the ChartType property

Type of Chart	Constants (in English)
Column Chart	xlColumnClustered, xl3DColumnClustered, xlColumnStacked, xl3DColumnStacked, xlColumnStacked100, xl3DColumnStacked100, xl3DColumn
Bar Chart	xlBarClustered, xl3DBarClustered, xlBarStacked, xl3DBarStacked, xlBarStacked100, xl3DBarStacked100
Line Chart	xlLine, xlLineMarkers, xlLineStacked, xlLineMarkersStacked, xlLineStacked100, xlLineMarkersStacked100, xl3DLine
Pie Chart	xlPie, xlPieExploded, xl3DPie, xl3DPieExploded, xlPieOfPie, xlBarOfPie
XY Scatter Chart	xlXYScatter, xlXYScatterSmooth, xlXYScatterSmoothNoMarkers, xlXYScatterLines, xlXYScatterLinesNoMarkers
Area Chart	xlArea, xl3DArea, xlAreaStacked, xl3DAreaStacked, xlAreaStacked100, xl3DAreaStacked100
Doughnut Chart	xlDoughnut, xlDoughnutExploded
Radar Chart	xlRadar, xlRadarMarkers, xlRadarFilled
Surface Chart	xlSurface, xlSurfaceTopView, xlSurfaceWireframe, xlSurfaceTopViewWireframe
Bubble Chart	xlBubble, xlBubble3DEffect
Stock Chart	xlStockHLC, xlStockVHLC, xlStockOHLC, xlStockVOHLC
Cylinder Chart	xlCylinderColClustered, xlCylinderBarClustered, xlCylinderColStacked, xlCylinderBarStacked, xlCylinderColStacked100, xlCylinderBarStacked100, xlCylinderCol
Cone Chart	xlConeColClustered, xlConeBarClustered, xlConeColStacked, xlConeBarStacked, xlConeColStacked100, xlConeBarStacked100, xlConeCol
Pyramid Chart	xlPyramidColClustered, xlPyramidBarClustered, xlPyramidColStacked, xlPyramidBarStacked, xlPyramidColStacked100, xlPyramidBarStacked100, xlPyramidCol

Methods of the Chart object

Like any object, Chart has methods to control its appearance and behavior. The methods most useful in practice are shown in Table.

Table. Methods of the Chart object

Method	Description
Activate	Activates the chart
ApplyDataLabels	Applies specified data labels
AutoFormat	Applies autoformat
Axes	Returns the Axes collection for setting axis properties
ChartObjects	Returns the ChartObjects collection
ChartWizard	Creates/sets up a chart using the wizard
CheckSpelling	Checks spelling
Copy	Copies the chart to the specified location
CopyPicture	Copies the chart to the clipboard as a picture
Delete	Deletes the chart
Deselect	Clears selection from the chart
Export	Exports the chart to a graphics file
GetChartElement	Returns information about the chart element at a specified point
Location	Sets the chart location
Move	Moves the chart
Paste	Pastes chart data from the clipboard
PrintOut	Prints the chart
SendToBack	Sends the chart behind other objects
Protect	Sets protection
Refresh	Refreshes the chart
SaveAs	Saves the modified chart to a new file
Select	Selects the chart
SeriesCollection	Returns the series collection
SetBackgroundPicture	Sets a background picture
SetSourceData	Specifies the source range for the chart
Unprotect	Removes protection from the chart

Events of the Chart object

The Chart object also exposes a number of events (Table 6.6) that allow you to track various user actions.

Table. Events of the Chart object

Event	Description
Activate	Occurs when the chart is activated
BeforeDoubleClick	Occurs before a double-click
BeforeRightClick	Occurs before a right-click
Calculate	Occurs when data change
Deactivate	Occurs when the chart is deactivated
DragOver	Occurs when a range is dragged over the chart
DragPlot	Occurs when a range is dragged and dropped into the chart
MouseDown, MouseUp	Occur when the user presses/releases any mouse button
MouseMove	Occurs when the user moves the mouse pointer over the chart
Resize	Occurs when the chart is resized
Select	Occurs when a chart element is selected
SeriesChange	Occurs when the reference to a data series changes

 

Let’s create a chart based on numerical data about world merchandise exports using the capabilities of Microsoft Excel 2010, following these steps:

• Prepare the data
  Enter the data related to world merchandise exports on a Microsoft Excel worksheet.

• Select the data range
  Highlight the prepared data range.
• Insert the chart
  Go to the Insert tab on the ribbon, and in the Charts group choose the required chart type.

• In our case, click the drop-down arrow under Column Chart and select 3-D Column.
  Note: by clicking the Insert Chart dialog box launcher in the lower right corner of the Charts group, you can preview all available chart types in the corresponding window.
• Chart placement
  The chart will appear on the worksheet next to the data, and three additional contextual tabs will appear on the ribbon under Chart Tools: Design, Layout, and Format.
• Format the chart
  • First, enlarge the chart area by dragging its border with the mouse pointer (resize handle).
  • Notice that for this chart type, when you enlarge the size, all data labels appear on the three axes.
  • Next, remove the legend by clicking it and pressing .
  • Go to the Design contextual tab, and in the Data group click Switch Row/Column if necessary to change the orientation of the data on the chart.
  • In the same group, the Select Data button allows you to adjust the data range displayed on the chart.

• Add a chart title
  On the Layout contextual tab, go to the Labels group, click Chart Title, and select Centered Overlay Title. In the title area that appears, type:
  World Merchandise Exports, at 2000 Prices and PPP, in Billion USD.
• Further customization
  Using other options provided by the contextual tabs or each chart element’s context menu, change the chart style, chart area fill, and formatting of different chart elements.
  Note: by selecting Move Chart in the Location group on the Design contextual tab, you can place the chart on a separate chart sheet in the Excel workbook.
• Final result
  The resulting chart may look, for example, as shown.

In MS Excel, you can create two types of charts: embedded charts and charts on separate sheets. Embedded charts are created on the worksheet alongside tables, data, and text, and are mainly used in reports. Charts on separate sheets are more convenient for preparing slides or printing.

In the new version, Excel 2010, creating a chart is practically done with a single mouse click: select the prepared data for the chart, go to the Insert tab on the ribbon, and in the Charts group choose the required chart type. By default, the created chart is placed next to the data on the worksheet. When the chart is activated, three additional contextual tabs of the ribbon become available: Design, Layout, and Format. The tools on these tabs allow you to format the chart, change its type, style, location, etc.

MS Excel charts consist of different objects, each of which can be modified and formatted. In addition, MS Excel offers a variety of chart types.

Elements of an MS Excel chart include:

• Chart area
• Chart title
• Data point
• Plot area
• Value axis
• Side wall
• Data label
• Data series
• Back wall
• Category axis
• Floor
• Legend

Table. Types of MS Excel Charts

Chart Type	Description
Column Chart (Histogram)	Used to compare individual values or their changes over a certain period of time. Suitable for displaying discrete data.
Line Chart	Displays the dependence of data (Y-axis) on a variable that changes at a constant rate (X-axis). Category axis labels should be arranged in ascending or descending order. Line charts are commonly used for commercial or financial data evenly distributed over time (continuous data), such as sales, prices, etc.
Pie Chart	Displays the relationship between parts and the whole, based on only one data series (the first in the selected range). Best used when components sum to 100%.
Bar Chart	Similar to column charts but rotated 90° clockwise. Used to compare separate values at a specific point in time; does not show changes over time. The horizontal layout helps emphasize positive or negative deviations from a reference value. Useful, for example, for displaying budget deviations across different categories.
Area Chart	Shows the cumulative change of values across data series and the contribution of each series to the total. Often used to represent production or sales processes over equally spaced intervals.
Scatter Chart (XY)	Clearly demonstrates data trends with irregular time intervals or measurement scales on the category axis. Useful for displaying discrete measurements on X and Y axes. The X-axis divisions are evenly distributed between the lowest and highest X values.
Stock Chart	Used to display stock market data (e.g., opening, closing, and highest prices). Shows sets of three or more values.
Surface Chart	Displays high and low points of a surface, used for datasets depending on two variables. The chart can be rotated and viewed from different angles.
Doughnut Chart	Similar to a pie chart but can display two or more data series to compare contributions of parts to a whole.
Radar Chart (Spider Chart)	Typically used to show relationships among multiple data series or to compare one specific series to all others. Each category has its own axis (ray). Data points along the rays are connected to form a shape representing the distribution of values. Useful for showing, for example, time distribution across project tasks.
Bubble Chart	Displays sets of three values in two dimensions: X and Y represent coordinates, while bubble size represents the third value.

Additional Chart Operations

With charts, you can also:

• add or remove data series;
• edit, format, and add different chart elements;
• change the 3D orientation of charts;
• add graphical objects (arrows, callouts, etc.);
• adjust axes and scales;
• change chart types;
• create picture-filled charts (instead of color fill);
• link text on the chart to worksheet cells;
• build charts from structured data;
• use charts for data analysis, e.g., add trendlines and make forecasts.

Note

In Microsoft Office Excel 2010, a new feature was introduced: you can now place so-called sparklines directly in worksheet cells. Sparklines are microcharts displayed inside a single cell, visualizing data from the corresponding row in the table. With sparklines, you can show trends in value series (such as currency markets, economic cycles, sales by region, etc.) and highlight maximum and minimum values. Unlike regular charts, sparklines are not objects: essentially, a sparkline is the background of a cell. You can add a sparkline from the Sparklines group on the Insert tab of the ribbon. Further work with sparklines is done using the commands on the Design tab in the Sparkline Tools context mode.