Consistent formating of teh graphs

Question

Consistent formating of teh graphs

Chaturvedi, Santosh 400

Hello --Greetings !!

Here is one problem. I have contructed and formated the graph and saved the same.

When i close and open the excel sheet again theformat get distrubs and takes its own colour and format see below.

I had cleaned and format the graph many times butr the when i save and open teh excel again all the format gets distrub. it is very upsetting the case.

Can you suggest that the formated grapg remain same all the timeeven though t open and closes anytime.

See below image..(data is hypothetical)

User's image

0 comments

2 answers

Your answer

Answer 1

Ana Le 2,345 Independent Advisor

Hello,

From the screenshots, the clearest formatting change is the fill color of the data series. However, if both screenshots show the same chart before and after reopening, some data labels also appear to change, for example from 57.87 to 28.94. In that case, the issue may involve both the chart formatting and its source data.

Please first test the following in a copy of the workbook:

Select the affected series, right-click it, and choose Format Data Series.
Under Fill & Line > Fill, select Solid fill.
Choose More Colors > Custom and specify a fixed RGB color. If Vary colors by point is available, make sure it is not selected.
Repeat this for the line or markers if their formatting also changes. Microsoft documents the available chart formatting options here: Change the shape fill, outline, or effects of chart elements⁠.
Use File > Save As and save a test copy as an Excel Workbook (.xlsx). If the workbook contains macros, retain the .xlsm format instead. Other formats may not preserve all Excel formatting and features: File formats that are supported in Excel⁠.
Close all Excel windows, then reopen the test copy.

If the same issue continues, please create a new blank .xlsx workbook, insert a small test chart, apply a custom color, save it, and reopen it. This will help determine whether the behavior is limited to this workbook or also occurs in newly created files.

If the chart values also change after reopening, please let me know whether this is a regular chart or a PivotChart, and whether its source data uses formulas, external links, Power Query, or macros.

Please also provide:

The current file extension
The full Excel version and build shown under File > Account > About Excel
Whether the file is saved locally or in OneDrive/SharePoint
The result of the new blank-workbook test

Ana Le 2,345 Reputation points Independent Advisor

2026-09-02T07:51:25.91+00:00
Thank you for the additional screenshot. It indicates that the chart changes specifically when the grouped rows are collapsed. Please apply both settings below to each affected chart:

Select the chart border, right-click, and choose Format Chart Area.

Open Size & Properties > Properties.

Select Don’t move or size with cells. This prevents the chart itself from shrinking when the grouped rows are collapsed.

With the chart selected, go to Chart Design > Select Data > Hidden and Empty Cells.

Select Show data in hidden rows and columns, then click OK. This keeps the chart plotting its source data when those rows are hidden by the collapsed group.

These are two separate settings: one keeps the chart’s size and position fixed, while the other keeps hidden source data included in the chart.

After applying them, expand the groups, restore the desired formatting once, save the workbook, and test it again with the groups both expanded and collapsed.

Microsoft’s Excel documentation confirms that embedded charts can be configured so they do not move or resize with their underlying cells, and charts can plot both visible and hidden cells:

Chart placement behavior⁠

Plotting visible and hidden cells⁠
Chaturvedi, Santosh 400 Reputation points

2026-09-02T08:37:33.5833333+00:00

Hello -- This is understood and working well
Chaturvedi, Santosh 400 Reputation points

2026-09-02T08:40:22.6966667+00:00

Hello --> Just want to know how to remove the gaps as shows as emty with "-" values

That means the bar graphs must start where the vakues starts, must not show the emty cell with"-" values.

please advice
Ana Le 2,345 Reputation points Independent Advisor

2026-09-03T09:51:19.6166667+00:00
Hello,

The cells containing “-” are not drawn as bars, but their rows are still included as categories on the chart’s horizontal axis. This is why Excel leaves empty space before or after the bars. Changing Gap Width will not remove it.

If those rows should be excluded from the entire chart:

Select the chart and go to Chart Design > Select Data.

Adjust the Chart data range so that it begins at the first row containing an actual value and ends at the last row containing an actual value.

If necessary, edit each series and the Horizontal Axis Labels separately so that all ranges cover the same rows.

Microsoft provides the steps for changing a chart’s source range here: Select data for a chart.

Please note that if another series in the same chart contains real values in those “-” rows, Excel must retain those categories because all series share the same horizontal axis. In that situation, removing the spaces would also remove those points from the other series. A separate chart or a reorganized helper data range would be required.

Also, please select one of the “-” cells and check the Formula Bar. Let me know whether it contains a literal “-”, a formula returning “-”, or a numeric zero displayed as “-”, as the appropriate helper formula depends on that result.
Ana Le 2,345 Reputation points Independent Advisor

2026-09-04T09:32:10.1833333+00:00

Hi, may I know if you have any chance to check my answer? I am glad to help if you have any other questions.

Answer 2

Chaturvedi, Santosh 400

Hello some more update--

User's image

0 comments