VBA & Macro Problems

How to Format Waterfall and Treemap Charts with VBA in Excel

Steven ParkerSteven Parker Oct 15, 2026 872 views

Question details

Users need to automate the formatting of built-in waterfall and treemap charts in Excel using VBA.

How to Format Waterfall and Treemap Charts with VBA in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Automating chart design and formatting using VBA macros for specific reporting requirements.
Observed behavior
Built-in waterfall and treemap charts expose limited formatting properties in the Excel VBA object model compared to traditional charts, restricting full automation.
Before you start

Verify your Excel version, as the availability of specific VBA properties for modern chart types like Waterfall and Treemap can vary significantly between Office 365 and older desktop versions.

Solution 1Recommended

Utilize Chart Templates and Limited VBA Properties

Apply available VBA formatting commands and use pre-formatted templates to bypass object model limitations.

VBA support for modern built-in charts (like Waterfall and Treemap) is heavily restricted. Many detailed formatting properties are simply not exposed through the standard Excel object model.

To achieve the desired look without full VBA support, you can combine basic macro commands with manually designed chart templates.

1
Check available properties

Test the required settings through the chart's Series, ChartFormat, or Format properties in the VBA editor to see if your specific Excel version supports them.

2
Create a manual template

Format a Waterfall or Treemap chart exactly how you want it manually in Excel, then right-click the chart and save it as a Chart Template (.crtx).

3
Apply template via VBA

Use VBA code to create a new chart and apply your custom template using the ApplyChartTemplate method, which applies your visual formatting without needing individual VBA property adjustments.

Utilize Chart Templates and Limited VBA Properties
Alternative Automations: If VBA is insufficient, consider using Office Scripts (in Excel on the web), which sometimes offer slightly different levels of support for newer chart types.
Free Microsoft Office alternative

Experience Seamless Data Visualization with WPS Office

If you are frustrated by automation and formatting limitations in Microsoft Excel, try WPS Office. It provides robust spreadsheet tools, extensive chart options, a familiar UI, and excellent compatibility with Microsoft file formats, all within a lightweight and free application.

  1. 1. Download and Install: Get WPS Office for free from the official website and install it on your device in just a few minutes.
  2. 2. Open Your Excel File: Launch WPS Spreadsheets and open your existing .xlsx workbooks directly without any conversion or data loss.
  3. 3. Edit and Visualize Data: Enjoy familiar charting tools and straightforward data analysis without the steep learning curve.
Highly compatible with Microsoft Excel (.xlsx) formats and standard macrosLightweight application that loads fast and runs smoothly on your deviceRich built-in chart templates for stunning data visualizationSeamless migration process with a familiar user interface
microsoft office alternative - wps office

Frequently Asked Questions

Why do my VBA macros fail when formatting Excel Waterfall charts?

Waterfall and Treemap charts were introduced in newer Excel versions and rely on a different internal architecture. As a result, many detailed formatting properties available for traditional charts are not exposed in the older VBA object model.

Can I record a macro to format Treemap and Waterfall charts?

Macro recording for these modern chart types is often limited. While the recorder might capture the chart creation, it usually fails to record specific formatting actions like changing individual data point colors or adjusting the layout.

How can I fully automate a waterfall chart if VBA doesn't support it?

The most robust workaround is to build a simulated waterfall chart using a standard Stacked Column chart layout. Since Stacked Column charts are traditional chart types, they fully support all VBA formatting properties.

Will Microsoft update VBA to support these new charts?

Microsoft has historically prioritized updating newer automation frameworks like Office Scripts over legacy VBA. If you need specific properties, it is recommended to submit a request through Microsoft's feedback channels.