Chart & Visualization Issues

Keep Negative Chart Columns Colored in Excel 2013

Elise WilliamsElise Williams Nov 22, 2026 868 views

Question details

Keep negative columns in a clustered column chart at their chosen fill color after reopening the workbook.

Keep Negative Chart Columns Colored in Excel 2013
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.
Before you start

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.

Solution 1Recommended

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.

1
Check the chart names

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.

2
Open the workbook module

Press Alt+F11 to open the Visual Basic Editor. In the Project Explorer, expand your workbook and double-click ThisWorkbook.

3
Add the open-event macro

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

4
Save and test the workbook

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.

Restore the negative fill color when the workbook opens
If the workbook already has an open event: Add the With block to the existing Workbook_Open procedure instead of creating a second procedure with the same name. Repeat the block for other affected chart series.
Free Microsoft Office alternative

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.

Create and format column charts in WPS Spreadsheet.Open and save common Microsoft Excel formats, including XLSX and XLS.Use a familiar spreadsheet interface that makes switching easier.Access a free, lightweight Office alternative.
microsoft office alternative - wps office

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.