Add A Data Table To The Chart

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

You can add data tables to any chart you create. You use data tables to provide a list of the values you see on the corresponding chart. Because the data values that Excel plots on a chart can come from different ranges of data, data tables work well for showing the actual data values from the chart in a concise table.

Excel stores the data table associated with a chart in the DataTable object. The HasDataTable property, associated with the Chart object, specifies whether a data table actually displays for the selected chart. This property only accepts Boolean values of either True or False. To display a data table for a chart, you need to set this property to True.

After you set the HasDataTable property, you can customize the data table using the properties associated with the DataTable object. Just like the other objects associated with the Chart object, the DataTable object has its own list of associated properties and methods. For example, you can customize the font settings and border settings for the data table. You can specify the font for the data table using the Font property. With the Font property, you use the properties associated with the Font object to specify the actual font properties of the text. For example, DataTable.Font.Name = "Arial" specifies that the data table uses the Arial font. See the section "Format Chart Text" for more information on working with the Font object on a chart.

You select and unselect the display of borders for the data table using the HasBorderHorizontal, HasBorderOutline, and HasBorderVertical properties. By default, Excel displays borders on a data table. The Border object contains the entire borders for the data table. You can customize the border using the associated properties.

ADD A DATA TABLE TO THE CHART

ADD A DATA TABLE TO THE CHART

—D Create a new subroutine.

L0 Type SelectChart As Chart, replacing SelectChart with the chart variable.

LH Type Set SelectChart = ThisWorkbook.Charts(1), replacing

ThisWorkbook.Charts(l) with the chart to modify.

'-Q Type SelectChart.HasData Table = True.

—D Create a new subroutine.

L0 Type SelectChart As Chart, replacing SelectChart with the chart variable.

LH Type Set SelectChart = ThisWorkbook.Charts(1), replacing

ThisWorkbook.Charts(l) with the chart to modify.

'-Q Type SelectChart.HasData Table = True.

WORKING WITH CHARTS

When you add a data table to a chart, you have the option of combining the chart legend with the data table. By doing so, you can create a more readable chart because you can quickly see which chart series has the specified data values.

To create a data table containing the chart legend, you need to set the ShowLegendKey property to True for the DataTable object. By default, Excel sets this value to True, but it is a good idea to specify this value to ensure its proper setting. Use the following code to set the value of the ShowLegendKey property.

Example:

ThisWorkbook.Charts(1). DataTable. ShowLegendKey = True

If you display the legend as part of the data table, you typically do not want the legend to display separately on your chart. To hide the legend, you can set the HasLegend property for the Chart object to False, as illustrated with the following code:

Example:

ThisWorkbook.Charts(1).HasLegend = False

Type

SelectChart.DataTable.Font

.Name = "Tahoma", replacing "Tahoma" with the font name.

Type any additional chart or data-table-related statements.

□ Switch to Excel and run the macro.

Excel displays the chart with the data table at the bottom.

Type

SelectChart.DataTable.Font

.Name = "Tahoma", replacing "Tahoma" with the font name.

□ Type SelectChart.DataTable .Border.Color = RGB(0, 0, 255), replacing (0, 0, 255) with the border color.

Type any additional chart or data-table-related statements.

□ Switch to Excel and run the macro.

Excel displays the chart with the data table at the bottom.

Was this article helpful?

+2 -3

Responses

  • Bosco
    How to add table in chart object excel?
    2 years ago
  • tamara toscano
    How to check when datatable is updated with vba?
    1 year ago
  • hugo
    How to use chart data table values in Excel using VBA?
    1 year ago
  • Antonella Greece
    How to show table with chart in Excel VBA?
    7 months ago
  • janelle
    How to add a editable chart in vba macro?
    5 months ago
  • guendalina calabresi
    How to get both charts and data tables on the same slide using vba?
    5 months ago
  • maria kuefer
    How selection a data table in a excel macro?
    4 months ago

Post a comment