How to Refresh a Chart in Excel

You’ve meticulously prepared your data, created a beautiful chart in Excel, and are ready to present your findings. But when you add a new month of sales data or update a few numbers, your chart just sits there, completely unchanged. This common frustration can turn a simple task into a major headache, but the fix is usually quite simple. This guide will walk you through exactly how to get your Excel charts to refresh automatically, from the easiest one-click solution to a best-practice method that will save you time in the long run.

Why Isn't My Excel Chart Updating?

Before we jump into the solutions, it helps to understand why your chart isn't refreshing. An Excel chart is a visual representation of a specific range of cells. If it's not updating, it's almost always because your new data falls outside that original range. Think of it like taking a photo of a group of five friends. If a sixth friend arrives and stands next to the group, they won't be in your picture until you step back and retake the photo to include them. Your Excel chart works the same way, you need to tell it to "step back" and include the new data.

Here are the most common reasons this happens:

• Static Data Range: You selected a fixed range of cells (e.g., A1:B10) when creating the chart. When you type new data into row 11, the chart doesn't know it's supposed to look there.
• PivotChart Lag: If your chart is a PivotChart, it's linked to a PivotTable. The chart won’t update until the PivotTable itself is refreshed to reflect changes in the source data.
• External Data Connections: The chart may be pulling data from another file or a web source. These connections often need to be manually refreshed to pull in the latest information.

Fortunately, you have several ways to solve this, ranging from a quick manual refresh to setting up dynamic charts that update themselves.

Solution 1: The Quick Manual Refresh

Sometimes you just need a fast, immediate fix, especially if you're dealing with a PivotChart or data linked from an external source. Excel has a built-in feature to update all data connections in your workbook at once.

How to Use "Refresh All"

This is often the first thing to try when a chart seems stuck.

1. Go to the Data tab on the Excel ribbon.
2. In the "Queries & Connections" group, click the Refresh All button.
3. You can also use the keyboard shortcut: Ctrl + Alt + F5.

This action forces Excel to re-query all of its data sources, including PivotTables, stock data types, and links to external files. If your chart is based on a PivotTable that hasn't updated, this one click will often solve the problem immediately by refreshing the PivotTable and, in turn, the PivotChart.

Solution 2: Use Excel Tables for Automatic Chart Updates (The Best Practice)

Manually refreshing works, but what if you could set things up so your chart always updates automatically? The best way to do this in modern Excel is by using a feature called Excel Tables. Don't confuse this with just a range of data, a formatted Excel Table is a dynamic object that "knows" how big it is.

When you base your chart on a Table, the chart's data source automatically expands whenever you add new rows or columns to the Table. This is the "set it and forget it" method.

Step 1: Convert Your Data Range into a Table

If you have a plain range of data, converting it to a Table takes only a few seconds.

1. Click anywhere inside your data range.
2. Go to the Insert tab on the ribbon.
3. Click Table. A small "Create Table" dialog box will appear.
4. Excel will automatically guess your data range. Ensure it's correct.
5. Make sure the box for "My table has headers" is checked if your data has column titles (like "Month" or "Sales").
6. Click OK.

Your data range will now be formatted with alternating colors and filter dropdowns. This is now a formal Excel Table.

Step 2: Create Your Chart from the Table

Now, build your chart as you normally would.

1. Click anywhere inside your newly created Table.
2. Go to the Insert tab and choose the chart type you want (e.g., Column, Line, Pie).
3. Excel will instantly create the chart based on the data in your Table.

Step 3: Test the Dynamic Link

This is where the magic happens. Go to the first empty row directly below your Table and start typing in new data. For example, if your last entry was for "June," type "July" in the next row and add its corresponding sales data. You'll notice two things:

• The Table's formatting automatically expands to include your new row.
• Your chart instantly updates to include the "July" data point. No refreshment is needed!

Using Tables is the most reliable and efficient way to ensure your charts stay current without any extra work.

Solution 3: Manually Adjust the Chart’s Data Source

What if you've already created a chart and don't want to convert your data to a Table? You can manually edit the chart's source data range to include the new information.

How to Redefine the Source Data

1. Select the chart. When you click on your chart, you will see a colored outline appear around its source data on your worksheet.
2. Go to Chart Design. With the chart selected, a new menu tab called "Chart Design" will appear on the ribbon. Click on it.
3. Click "Select Data." In the "Data" group, click the Select Data button.
4. Edit the Chart Data Range. The "Select Data Source" dialog box will pop up. At the top, you'll see a field called "Chart data range." This contains the cell reference for your chart's data (e.g., =Sheet1!$A$1:$B$7). You can manually edit this range to include your new rows. For example, if you added three new rows of data, change the range from $B$7 to $B$10.

The Easier Drag-and-Drop Method

A faster way to do this is to use the visual handles on the worksheet.

1. Click your chart once to select it.
2. Look at the source data on your spreadsheet. You will see a colored border around it.
3. Move your mouse to the small square handle on the bottom-right corner of this border. Your cursor will change to a two-sided diagonal arrow.
4. Click and drag this handle downwards to include the new rows of data. As you drag, the chart will update in real-time.
5. Release the mouse button when a new border encompasses all your data.

This method is great for quick adjustments when you've only added a few new data points.

Advanced Method: Creating Dynamic Named Ranges with Formulas

For users who want ultimate control without using Tables, there's a more advanced technique using formulas to create a dynamic named range. This is a named range that automatically expands as you add or remove data. It was the standard method before Excel Tables became popular, and it's still incredibly powerful.

We'll use a combination of the OFFSET and COUNTA functions.

Step 1: Open the Name Manager

1. Go to the Formulas tab and click Name Manager. You can also use the shortcut Ctrl + F3.
2. Click New...

Step 2: Define the Name

1. In the "Name" field, give your range a descriptive name (e.g., SalesData). Avoid spaces.
2. In the "Refers to" field, you'll enter a formula that tells Excel how to find the start of your data and how many rows and columns to include. If your sales numbers are in column B, starting at cell B2, the formula would look like this:

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

1. What this formula does: It starts at cell B2 (Sheet1!$B$2), and COUNTA(Sheet1!$B:$B)-1 counts all the non-empty cells in column B (minus the header) to determine the height of the range.

Step 3: Update Your Chart

Now, go back to your chart. Click it, go to Chart Design > Select Data. In the "Select Data Source" window, edit your data series. For the series values, replace the static range (like =Sheet1!$B$2:$B$10) with your new named range, making sure you include the sheet name: =Sheet1!SalesData.

Now, whenever you add data to column B, the SalesData named range will automatically adjust, and so will your chart.

Final Thoughts

Running into an Excel chart that won't refresh can be a roadblock, but knowing the cause makes the solution clear. For a quick fix, a manual refresh will do the job, but the best long-term strategy is to build your charts from data formatted as an Excel Table. This ensures your visuals always reflect the latest data automatically, keeping your reports accurate and saving you valuable time.

We believe data analysis should be this intuitive - and even faster. Manually updating charts and wrangling spreadsheets is a common time-sink when what you really need is an answer. That's why we created Graphed. By connecting your data sources directly, you can simply ask for the chart you need using plain language - like "Show me a line chart of Shopify sales for the last 90 days" - and get a live, automatically updating dashboard in seconds. No more troubleshooting formulas or redefining data ranges.