How to Duplicate Chart in Excel with Different Data

Duplicating a chart in Excel but pointing it to different data sounds simple, but it's one of those tasks that can turn into a frustrating series of clicks. You’ve spent ages perfecting a chart - getting the colors, fonts, and labels just right - and now you need to create the exact same chart for a new month's sales data or a different marketing campaign. This tutorial will show you several ways to efficiently copy your chart format and connect it to a new data set, from the quick-and-dirty drag method to more robust, time-saving techniques.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Replicate a Chart with New Data?

Before we get into the "how," let's quickly touch on the "why." Manually rebuilding a chart for every new report is a huge time sink. The real benefits of duplicating come down to three things:

• Consistency: When you present data, consistency is professional. Using the same chart style across all your reports (e.g., monthly sales reports, weekly campaign performance) makes the information easier for your audience to interpret at a glance.
• Speed: Once you have a master chart formatted, reusing it is infinitely faster than starting from scratch. You save yourself the tedious work of re-selecting colors, adjusting axis labels, and adding data callouts.
• Comparison: Placing two identically styled charts side-by-side, each showing a different data set (like Q1 vs. Q2 sales), is a powerful way to visualize performance and spot trends.

Let's say you have sales data for January and February. You've already built a beautiful column chart for January's performance, and now you need one for February.

The Foundation: Structure Your Data Correctly

The key to a smooth process is making sure your new data is structured identically to your original data. If your January sales data is in two columns, "Product Category" and "Sales," your February data should be in the same two-column format. Having mismatched formats is where most of the trouble starts.

Here’s an example of well-structured data for this task:

Original Data (January Sales):

New Data (February Sales):

With your data prepped, let’s explore the best methods for duplicating your chart.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Method 1: The Copy and Paste and Drag Method

This is the quickest and most intuitive method, perfect for simple charts and datasets located close to each other on the same worksheet.

This is probably the first thing everyone tries, and for good reason - it’s fast. Here's how it works.

Step-by-Step Instructions:

1. Copy the chart: Click on the border of your original chart (the one connected to the January data). Use the shortcut Ctrl+C (or Cmd+C on a Mac) to copy it.
2. Paste the copy: Click on an empty cell where you want your new chart to go and press Ctrl+V (or Cmd+V on a Mac). You now have an exact, identical copy of the first chart, pointing to the same data.
3. Select the new chart: Click on the duplicated chart. When you do, you'll see the source data outlined in colored boxes.
4. Drag the selection: Hover your mouse over the edge of one of the colored boxes. Your cursor will turn into a four-headed arrow. Click and drag the entire selection box to cover your new data set (the February sales).

That’s it! Excel will automatically update the chart to reflect the new data while keeping all of the original formatting. The chart title might need a manual update (e.g., changing "January Sales" to "February Sales"), but the colors, styles, and data connection are all adjusted for you.

Pros and Cons of This Method:

Its main advantage is speed. For simple, adjacent data sets, it’s a two-step process. However, it can become clumsy if your data is on another sheet or if the data ranges are complex. Dragging isn’t always precise, and you might accidentally miss a row or column, leading to inaccurate visualizations.

Method 2: Using the 'Select Data' Menu for Precision

This method is more deliberate and offers greater control. It's the best option when your new data is on a different worksheet or your chart contains multiple data series.

If the drag-and-drop method feels a bit sloppy, or if your new data isn't conveniently located next to the original, the 'Select Data Source' menu is your best friend. It gives you precise control over which cells are driving your chart.

Step-by-Step Instructions:

1. Copy and paste the chart: Follow steps 1 and 2 from the previous method to create a duplicate of your original chart.
2. Open the 'Select Data' Menu: Right-click anywhere on the new chart (the copy) and choose "Select Data" from the context menu. This will open the 'Select Data Source' window.
3. Update the 'Chart Data Range': At the top of the window, you'll see the 'Chart data range' field. This field contains the current cell references (e.g., =Sheet1!$A$1:$B$5). Simply click inside this box, delete the old range, and then select your new range of data (e.g., the February sales table). Excel will show a live preview of the updated chart. Click "OK" when you're done.

Alternatively, if you have a more complex chart with multiple series, you can edit them individually:

• In the 'Select Data Source' window, on the left side under 'Legend Entries (Series)', you'll see each data series listed. Select the series you want to change and click the "Edit" button.
• A smaller 'Edit Series' window will pop up.
• For 'Series name', you can change what the series is called in the legend. For 'Series values', delete the old range and select the new column of data values.
• Click "OK" to apply, and repeat for any other data series.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Pros and Cons of This Method:

This approach is extremely accurate. It eliminates the guesswork of dragging and makes it easy to pull data from anywhere in your workbook. The downside is that it requires more clicks and a bit more understanding of how Excel references data series, but it’s a skill worth learning for anyone who regularly works with charts.

Method 3: Creating a Chart Template (.CRTX)

This is the most scalable and professional method. It's perfect for when you need to create the same style of chart repeatedly for different reports over time.

If you've designed a chart that perfectly matches your company's branding and you know you'll need to reuse it again and again, save it as a template. This allows you to apply your custom design to any new data set in just a few clicks.

Step-by-Step Instructions:

1. Perfect your master chart: Create and format one chart exactly how you want it. This will be your template.
2. Save it as a template: Right-click on your perfectly formatted chart and choose "Save as Template...".
3. Name your template: A dialog box will appear. Give your template a memorable name, like "Monthly Sales Report - Column Chart," and click "Save." Excel saves this as a .crtx file in a special Templates folder.
4. Apply the template to new data: Now, for the magic. Select your entirely new data set (e.g., your February sales data).
5. Go to the Insert tab on the ribbon. Click on "Recommended Charts" and then select the "All Charts" tab in the new window.
6. On the left-hand pane, you'll see a folder named "Templates." Click on it.
7. Select your saved custom chart template and click "OK."

Excel will instantly create a brand-new chart based on your selected new data, with all of your custom formatting perfectly applied.

Pros and Cons of This Method:

Templates are the gold standard for branding and consistency. It’s slightly more work upfront, but it saves immense amounts of time in the long run, especially for recurring reports. There are no real cons, other than remembering where to find the templates menu.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Pro Tip: Using Dynamic Named Ranges to Automate Chart Expansion

This is a slightly more advanced technique, but it solves a common problem: having to manually update your chart range every time you add a new row of data.

Imagine a chart tracking your sales by month. Every month, you add a new row to your data table. With the methods above, you'd have to resize the data range each time. A dynamic named range automatically expands to include new data, and your chart will update itself.

Here’s how to set one up:

1. Go to the Formulas tab and click on "Name Manager."
2. Click "New..." to create a new named range.
3. In the 'Name:' field, give it a name like SalesData.
4. In the 'Refers to:' field, this is where the magic happens. You’ll use a formula with the OFFSET and COUNTA functions. For a sales data list that starts in cell $B$2 and a labels column in $A$2, the formula might look like this:

=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$A$2:$A$100),1)

Let's break that down:

• OFFSET(Sheet1!$B$2,0,0... starts at cell B2.
• ...COUNTA(Sheet1!$A$2:$A$100)... counts the number of non-empty cells in the 'Month' column to determine how many rows of data there are.
• ,1) specifies that the range is one column wide.

You would create a similar named range for your labels (e.g., SalesLabels). Now, when you go to the 'Select Data Source' menu for your chart, instead of a static cell reference like =Sheet1!$B$2:$B$13, you use your named range like =Sheet1!SalesData.

From now on, whenever you add a new month of sales to the table, the named range will automatically expand, and so will your chart. No manual updates required.

Final Thoughts

You now have a full toolset for re-using Excel charts, allowing you to create consistent, professional reports quickly. From a simple copy and paste for quick updates to saving reusable templates for long-term consistency, you can pick the method that best fits your workflow and stop rebuilding charts from scratch.

While mastering these tricks in Excel is a big step up, you'll find that once dashboards start getting more complex, a lot of time is still spent on manual reporting - connecting different data sources, building charts one by one, and ensuring they all stay up-to-date. This is where we designed Graphed to remove the friction. Instead of manually copying charts, you can simply connect your live data sources and describe the dashboard you want in plain English. Graphed instantly builds live, interactive dashboards that update automatically, so you can spend your time acting on insights instead of just gathering them.