Fix Excel Chart Marker Formatting Lost After Filtering
Question details
The user needs to prevent custom chart marker formatting from disappearing after filtering data, saving, and reopening the workbook.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user applies custom markers (images from a file) to an Excel chart, but the markers vanish when data filters are applied, the file is saved, and then reopened.
- Observed behavior
- Custom chart markers derived from external files disappear and fail to reload when filters are toggled, even though Excel's built-in standard markers function correctly under the same conditions.
Ensure your workbook is saved to prevent accidental data loss before modifying application-level settings. Confirm whether your chart uses Excel's default marker shapes or custom image files.
Disable Chart Data Point Properties Tracking
Adjust Excel's advanced settings so that formatting properties do not strictly follow data points, which frequently resolves marker disappearance issues.
Excel has an advanced setting that tracks custom formatting for individual data points. When data is filtered and hidden, this tracking can sometimes fail for custom images, causing the formatting to be lost upon reopening. Disabling this feature stops Excel from dropping the format when the data point is hidden.
Click on the 'File' tab in the top-left corner of the Excel ribbon and select 'Options' located at the bottom of the menu pane.
In the Excel Options dialog box, select 'Advanced' from the left-hand sidebar menu.
Scroll down the right pane until you find the 'Chart' section. Uncheck both 'Properties follow chart data point for current workbook' and 'Properties follow chart data point for all new workbooks'.
Click 'OK' to apply the changes. Save your workbook, close Excel, and reopen the file to verify if the chart markers are retained when filtering.
Switch to Built-in Marker Styles
If custom external images continue to drop out due to Excel limitations, switching to built-in markers provides a stable alternative.
Try WPS Office for Stable Chart Formatting
If Microsoft Excel continues to drop your custom chart marker formatting due to filtering glitches, consider switching to WPS Office. It provides a robust, free, and highly compatible alternative with a stable chart rendering engine.
- 1. Download and Install WPS Office: Visit the official WPS Office website to download the free suite and follow the straightforward installation instructions.
- 2. Open Your Excel Workbook: Launch WPS Spreadsheet and easily open your existing .xlsx file without worrying about layout corruption.
- 3. Manage Data Safely: Apply custom chart markers and use data filters in a stable environment where visual formatting remains intact.
Frequently Asked Questions
Why do my custom image markers disappear in Excel charts?
Excel occasionally struggles to retain custom image markers sourced from external files when data points are hidden and revealed using filters. The graphical link to the image can break when the chart refreshes, causing it to revert to a default or invisible state.
What does 'Properties follow chart data point' actually do?
This setting dictates whether custom formatting (like a specific color or border manually assigned to a single data point) moves with that data point when the underlying data is sorted, filtered, or rearranged.
Can I recover my chart formatting without manually rebuilding it?
If the formatting is lost after saving, you generally must reapply it. To speed up future recovery, configure your chart exactly how you want it, right-click it, and select 'Save as Template'. You can then quickly apply this template to restore the look.
Are built-in markers more reliable than custom image markers?
Yes, built-in markers are generated natively by the spreadsheet application's rendering engine. They do not rely on external graphic elements, making them immune to the drop-out bugs that affect custom image files during filtering.
