1. Mastering Static Data Visualization: Secure Your Charts in Microsoft Excel

If you insert or resize columns and rows in a spreadsheet, the position and size of your charts change. Charts automatically relocate because they are set to move and resize with cells. Fortunately, you can lock the position of a chart in Microsoft Excel.

This kind of scenario is common in reports and dashboards. When a user selects an item in a Slicer to filter data, Excel resizes columns to fit its contents. And because the chart overlays these columns, its size too is adjusted.

To lock the position of a chart, right-click on the item and select the “Format Chart Area” option found at the bottom of the pop-up menu.

If you do not see the option to format the chart area, you might have clicked on the wrong part of the chart. Ensure the resize handles are around the border of the chart. This confirms that the chart area is selected.

In the Format Chart Area pane, click the “Size & Properties” icon.

You might need to click the arrow to expand the “Properties” settings if they are not already visible.

There are two useful options here. Click “Don’t Move Or Size With Cells” to fully lock the chart against being resized by Slicer selections or moved by users adding or removing columns.

Alternatively, there is also an option to “Move But Don’t Size With Cells” if that fits your needs better.

Related: How to Create Custom Charts for Better Excel Spreadsheets

Also read: