Keep Negative Chart Columns Colored in Excel 2013
Question details
Keep negative columns in a clustered column chart at their chosen fill color after reopening the workbook.

- Product
- Microsoft Excel 2013
- Device & OS
- not provided
- Scenario
- A workbook contains a clustered column chart with Invert if negative enabled for a data series.
- Observed behavior
- Excel resets the fill color of negative columns when the workbook is reopened.
Identify the worksheet, chart object, and data series that need the negative color. The workaround requires a macro-enabled workbook and permission to run trusted macros.
Restore the negative fill color when the workbook opens
Use a Workbook_Open macro to reapply the negative series color whenever Excel opens the workbook.
This workaround addresses an Excel 2013 chart-formatting issue by setting the series fill BackColor after the workbook opens. The macro must be allowed to run for the color to be restored.
Select the chart and use the Name Box to check its chart object name. Note the worksheet tab name and the position of the affected series; SeriesCollection(1) refers to the first series.
Press Alt+F11 to open the Visual Basic Editor. In the Project Explorer, expand your workbook and double-click ThisWorkbook.
Paste the following code into ThisWorkbook, replacing Sheet1, Chart 1, the series number, and the RGB values with your own settings: Private Sub Workbook_Open() With ThisWorkbook.Worksheets("Sheet1").ChartObjects("Chart 1").Chart.SeriesCollection(1) .InvertIfNegative = True .Format.Fill.BackColor.RGB = RGB(255, 0, 0) End With End Sub
Choose File > Save As and save the file as an Excel Macro-Enabled Workbook (.xlsm). Close and reopen it, enable macros if you trust the file and are prompted, and check the negative columns.

Work with column charts in WPS Spreadsheet
WPS Office is a free, lightweight alternative with a familiar interface for opening spreadsheets and working with charts. Its Excel format compatibility can make migration easier, although it does not repair the Excel 2013 issue.

Frequently Asked Questions
Why does my negative column color change after I reopen the workbook?
This can be an Excel 2013 chart-formatting issue, even when Invert if negative is enabled. A Workbook_Open macro can reapply the series BackColor when the file opens.
Where do I put the Workbook_Open code?
Press Alt+F11, find your workbook in the Project Explorer, and open ThisWorkbook. Place the code there, not in a standard module.
Why does the color still reset after I add the macro?
Check that the file is saved as .xlsm, macros are permitted for the trusted file, and the worksheet name, chart object name, and series number in the code match the chart.
Will an online Office repair necessarily fix this issue?
Not necessarily. The reported issue persisted after an online repair, so use the workbook-open workaround if the negative color still resets.
Can WPS Spreadsheet fix Excel 2013's chart-formatting bug?
No. WPS Spreadsheet is an alternative for working with Excel files, not a repair for Excel 2013. Check chart appearance and macro compatibility before relying on an existing workbook in WPS.




