How to Create a Pipe Defect Chart with Colored Boxes in Excel
Question details
The user wants to visualize pipeline defects as colored rectangular boxes on a chart, accurately positioned by axial distance, defect length, circumferential center, width, and type.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Mapping pipeline inspection data using specialized spatial parameters (axial distance, length, circumference, width, and defect category).
- Observed behavior
- The user needs a charting method that plots precise rectangular shapes on a fixed 0-360 degree Y-axis, which standard Excel chart types cannot handle natively.
Ensure your dataset is organized into clear columns for axial distance, defect length, circumferential center, width, and defect type, and verify that the Developer tab is enabled in your ribbon to use VBA macros.
Use a Custom VBA Charting Macro
Use a VBA macro to draw accurately sized and positioned colored shapes on a base scatter plot.
Standard Excel charts, like bubble charts or bar charts, cannot draw precise rectangular boxes based on four independent variables (X, Y, width, length). A VBA script allows you to loop through your dataset and draw custom shapes directly onto a chart object, matching your exact axial and circumferential dimensions.
Go to File > Options > Customize Ribbon and check the Developer box in the right pane to enable macro creation.
Insert a blank Scatter chart. Right-click the Y-axis, select Format Axis, and fix the Minimum bound to 0 and Maximum bound to 360. Adjust the X-axis maximum to match your pipeline's total axial distance.
Open the VBA editor (Alt + F11) and insert a new module. Write a macro that uses 'ActiveChart.Shapes.AddShape(msoShapeRectangle, ...)' inside a loop. Map your data columns to the shape's Top, Left, Width, and Height properties relative to the chart's plot area.
Add conditions in your macro to color-code the shapes based on the defect type column using the '.Fill.ForeColor.RGB' property. You can also automate adding text boxes and connector lines to identify related defects.

Use a Bubble Chart as an Approximation
Plot the defects using a standard Bubble chart if using VBA macros is not an option.
Visualize Pipeline Data with WPS Spreadsheet
WPS Spreadsheet offers robust chart formatting tools and full VBA/Macro support, allowing you to create complex, customized visualizations like pipe defect charts just as you would in Microsoft Excel.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your pipeline defect dataset in WPS Spreadsheet.
- 2. Insert the Base Chart: Navigate to the Insert tab, select Scatter chart, and format your Y-axis boundaries to 0-360 degrees.
- 3. Apply VBA Macros: Use the Developer tab (available in macro-enabled editions) to run your custom shape-drawing script and generate the colored defect boxes.

Frequently Asked Questions
Can I use a standard Excel chart to draw colored boxes for pipe defects?
No standard Excel chart natively supports drawing specific rectangular boxes based on four independent dimensions (X, Y, width, length). A Bubble chart is the closest built-in alternative, but a custom VBA macro is required for precise rectangular plotting.
How do I fix the Y-axis to 360 degrees for circumferential measurement?
Double-click the Y-axis on your chart to open the Format Axis pane. Under the Axis Options section, set the Minimum bound manually to 0 and the Maximum bound manually to 360.
How can I color-code defects by their type automatically?
If using a VBA macro, you can add a 'Select Case' statement in your script to change the '.Fill.ForeColor.RGB' property based on the defect type value. If using a standard Bubble chart, you must separate each defect type into its own individual data series.




