The Distorted Data Visualizations
When you build a dashboard in Microsoft Excel, you spend a significant amount of time carefully sizing and aligning your charts, graphs, and slicers so they fit perfectly within the viewable area of the screen. However, by default, Excel ties the physical dimensions of these floating objects to the cells located directly behind them. If you adjust the width of a column to accommodate a long string of text, or hide a row to temporarily suppress some data, any chart resting on top of those cells will automatically stretch, squash, or collapse. This completely ruins the formatting of your dashboard, turning a perfect circle chart into an unreadable oval.
How to Lock a Chart’s Size and Position
You can sever the link between the spreadsheet cells and the floating objects, ensuring your charts remain exactly the size and shape you designed them to be, regardless of how you manipulate the underlying grid.
1. Open your Excel workbook and navigate to the sheet containing your chart.
2. Right-click directly on the outer border of the chart. (Be careful not to click inside the chart plot area; you want to select the entire object container).
3. A context menu will appear. Look towards the bottom and select Format Chart Area…
4. A formatting pane will slide out on the right side of your Excel window.
5. At the top of this pane, click on the icon that looks like a small green square with sizing handles (the Size & Properties tab).
6. Click on the Properties section to expand it and reveal the positioning options.
7. You will see three radio buttons. By default, “Move and size with cells” is selected.
8. Click the radio button next to Don’t move or size with cells.
Locking Multiple Charts Simultaneously
If you have a complex dashboard with five different graphs, fixing them one by one is tedious. You can apply this setting to all of them at once.
1. Hold down the Ctrl key on your keyboard.
2. While holding Ctrl, click the outer border of every single chart, graph, or image on your sheet. You will see selection handles appear around all of them.
3. With all objects selected, right-click the border of one of them and choose Format Object…
4. Navigate to the Size & Properties tab, expand Properties, and select Don’t move or size with cells.
You can now freely resize, hide, or delete rows and columns anywhere in your workbook without ever distorting your carefully designed data visualizations.