How to Format Waterfall and Treemap Charts with VBA in Excel
Question details
Users need to automate the formatting of built-in waterfall and treemap charts in Excel using VBA.

- 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.
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.
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.
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.
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).
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.

Build Custom Charts Using Standard Chart Types
Construct a simulated waterfall chart using standard stacked column charts which have full VBA support.
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. Download and Install: Get WPS Office for free from the official website and install it on your device in just a few minutes.
- 2. Open Your Excel File: Launch WPS Spreadsheets and open your existing .xlsx workbooks directly without any conversion or data loss.
- 3. Edit and Visualize Data: Enjoy familiar charting tools and straightforward data analysis without the steep learning curve.

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.




