Home > Charts > Advanced > Macros

Macros for Excel Charts

Use these sample macros to work with Excel charts. For example, export a chart as a picture, apply colour to pie chart slices, or delete all charts

Note: For pivot chart macros, go to the Pivot Chart VBA page.

Export Picture of Active Chart

The following code will export a picture of the active chart, in gif format. Change the file name and path to suit your needs.

Sub ExportChartGIF()
ActiveChart.Export filename:="C:\Data\ExpChart2K.gif", _
  FilterName:="GIF"
End Sub

Delete All Charts in Workbook

Use this sample code carefully! It deletes all the charts in the active workbook, and is helpful if you want to clear things out and start fresh.

Sub AllChartsDelete()
On Error GoTo ExitChart
Dim ws As Worksheet
Dim chObj As ChartObject
  Application.DisplayAlerts = False
  'delete embedded charts
  For Each ws In ActiveWorkbook.Worksheets
    For Each chObj In ws.ChartObjects
       chObj.Delete
    Next chObj
  Next ws
  'delete the chart sheets
  ActiveWorkbook.Charts.Delete

ExitChart:
  Application.DisplayAlerts = True
  Exit Sub
End Sub

Video: Show Hidden Data in Excel Chart

If you create an Excel chart, then hide rows or columns in your worksheet, the hidden data might also disappear from your chart. By default, Excel charts do not display the data in hidden rows and columns.

This video shows how to change an Excel chart's settings (manually), so all the data will appear in the chart, even if some of the data rows or data columns are hidden.

To make this change quickly, for multiple charts, you can use the macro shown below video.

Macros - Show Hidden Data in Charts

There are two macros shown below -

-- VisDataOnlyOnALLSheets

• Turns ON the chart setting to show visible worksheet data only
• Affects all charts on all sheets in the active workbook

-- VisDataOnlyOffALLSheets

• Turns OFF the chart setting to show visible worksheet data only
• Affects all charts on all sheets in the active workbook

There are details steps on the Show Hidden Data in Excel Chart page, and macros to change the hidden data settings for Sparkline charts too.

Copy the Macro Code

Copy this macro code, and paste it into a regular code module in your workbook. Later, run this macro to change the settings for all charts, on all worksheets in the active workbook.

The macros, and buttons to run them are also in the Hidden Data in Charts sample file that you can download, at the bottom of this page.

Sub VisDataOnlyOnALLSheets()
'only visible data in chart
'all charts - all worksheets
Dim wb As Workbook
Dim ws As Worksheet
Dim ch As ChartObject

Set wb = ActiveWorkbook
On Error Resume Next

For Each ws In wb.Worksheets
  If ws.ChartObjects.Count > 0 Then
    For Each ch In ws.ChartObjects
      ch.Chart.PlotVisibleOnly = True
    Next ch
  Else
    MsgBox "No charts on this sheet"
  End If
Next ws

End Sub
'=================================
Sub VisDataOnlyOffALLSheets()
'include hidden data in chart
'all charts - all worksheets
Dim wb As Workbook
Dim ws As Worksheet
Dim ch As ChartObject

Set wb = ActiveWorkbook
On Error Resume Next

For Each ws In wb.Worksheets
  If ws.ChartObjects.Count > 0 Then
    For Each ch In ws.ChartObjects
      ch.Chart.PlotVisibleOnly = False
    Next ch
  Else
    MsgBox "No charts on this sheet"
  End If
Next ws

End Sub

Remove Error Bars

The following code will remove both the X and Y error bars, for the first chart on the active worksheet.

Sub RemoveErrorBars()

Dim ch As ChartObject
Dim ser As Series
  Set ch = ActiveSheet.ChartObjects(1)
  Set ser = ch.Chart.SeriesCollection(1)
   ser.ErrorBar Direction:=xlX, _
        Include:=xlErrorBarIncludeNone, Type:=xlStError
   ser.ErrorBar Direction:=xlY, _
        Include:=xlErrorBarIncludeNone, Type:=xlStError

End Sub

Add Error Bars

The following code will add both the X and Y error bars, for the first chart on the active worksheet.

The Y error bars will have a fixed value of 2, and the X error bars will be set for 10%.

Sub AddErrorBars()


Dim ch As ChartObject
Dim ser As Series

Set ch = ActiveSheet.ChartObjects(1)
Set ser = ch.Chart.SeriesCollection(1)
  ser.ErrorBar Direction:=xlY, _
     Include:=xlErrorBarIncludeBoth, _
     Type:=xlErrorBarTypeFixedValue, _
     Amount:=2
  ser.ErrorBar Direction:=xlX, _
     Include:=xlErrorBarIncludeBoth, _
     Type:=xlErrorBarTypePercent, _
     Amount:=10

End Sub

Add or Remove Error Bars

The following code will add the X error bars, and remove Y error bars, for the first chart on the active worksheet.

The X error bars will be set for 10%.

Sub SetErrorBars()

Dim ch As ChartObject
Dim ser As Series

Set ch = ActiveSheet.ChartObjects(1)
Set ser = ch.Chart.SeriesCollection(1)

ser.ErrorBar Direction:=xlX, _
    Include:=xlErrorBarIncludeBoth, _
    Type:=xlErrorBarTypePercent, _
    Amount:=10
ser.ErrorBar Direction:=xlY, _
    Include:=xlErrorBarIncludeNone, _
    Type:=xlStError

End Sub

Pie Chart Colour Macro

This macro uses conditional formatting to colour the slices in an Excel pie chart.

In the pie chart, the slice size and colour gives us information about client orders and invoices:

• slice size based on the client's order amount
• slice colour based on a colour scale for the % invoiced

We need to send another invoice to Fran -- her pie slice is large, and the orange colour shows a mid-range percentage invoiced

Pie Chart Data

Here’s a screen shot of the sample data for this pie cahrt.

• Client names in column B
• Client order totals in column C
• Total invoiced amounts in column D
• Formulas in column E calculate percent invoiced
• Conditional formatting colour scale in column E - green highest, red lowest

Pie Chart Colour Match Macro

There's no built-in way to match a pie slice colour to a cell's condtional formatting colour, so I wrote a macro to do the job.

• The macro gets the location of the source data, based on the formula for the chart series.
• Then, the macro gets the conditional formatting color from the 3rd column to the right of the source data’s start column, by using the DisplayFormat property for the cell
• That color is used to colour the applicable pie chart slice

Macro VBA Code

Here is the VBA code, and it's also available in the sample file you can download. The sample file also has a bar chart on the worksheet, and the macro colours its bars too.

Sub ColorChartDataPoints()
'colour data point based on
'value in rank column
Dim ws As Worksheet
Dim ch As ChartObject
Dim ser As Series
Dim dp As Point
Dim ptnum As Long
Dim rngSD01 As Range
Dim strF  As String
Dim strRng  As String
Dim CharStart As Long
Dim CharEnd As Long
Dim ColOff As Long
Dim PtColor As Long

ColOff = 3  'offset to Rank column
Set ws = ActiveSheet

For Each ch In ws.ChartObjects
  Set ser = ch.Chart.FullSeriesCollection(1)
  strF = ch.Chart.SeriesCollection(1).Formula
   
  CharStart = InStr(1, strF, ",")
  CharEnd = InStr(InStr(1, strF, ",") _
        + 1, strF, ",")
  strRng = Mid(strF, CharStart + 1, _
         CharEnd - CharStart - 1)
  Set rngSD01 = ws.Range(strRng)
  ptnum = 1
  
  For Each dp In ser.Points
    PtColor = rngSD01.Cells(ptnum, 1) _
        .Offset(0, ColOff).DisplayFormat.Interior.Color
    dp.Format.Fill.ForeColor.RGB = PtColor
    ptnum = ptnum + 1
  Next dp
Next ch

End Sub

Running the Macro

The macro runs automatically, if any changes are made in the named range, "ClientAmts". The following event code is on the worksheet code module for the sheet named Chart

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("ClientAmts")) Is Nothing Then
  ColorChartDataPoints
End If
End Sub

Download Sample Files

Chart Colour Based on Rank -- Get the sample Excel Chart Colour Macro workbook, with VBA macro that changes the pie chart and bar chart colours, based on rank. The zipped file is in xlsm format, and contains macros

Hidden Chart Data Macro: Get the Excel workbook with hidden chart data, and change setting to show that data in the chart, manually, or with macros. The zipped workbook is in xlsm format, and contains the macros from this page

Sparklines Hidden Data Macro: Download the Excel workbook with hidden Sparklines data example. Macro changes all sparklines on active sheet, so they will show data, even if rows and columns are hidden. Excel 2010 or later. The zipped workbook is in xlsm format, and contains the macros from this page

More Tutorials

Show Hidden Data in Charts

Interactive Chart - Totals

Cluster Stack Chart

Pie Charts

Charts, Line-Column 2 Axes

Charts, Panel

Charts, Waterfall

VBA Code, Copy to a workbook