Excel: Macro for Automatic Chart Re-formatting[ ]
Excel’s default chart formatting is … not amazing.
While aesthetic preferences differ, to my eye I’d far rather it have:
- No legend (I usually prefer labeling series directly)
- No border around the chart object (cleaner when pasting into reports, etc.)
- A border around the full plot area with color matched to the axes & ticks
- A lighter shade of gray and dashed lines on the gridlines
- A larger font for the axes tick labels
- Axis labels for both the x- and y-axes
While I’ll often want to tweak further, these would
provide a solid formatting baseline. While it would be
possible to define a custom chart type with these properties, I’d have to
define a custom type for every charting variation (scatter, line, column, etc.).
I’d much rather just generate the default chart I want from the
and then be able to reformat it from there.
So, I finally took the time to write a macro for the reformatting, convert it to an add-in, and bind it to a keyboard shortcut. (I have eventual plans for eventually writing up the procedures for add-in creation and keyboard shortcut binding…eventually. Maybe.)
Sub FormatInlineChart() Dim ca As ChartArea, co As ChartObject, pa As PlotArea, cht As Chart Dim shp As Shape, ax As Axis Set cht = ActiveChart If cht Is Nothing Then MsgBox "No chart selected!" Exit Sub End If Set ca = cht.ChartArea Set pa = cht.PlotArea Set co = cht.Parent Set shp = co.ShapeRange(1) With ca .Format.Line.Visible = msoFalse End With With co .Width = 300 .Height = 250 End With With cht On Error Resume Next .Legend.Delete Err.Clear: On Error GoTo 0 Set ax = .Axes(xlCategory) With ax .HasTitle = True .AxisTitle.Text = "[X-Axis Text]" .AxisTitle.Characters.Font.Size = 14 .TickLabels.Font.Size = 12 End With Set ax = .Axes(xlValue) With ax .HasTitle = True .AxisTitle.Orientation = xlUpward .AxisTitle.Text = "[Y-Axis Text]" .AxisTitle.Characters.Font.Size = 14 .TickLabels.Font.Size = 12 With .MajorGridlines.Format.Line .Weight = 0.75 .ForeColor.RGB = RGB(210, 210, 210) .DashStyle = msoLineDash End With End With End With With pa With .Format.Line .Visible = msoTrue .ForeColor.RGB = RGB(127, 127, 127) End With End With End Sub
After running the macro, the chart looks like this:
Now that I look at it, it might be a little tall – a
so might be preferable. But, that’s easy enough to change. I could also always
augment the macro so that it queries for the desired dimensions, font sizes, etc.
before doing the reformatting.
The initial trawl through the object hierarchy is probably the least obvious part
of the whole thing; everything else is a pretty straightforward modification
yields the relevant
which then exposes the needed
properties. Resizing the overall chart requires access to the containing
which is accessed through