How to Move a Chart to a New Sheet in Excel? (Also VBA Code)

Charts are a great way to visualize data. Since we’re dealing with visualizations, the placement of these charts also matters. Oftentimes, it’s better to separate the raw data from the charts to make our dashboards clean and more appealing. In this article, I’ll show you ways to do just that.

Move the Chart to a New Sheet as a Chart Object

To move the chart to a new sheet as a chart object:

Click on a blank space on your chart to select it.

1. Click on a blank space on your chart to select it.

2. Once the entire chart is selected, click Chart Design from the Excel ribbon.

Go to the 'Chart Design' tab from the Excel ribbon and click 'Move Chart'.

Go to the Location section and click Move Chart.

3. The Move Chart menu will appear.

Steps to move chart as an object to a new sheet.

Tick the Object in option.

From the dropdown menu, select the sheet where you want to move the chart and click OK.

NOTE: If you haven’t yet added that sheet, close the menu first, insert a new sheet, and redo the steps.

4. Once you click OK, you will be redirected to the specified sheet.

Sample output after moving chart as an object to a new sheet.

You’ll then see the chart that you have previously selected.

Drag the chart to wherever you like to position it inside that sheet. And that’s it! You’re all set.

Move the Chart to a Chart Sheet

If you prefer a designated sheet for your chart, then it would be best to move the chart to a Chart Sheet.

Example of a chart in a chart sheet.

A chart sheet is like a regular worksheet, except that a chart sheet has no gridlines, making it easy to read through the chart.

The chart inside it is also magnified and centered. Anyone can easily go over it.

The chart cannot be resized by the users. It cannot even be deleted. If you want to delete the chart, you must delete the sheet itself because it is directly linked to it.

Setting up a chart sheet is perfect if you want your target audience to be able to focus on the chart and examine it without any distractions.

To move your chart to a chart sheet:

1. Right-click on the chart. When the pop-up menu appears, select Move Chart.

Right-click on chart and select 'Move Chart' from the menu that pops up.

2. The Move Chart menu will appear.

Steps to move chart to a chart sheet.

From the list of options, select New Sheet.

Enter the name you want for this chart sheet and click OK.

3. And that’s it! You now have your chart added to a chart sheet.

Sample output after moving chart to a chart sheet.

Move the Chart to a New Sheet Using the Cut and Paste Method

If you prefer doing things with keyboard shortcuts, then you will like this method.

To transfer a chart from one sheet to another, simply select the chart and press CTRL + X to cut it.

Click the chart and press CTRL + X to cut it.

Once it’s cut, go to the sheet where you’d like to transfer it.

Click on the cell where you’d like to position your chart. Once selected, press CTRL + V to paste.

Steps to paste the chart that was cut.

And that’s it! You have successfully transferred the chart onto the new sheet.

Sample output after applying the cut and paste method.

Move Multiple Charts to a New Sheet in One Go Using VBA

If you have multiple charts spread throughout different sheets in your workbook and you want to place them all in just one sheet, then this method is perfect for you.

This approach is great for creating a dashboard because you can transfer all sheets in one go. No need to go through each sheet to transfer each of them. 

To do this:

1. While your workbook is open, press ALT + F11 to open the VBA Editor.

2. Then, from the Insert menu, select Module.

Steps to insert a new module.

3. Copy the following code and paste it onto the new module added. 

Option Explicit

Sub MoveAllChartsToOneSheet()
'moves all charts inside the workbook in one worksheet

    Dim chrtObj As Object, ws As Worksheet, sheetName As String, chartWS As Worksheet
    sheetName = "All Charts"   'note: change the text inside the double quotes to change the name of the worksheet where all charts will be moved to.
    On Error Resume Next
    Set chartWS = ThisWorkbook.Worksheets(sheetName)
    If Err.Number > 0 Then
        ThisWorkbook.ActiveSheet.Name = sheetName
    End If
    On Error GoTo 0
    Set chartWS = ThisWorkbook.Worksheets(sheetName)
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> sheetName Then
            For Each chrtObj In ws.ChartObjects
                chrtObj.Chart.Location xlLocationAsObject, sheetName
        End If
End Sub
Copy code to the new module added.

This macro will automatically create a new sheet named “All Charts” and place all the charts inside it.

If you want to use a different name for the worksheet (e.g., Dashboard), edit the code and change the text assigned to sheetName.

The worksheet name is highlighted in the code.

Remember to enclose the name in double quotes (“”).

4. Now, it’s time to run the code.


Note that once you run the code, the changes cannot be undone. As a precaution, you can create a copy of the workbook first before running the code so that you’ll have a backup copy.

To run the macro, click anywhere inside the code. You should see the text cursor blinking inside the macro.

Click anywhere on the code before running it.

Next, press F5 to run it.

After a few seconds, close the VBA Editor and go back to your workbook.

5. You should now see all the charts on top of each other in the “All Charts” worksheet (if you have not renamed it).

Sample output after running the macro.

6. Click on each chart and drag them across the sheet to position them wherever you want them to be.

Sample output after moving the charts and resizing them to have them fit in the worksheet.

7. Also, as a pro tip, you might want to remove the gridlines from the worksheet to make it look clean, making the charts easier to read.

To do this, click the View tab from the Excel ribbon and uncheck the Gridlines checkbox under the Show section.

Steps to hide gridlines in worksheet.

That’s it! All the gridlines should now disappear from your sheet.

Sample output after removing the gridlines.

8. When you close or save the file, you might encounter the following prompt:

The prompt that appears when you try to save a .xlsx file with the macro added.

If you no longer need to run the code next time, click Yes to continue. Doing so will remove the macro from your file.

On the other hand, if you want to save the file with the code, click No and select .xlsm or .xlsb as the file format.


Excel charts are perfect for summarizing long rows of data. They give our targeted audience a glimpse of “what’s happening” through these graphical representations.

Organizing these charts and placing them on specific sheets can significantly increase the professional appeal of your workbook. I hope the suggested methods above help you do so with ease.

Leave a Comment