How to Lock a Chart in Excel

Nothing disrupts a carefully crafted Excel dashboard faster than a chart that moves when you sort your data or insert a new row. Keeping charts perfectly aligned is essential for professional, easy-to-read reports. This guide will walk you through exactly how to lock your charts in place, so your dashboards and reports always look clean and stay organized, no matter how a user interacts with the underlying data.

Why Should You Lock a Chart in Excel?

Pinning down your charts might seem like a small detail, but it has a big impact on the usability and professionalism of your spreadsheet. When you lock a chart, you keep your dashboard layout exactly as you designed it, preventing accidental changes that can make reports confusing or appear sloppy.

Here are the key reasons why locking a chart is a best practice:

Prevent Accidental Movement: The most common issue is charts shifting or resizing when someone sorts a data table, applies a filter, or deletes rows and columns. Locking prevents this from happening.
Maintain Dashboard Integrity: For dashboards with multiple charts, tables, and slicers, alignment is crucial. One out-of-place chart can throw off the entire visual flow. Locking ensures all your visual elements stay put.
Improve User Experience: When you share a report, you want users to focus on the insights, not on fixing a broken layout. A locked worksheet provides a stable and predictable experience for other people on your team.
Save Time on Readjustments: Constantly repositioning and resizing charts after every data update is a tedious and unnecessary time sink. Lock them once and save yourself the hassle of repeated manual fixes.

Two Main Ways to Lock a Chart in Excel

Excel offers a couple of different methods to control chart behavior. They serve distinct purposes, and you can even combine them for maximum control. Your choice depends on exactly what you’re trying to prevent: accidental shifts from data changes or direct user edits to the chart itself.

Adjusting Chart Properties: This method controls how the chart reacts to changes in the cells behind it - like hiding, resizing, or inserting rows and columns. This is the most common and direct way to "lock" a chart's position.
Using Worksheet Protection: This is a more robust method that prevents users from selecting, moving, resizing, or editing the chart object itself. This is ideal for when you're preparing a final report for others to view.

Let’s go through each method with step-by-step instructions.

Method 1: Locking Chart Position with Format Properties

This is the go-to solution for preventing your chart from resizing or shifting when rows and columns are changed. Excel provides three options that define how a chart is tied to the worksheet cells underneath it.

Step 1: Select Your Chart

First, click anywhere on the border of the chart you want to lock. Make sure the entire chart object is selected, not just an individual element within it like the title or a data series.

Step 2: Open the "Format Chart Area" Pane

With the chart selected, right-click on its border (the outer edge of the chart area, not the chart itself). From the context menu that appears, select "Format Chart Area."

This will open a task pane on the right side of your Excel window, giving you access to all the chart's formatting settings.

Step 3: Navigate to "Size & Properties"

In the "Format Chart Area" pane, look for a small icon that looks like a cube or a square with arrows, typically labeled "Size & Properties." Click this icon to expand the properties menu associated with the chart object's size and positioning.

Step 4: Choose Your Chart Locking Option

Under the "Size & Properties" menu, you will see a section called "Properties." Here you'll find three radio buttons that determine how your chart behaves. Here's what each one does:

Move and size with cells (Default): This is the standard setting. If you insert rows or columns that push the cells beneath the chart down or to the right, the chart moves and resizes with them. If you adjust the width or height of the cells the chart is sitting on, the chart will stretch or shrink. This is typically what people want to avoid.
Move but don’t size with cells: This option provides a middle ground. The chart's position is still tied to the cells, if you insert rows above it, the chart will move down to not overlap with your new content. However, its dimensions remain fixed. It won't get wider or taller if you resize the underlying columns and rows. This is useful for maintaining a consistent chart size while allowing it to flow with page content.
Don’t move or size with cells: This is the option to choose to fully lock the chart’s position and size relative to the spreadsheet grid. The chart behaves like a floating object. It will not move or resize no matter how you filter, hide, sort, add, or delete the cells around or beneath it.

Select "Don’t move or size with cells" to completely lock your chart in place.

Method 2: Using Worksheet Protection to Prevent Edits

While the first method prevents charts from reacting to cell changes, it doesn’t stop a user from clicking on the chart and accidentally (or intentionally) moving, resizing, or deleting it. That's where worksheet protection comes in.

This method works by locking all objects on a sheet - including charts - while still allowing users to interact with specific cells that you designate as "unlocked."

Step 1: Unlock Any Cells Users Need to Input Data In

This step is critical and often overlooked. By default, every cell in Excel is set to a "Locked" state. This setting does nothing until you turn on worksheet protection. To ensure your chart can still update dynamically with new data, you must first unlock the cells where data might be entered or changed.

Select the cells or range of cells where users should be able to enter or modify data.
Right-click the selected cells and choose "Format Cells."
In the Format Cells dialog box, go to the "Protection" tab.
Uncheck the box next to "Locked" and click OK.

Step 2: Set the Chart's Object Properties

Before protecting the whole sheet, make sure your chart itself will not move or resize with cells. Follow the steps from Method 1 to select your chart, go to Format Chart Area > Size & Properties > Properties, and choose "Don’t move or size with cells."

This ensures that even if a user edits your unlocked data cells in a way that shifts rows around, your protected chart won't move.

Step 3: Protect the Worksheet

Now, it’s time to activate the protection for the entire sheet.

Go to the "Review" tab on the Excel ribbon.
Click the "Protect Sheet" button.
A dialog box will appear. At the very top, you have the option to set a password to unprotect the sheet. This is recommended if you need to prevent others from changing the protection settings.
Below the password field, there's a list titled "Allow all users of this worksheet to:" This list determines what a user can do on an otherwise protected sheet.
Ensure the box for "Edit objects" is unchecked. This is the specific setting that locks down charts and other visual objects.
Typically, you'll want to ensure that "Select locked cells" and "Select unlocked cells" are checked, so users can still click around the sheet and enter data in the cells you unlocked in Step 1.
Click OK.

Your chart is now fully locked. You (and other users) won't be able to select it, move it, delete it, or change its formatting until the sheet is unprotected (by going to Review > Unprotect Sheet and entering the password, if you set one).

However, users can still enter data into your designated unlocked cells. When they do, the chart's source data will change, and the chart's visual representation will update in real-time, all without ever leaving its locked position.

Final Thoughts

Fixing charts in place using the "Don't move or size with cells" property or layering on worksheet protection gives you full control over your dashboard layout in Excel. These simple steps ensure your reports stay clean, professional, and easy to interpret, taking one more manual task off your plate.

Manually building, formatting, and locking reports in spreadsheets is a classic productivity killer. That's why we built Graphed. We wanted to eliminate the hours spent on tedious spreadsheet tasks - like getting a chart to stay put - and automate the entire reporting process. You just connect your data and describe the dashboard you want in plain English, and Graphed builds it in seconds.