< Back
New-ExcelChartDefinition
Post
NAME New-ExcelChartDefinition
SYNOPSIS
Creates a Definition of a chart which can be added using Export-Excel, or Add-PivotTable
SYNTAX
New-ExcelChartDefinition [[-Title] <Object>] [[-RowOffSetPixels] <Object>] [[-Column] <Object>]
[[-ColumnOffSetPixels] <Object>] [[-LegendPosition] {Top | Left | Right | Bottom | TopRight}] [[-LegendSize]
<Object>] [[-SeriesHeader] <Object>] [[-TitleSize] <Int32>] [[-XAxisTitleText] <String>] [[-XAxisTitleSize]
<Object>] [[-XAxisNumberformat] <String>] [[-Header] <Object>] [[-XMajorUnit] <Object>] [[-XMinorUnit] <Object>]
[[-XMaxValue] <Object>] [[-XMinValue] <Object>] [[-XAxisPosition] {Left | Bottom | Right | Top}]
[[-YAxisTitleText] <String>] [[-YAxisTitleSize] <Object>] [[-YAxisNumberformat] <String>] [[-YMajorUnit] <Object>]
[[-YMinorUnit] <Object>] [[-ChartType] {Area | Line | Pie | Bubble | ColumnClustered | ColumnStacked |
ColumnStacked100 | ColumnClustered3D | ColumnStacked3D | ColumnStacked1003D | BarClustered | BarStacked |
BarStacked100 | BarClustered3D | BarStacked3D | BarStacked1003D | LineStacked | LineStacked100 | LineMarkers |
LineMarkersStacked | LineMarkersStacked100 | PieOfPie | PieExploded | PieExploded3D | BarOfPie | XYScatterSmooth |
XYScatterSmoothNoMarkers | XYScatterLines | XYScatterLinesNoMarkers | AreaStacked | AreaStacked100 | AreaStacked3D
| AreaStacked1003D | DoughnutExploded | RadarMarkers | RadarFilled | Surface | SurfaceWireframe | SurfaceTopView |
SurfaceTopViewWireframe | Bubble3DEffect | StockHLC | StockOHLC | StockVHLC | StockVOHLC | CylinderColClustered |
CylinderColStacked | CylinderColStacked100 | CylinderBarClustered | CylinderBarStacked | CylinderBarStacked100 |
CylinderCol | ConeColClustered | ConeColStacked | ConeColStacked100 | ConeBarClustered | ConeBarStacked |
ConeBarStacked100 | ConeCol | PyramidColClustered | PyramidColStacked | PyramidColStacked100 | PyramidBarClustered
| PyramidBarStacked | PyramidBarStacked100 | PyramidCol | XYScatter | Radar | Doughnut | Pie3D | Line3D | Column3D
| Area3D}] [[-YMaxValue] <Object>] [[-YMinValue] <Object>] [[-YAxisPosition] {Left | Bottom | Right | Top}]
[[-ChartTrendLine] {Exponential | Linear | Logarithmic | MovingAvgerage | Polynomial | Power}] [[-XRange]
<Object>] [[-YRange] <Object>] [[-Width] <Object>] [[-Height] <Object>] [[-Row] <Object>] [-LegendBold]
[-NoLegend] [-ShowCategory] [-ShowPercent] [-TitleBold] [-XAxisTitleBold] [-YAxisTitleBold] [<CommonParameters>]
DESCRIPTION
All the parameters which are passed to Add-ExcelChart can be added to a chart-definition object and passed to
Export-Excel with the -ExcelChartDefinition parameter, or to Add-PivotTable with the -PivotChartDefinition
parameter. This command sets up those definition objects.
PARAMETERS
-Title <Object>
The title for the chart.
Required? false
Position? 1
Default value Chart Title
Accept pipeline input? False
Accept wildcard characters? false
-Header <Object>
No longer used. This may be removed in future versions.
Required? false
Position? 2
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-ChartType <eChartType>
One of the built-in chart types, such as Pie, ClusteredColumn, Line etc. Defaults to "ColumnStacked".
Required? false
Position? 3
Default value ColumnStacked
Accept pipeline input? False
Accept wildcard characters? false
-ChartTrendLine <eTrendLine[]>
Superimposes one of Excel's trenline types on the chart.
Required? false
Position? 4
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XRange <Object>
The range of cells containing values for the X-Axis - usually labels.
Required? false
Position? 5
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YRange <Object>
The range(s) of cells holding values for the Y-Axis - usually "data".
Required? false
Position? 6
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-Width <Object>
Width of the chart in pixels. Defaults to 500.
Required? false
Position? 7
Default value 500
Accept pipeline input? False
Accept wildcard characters? false
-Height <Object>
Height of the chart in pixels. Defaults to 350.
Required? false
Position? 8
Default value 350
Accept pipeline input? False
Accept wildcard characters? false
-Row <Object>
Row position of the top left corner of the chart. 0 places it at the top of the sheet, 1 below row 1 and so on.
Required? false
Position? 9
Default value 0
Accept pipeline input? False
Accept wildcard characters? false
-RowOffSetPixels <Object>
Offset to position the chart by a fraction of a row.
Required? false
Position? 10
Default value 10
Accept pipeline input? False
Accept wildcard characters? false
-Column <Object>
Column position of the top left corner of the chart. 0 places it at the edge of the sheet, 1 to the right of
column A and so on.
Required? false
Position? 11
Default value 6
Accept pipeline input? False
Accept wildcard characters? false
-ColumnOffSetPixels <Object>
Offset to position the chart by a fraction of a column.
Required? false
Position? 12
Default value 5
Accept pipeline input? False
Accept wildcard characters? false
-LegendPosition <eLegendPosition>
Location of the key, either "Left", "Right", "Top", "Bottom" or "TopRight".
Required? false
Position? 13
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-LegendSize <Object>
Font size for the key.
Required? false
Position? 14
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-LegendBold [<SwitchParameter>]
Sets the key in bold type.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-NoLegend [<SwitchParameter>]
If specified, turns off display of the key. If you only have one data series it may be preferable to use the
title to say what the chart is.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-ShowCategory [<SwitchParameter>]
Attaches a category label in charts which support this.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-ShowPercent [<SwitchParameter>]
Attaches a percentage label in charts which support this.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-SeriesHeader <Object>
Specifies explicit name(s) for the data series, which will appear in the legend/key
Required? false
Position? 15
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-TitleBold [<SwitchParameter>]
Sets the title in bold face.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-TitleSize <Int32>
Sets the point size for the title.
Required? false
Position? 16
Default value 0
Accept pipeline input? False
Accept wildcard characters? false
-XAxisTitleText <String>
Specifies a title for the X-axis.
Required? false
Position? 17
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XAxisTitleBold [<SwitchParameter>]
Sets the X-axis title in bold face.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-XAxisTitleSize <Object>
Sets the font size for the axis title.
Required? false
Position? 18
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XAxisNumberformat <String>
A number formatting string, like "#,##0.00", for numbers along the X-axis.
Required? false
Position? 19
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XMajorUnit <Object>
Spacing for the major gridlines / tick marks along the X-axis.
Required? false
Position? 20
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XMinorUnit <Object>
Spacing for the minor gridlines / tick marks along the X-axis.
Required? false
Position? 21
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XMaxValue <Object>
Maximum value for the scale along the X-axis.
Required? false
Position? 22
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XMinValue <Object>
Minimum value for the scale along the X-axis.
Required? false
Position? 23
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XAxisPosition <eAxisPosition>
Position for the X-axis ("Top" or" Bottom").
Required? false
Position? 24
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YAxisTitleText <String>
Specifies a title for the Y-axis.
Required? false
Position? 25
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YAxisTitleBold [<SwitchParameter>]
Sets the Y-axis title in bold face.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-YAxisTitleSize <Object>
Sets the font size for the Y-axis title.
Required? false
Position? 26
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YAxisNumberformat <String>
A number formatting string, like "#,##0.00", for numbers on the Y-axis
Required? false
Position? 27
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YMajorUnit <Object>
Spacing for the major gridlines / tick marks on the Y-axis.
Required? false
Position? 28
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YMinorUnit <Object>
Spacing for the minor gridlines / tick marks on the Y-axis.
Required? false
Position? 29
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YMaxValue <Object>
Maximum value on the Y-axis.
Required? false
Position? 30
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YMinValue <Object>
Minimum value on the Y-axis.
Required? false
Position? 31
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YAxisPosition <eAxisPosition>
Position for the Y-axis ("Left" or "Right").
Required? false
Position? 32
Default value None
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
-------------------------- EXAMPLE 1 --------------------------
PS\\> $cDef = New-ExcelChartDefinition -ChartType line -XRange "X" -YRange "Sinx" -Title "Graph of Sine X"
-TitleBold -TitleSize 14 -Column 2 -ColumnOffSetPixels 35 -Width 800 -XAxisTitleText "Degrees" -XAxisTitleBold
-XAxisTitleSize 12 -XMajorUnit 30 -XMinorUnit 10 -XMinValue 0 -XMaxValue 361 -XAxisNumberformat "000" -YMinValue
-1.25 -YMaxValue 1.25 -YMajorUnit 0.25 -YAxisNumberformat "0.00" -YAxisTitleText "Sine" -YAxisTitleBold
-YAxisTitleSize 12 -SeriesHeader "Sin(x)" -LegendSize 8 -legendBold -LegendPosition Bottom
PS\\> 0..360 | ForEach-Object {[pscustomobject][ordered]@{x = $_; Sinx = "=Sin(Radians(x)) "}} | Export-Excel
-AutoNameRange -now -WorkSheetname SinX -ExcelChartDefinition $cDef -Show
This reworks an example from Add-Excel-Chart but here the chart is defined and the defintion stored in $cDef and
then Export-Excel uses $cDef .
RELATED LINKS
SYNOPSIS
Creates a Definition of a chart which can be added using Export-Excel, or Add-PivotTable
SYNTAX
New-ExcelChartDefinition [[-Title] <Object>] [[-RowOffSetPixels] <Object>] [[-Column] <Object>]
[[-ColumnOffSetPixels] <Object>] [[-LegendPosition] {Top | Left | Right | Bottom | TopRight}] [[-LegendSize]
<Object>] [[-SeriesHeader] <Object>] [[-TitleSize] <Int32>] [[-XAxisTitleText] <String>] [[-XAxisTitleSize]
<Object>] [[-XAxisNumberformat] <String>] [[-Header] <Object>] [[-XMajorUnit] <Object>] [[-XMinorUnit] <Object>]
[[-XMaxValue] <Object>] [[-XMinValue] <Object>] [[-XAxisPosition] {Left | Bottom | Right | Top}]
[[-YAxisTitleText] <String>] [[-YAxisTitleSize] <Object>] [[-YAxisNumberformat] <String>] [[-YMajorUnit] <Object>]
[[-YMinorUnit] <Object>] [[-ChartType] {Area | Line | Pie | Bubble | ColumnClustered | ColumnStacked |
ColumnStacked100 | ColumnClustered3D | ColumnStacked3D | ColumnStacked1003D | BarClustered | BarStacked |
BarStacked100 | BarClustered3D | BarStacked3D | BarStacked1003D | LineStacked | LineStacked100 | LineMarkers |
LineMarkersStacked | LineMarkersStacked100 | PieOfPie | PieExploded | PieExploded3D | BarOfPie | XYScatterSmooth |
XYScatterSmoothNoMarkers | XYScatterLines | XYScatterLinesNoMarkers | AreaStacked | AreaStacked100 | AreaStacked3D
| AreaStacked1003D | DoughnutExploded | RadarMarkers | RadarFilled | Surface | SurfaceWireframe | SurfaceTopView |
SurfaceTopViewWireframe | Bubble3DEffect | StockHLC | StockOHLC | StockVHLC | StockVOHLC | CylinderColClustered |
CylinderColStacked | CylinderColStacked100 | CylinderBarClustered | CylinderBarStacked | CylinderBarStacked100 |
CylinderCol | ConeColClustered | ConeColStacked | ConeColStacked100 | ConeBarClustered | ConeBarStacked |
ConeBarStacked100 | ConeCol | PyramidColClustered | PyramidColStacked | PyramidColStacked100 | PyramidBarClustered
| PyramidBarStacked | PyramidBarStacked100 | PyramidCol | XYScatter | Radar | Doughnut | Pie3D | Line3D | Column3D
| Area3D}] [[-YMaxValue] <Object>] [[-YMinValue] <Object>] [[-YAxisPosition] {Left | Bottom | Right | Top}]
[[-ChartTrendLine] {Exponential | Linear | Logarithmic | MovingAvgerage | Polynomial | Power}] [[-XRange]
<Object>] [[-YRange] <Object>] [[-Width] <Object>] [[-Height] <Object>] [[-Row] <Object>] [-LegendBold]
[-NoLegend] [-ShowCategory] [-ShowPercent] [-TitleBold] [-XAxisTitleBold] [-YAxisTitleBold] [<CommonParameters>]
DESCRIPTION
All the parameters which are passed to Add-ExcelChart can be added to a chart-definition object and passed to
Export-Excel with the -ExcelChartDefinition parameter, or to Add-PivotTable with the -PivotChartDefinition
parameter. This command sets up those definition objects.
PARAMETERS
-Title <Object>
The title for the chart.
Required? false
Position? 1
Default value Chart Title
Accept pipeline input? False
Accept wildcard characters? false
-Header <Object>
No longer used. This may be removed in future versions.
Required? false
Position? 2
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-ChartType <eChartType>
One of the built-in chart types, such as Pie, ClusteredColumn, Line etc. Defaults to "ColumnStacked".
Required? false
Position? 3
Default value ColumnStacked
Accept pipeline input? False
Accept wildcard characters? false
-ChartTrendLine <eTrendLine[]>
Superimposes one of Excel's trenline types on the chart.
Required? false
Position? 4
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XRange <Object>
The range of cells containing values for the X-Axis - usually labels.
Required? false
Position? 5
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YRange <Object>
The range(s) of cells holding values for the Y-Axis - usually "data".
Required? false
Position? 6
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-Width <Object>
Width of the chart in pixels. Defaults to 500.
Required? false
Position? 7
Default value 500
Accept pipeline input? False
Accept wildcard characters? false
-Height <Object>
Height of the chart in pixels. Defaults to 350.
Required? false
Position? 8
Default value 350
Accept pipeline input? False
Accept wildcard characters? false
-Row <Object>
Row position of the top left corner of the chart. 0 places it at the top of the sheet, 1 below row 1 and so on.
Required? false
Position? 9
Default value 0
Accept pipeline input? False
Accept wildcard characters? false
-RowOffSetPixels <Object>
Offset to position the chart by a fraction of a row.
Required? false
Position? 10
Default value 10
Accept pipeline input? False
Accept wildcard characters? false
-Column <Object>
Column position of the top left corner of the chart. 0 places it at the edge of the sheet, 1 to the right of
column A and so on.
Required? false
Position? 11
Default value 6
Accept pipeline input? False
Accept wildcard characters? false
-ColumnOffSetPixels <Object>
Offset to position the chart by a fraction of a column.
Required? false
Position? 12
Default value 5
Accept pipeline input? False
Accept wildcard characters? false
-LegendPosition <eLegendPosition>
Location of the key, either "Left", "Right", "Top", "Bottom" or "TopRight".
Required? false
Position? 13
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-LegendSize <Object>
Font size for the key.
Required? false
Position? 14
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-LegendBold [<SwitchParameter>]
Sets the key in bold type.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-NoLegend [<SwitchParameter>]
If specified, turns off display of the key. If you only have one data series it may be preferable to use the
title to say what the chart is.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-ShowCategory [<SwitchParameter>]
Attaches a category label in charts which support this.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-ShowPercent [<SwitchParameter>]
Attaches a percentage label in charts which support this.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-SeriesHeader <Object>
Specifies explicit name(s) for the data series, which will appear in the legend/key
Required? false
Position? 15
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-TitleBold [<SwitchParameter>]
Sets the title in bold face.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-TitleSize <Int32>
Sets the point size for the title.
Required? false
Position? 16
Default value 0
Accept pipeline input? False
Accept wildcard characters? false
-XAxisTitleText <String>
Specifies a title for the X-axis.
Required? false
Position? 17
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XAxisTitleBold [<SwitchParameter>]
Sets the X-axis title in bold face.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-XAxisTitleSize <Object>
Sets the font size for the axis title.
Required? false
Position? 18
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XAxisNumberformat <String>
A number formatting string, like "#,##0.00", for numbers along the X-axis.
Required? false
Position? 19
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XMajorUnit <Object>
Spacing for the major gridlines / tick marks along the X-axis.
Required? false
Position? 20
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XMinorUnit <Object>
Spacing for the minor gridlines / tick marks along the X-axis.
Required? false
Position? 21
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XMaxValue <Object>
Maximum value for the scale along the X-axis.
Required? false
Position? 22
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XMinValue <Object>
Minimum value for the scale along the X-axis.
Required? false
Position? 23
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XAxisPosition <eAxisPosition>
Position for the X-axis ("Top" or" Bottom").
Required? false
Position? 24
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YAxisTitleText <String>
Specifies a title for the Y-axis.
Required? false
Position? 25
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YAxisTitleBold [<SwitchParameter>]
Sets the Y-axis title in bold face.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-YAxisTitleSize <Object>
Sets the font size for the Y-axis title.
Required? false
Position? 26
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YAxisNumberformat <String>
A number formatting string, like "#,##0.00", for numbers on the Y-axis
Required? false
Position? 27
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YMajorUnit <Object>
Spacing for the major gridlines / tick marks on the Y-axis.
Required? false
Position? 28
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YMinorUnit <Object>
Spacing for the minor gridlines / tick marks on the Y-axis.
Required? false
Position? 29
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YMaxValue <Object>
Maximum value on the Y-axis.
Required? false
Position? 30
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YMinValue <Object>
Minimum value on the Y-axis.
Required? false
Position? 31
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YAxisPosition <eAxisPosition>
Position for the Y-axis ("Left" or "Right").
Required? false
Position? 32
Default value None
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
-------------------------- EXAMPLE 1 --------------------------
PS\\> $cDef = New-ExcelChartDefinition -ChartType line -XRange "X" -YRange "Sinx" -Title "Graph of Sine X"
-TitleBold -TitleSize 14 -Column 2 -ColumnOffSetPixels 35 -Width 800 -XAxisTitleText "Degrees" -XAxisTitleBold
-XAxisTitleSize 12 -XMajorUnit 30 -XMinorUnit 10 -XMinValue 0 -XMaxValue 361 -XAxisNumberformat "000" -YMinValue
-1.25 -YMaxValue 1.25 -YMajorUnit 0.25 -YAxisNumberformat "0.00" -YAxisTitleText "Sine" -YAxisTitleBold
-YAxisTitleSize 12 -SeriesHeader "Sin(x)" -LegendSize 8 -legendBold -LegendPosition Bottom
PS\\> 0..360 | ForEach-Object {[pscustomobject][ordered]@{x = $_; Sinx = "=Sin(Radians(x)) "}} | Export-Excel
-AutoNameRange -now -WorkSheetname SinX -ExcelChartDefinition $cDef -Show
This reworks an example from Add-Excel-Chart but here the chart is defined and the defintion stored in $cDef and
then Export-Excel uses $cDef .
RELATED LINKS