How to Automatically Prevent Overlapping Chart Data Labels in WPS Spreadsheets with VBA
Question details
The user wants to use VBA to identify overlapping data labels on a stacked bar chart and move them automatically after chart data refreshes, without manual repositioning.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A stacked bar chart is refreshed with new data in WPS Spreadsheets and its data labels collide or sit too close to each other, reducing readability.
- Observed behavior
- After refreshing the chart data, labels overlap or intersect, and they do not reposition automatically.
Open the target workbook in WPS Spreadsheets, press Alt+F11 to confirm that the VBA editor opens, and save the file in a macro-enabled format so the VBA module can be stored and run.
Create a VBA Macro to Detect and Reposition Overlapping Data Labels Automatically
Use this method when the chart updates frequently and you want labels to be moved automatically every time the VBA macro runs.
WPS Spreadsheets can run VBA-compatible macros that inspect the chart object, series, and data labels. The macro compares each label's Left, Top, Width, and Height values; when an intersection is detected with another visible label, it moves the label until the overlap is cleared.
Label behavior can differ by chart type and WPS Office version, so run the macro on a copy of the chart and adjust the movement increment or direction for your layout.
In WPS Spreadsheets, press Alt+F11. In the Project pane, click Insert > Module to create a new standard module.
Paste a macro named AdjustDataLabels that sets a ChartObject, loops through each Series in the chart, checks whether the series has data labels, and calls a CheckOverlap function that compares each label's Left, Top, Width, and Height properties.
Inside the loop, use Do While CheckOverlap(...): label.Left = label.Left + 5: Loop so a label that still intersects another label is shifted to the right before the next check.
Return to the worksheet, refresh the chart source data, press Alt+F8, choose AdjustDataLabels, and click Run. Test the chart to confirm labels no longer overlap.
Use Built-in Data Label Placement as a Quick Pre-Check
Use this manual alternative when you have only a few conflicting labels or want to reduce overlap before running the VBA routine.
Automatically Keep WPS Spreadsheets Chart Data Labels Readable with VBA
Use WPS Spreadsheets' VBA-compatible editor to run a macro that checks data label positions and moves overlapping labels after chart refreshes, so you can keep Excel-compatible charts tidy without manual label dragging.
- 1. Download and open WPS Office: Download WPS Office from the official WPS site, install it, and open your macro-enabled WPS Spreadsheets workbook.
- 2. Enable macro content and open the VBA editor: From the workbook, enable macros if prompted, then press Alt+F11 to open the VBA editor.
- 3. Insert the overlap-check macro: Insert a module and paste the AdjustDataLabels and CheckOverlap macro that compares data label positions and shifts overlaps.
- 4. Run after chart refresh: Refresh the chart data, press Alt+F8, select AdjustDataLabels, and click Run to automatically reposition overlapping labels.
Frequently Asked Questions
Can WPS Spreadsheets run Excel VBA macros for chart data labels?
Yes, WPS Spreadsheets provides a VBA-compatible editor. Press Alt+F11 to open it, insert a module, and run macros that use the Excel object model such as ChartObjects, SeriesCollection, and DataLabel.
Why do my data labels still overlap after running the macro?
The movement increment may be too small, the macro may not be checking all series, or the chart type may not expose label Left, Top, Width, and Height in the same way. Increase the increment and run the macro after the chart source data has refreshed.
Do I need to save the workbook in a macro-enabled format?
Yes. Save the WPS Spreadsheets file in a macro-enabled format so the VBA module is retained and the macro remains available after closing and reopening the workbook.
Can this VBA method be used for other chart types?
The same loop and CheckOverlap logic can be adapted for other charts, but label behavior differs by chart type. Test it on a copy and adjust the label movement direction as needed.
Is manually moving labels an alternative if I do not want to use VBA?
Yes. Select the chart, open Format Data Labels, and try a different label position such as Outside End, or click individual labels to move them manually. This is faster for a few labels but not automatic after refresh.
