Home > Charts > Advanced > Macros Macros for Excel ChartsUse 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. |
Video: Show Hidden Data in Excel ChartIf 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 ChartsThere are two macros shown below - -- VisDataOnlyOnALLSheets
-- VisDataOnlyOffALLSheets
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 CodeCopy 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 |
Pie Chart Colour MacroThis 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:
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 DataHere’s a screen shot of the sample data for this pie cahrt.
|
Pie Chart Colour Match MacroThere'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.
Macro VBA CodeHere 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 MacroThe 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 FilesChart 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 |
Last updated: December 18, 2022 2:15 PM