How to Edit Source Data in Excel

Your beautiful Excel chart or insightful PivotTable is only as good as the data powering it. When that underlying data changes - whether you’re adding new sales figures for the month or updating project statuses - you need your reports to reflect those changes instantly and accurately. This guide walks you through the essential methods for editing source data in Excel, from simple updates to more powerful, automated workflows.

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

First Things First: Why Correctly Editing Source Data Matters

Working with data follows a simple principle: garbage in, garbage out. If the data you’re analyzing is outdated, incomplete, or formatted incorrectly, any reports, charts, or conclusions you draw from it will be unreliable. Treating your source data with care isn't just a technical exercise, it's about making sure your decisions are based on the truth.

Taking a few moments to manage and edit your data sources correctly saves hours of backtracking and fixing errors down the line. A clean, well-managed data source is the foundation for trustworthy analysis, preventing those "uh-oh" moments when you realize your Q3 sales report was still pulling Q2 numbers.

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

How to Change the Data Source for a Chart or PivotTable

One of the most common tasks in Excel is updating a chart or PivotTable after you've added new rows or columns to your dataset. If you find your visuals aren't updating, you probably need to manually adjust the source range.

Editing a Chart's Data Source

Imagine you have a chart tracking monthly sales, and you’ve just added data for the new month. Here’s how to get your chart to recognize it:

1. Select the Chart: Click anywhere on your chart to activate it. You’ll see new tabs appear in the Excel ribbon at the top, like "Chart Design" and "Format."
2. Find "Select Data": Go to the Chart Design tab. In the "Data" group, click on the Select Data button.
3. Adjust the Data Range: A dialog box called "Select Data Source" will pop up. At the top, you'll see a field labeled "Chart data range." This field shows the current cells your chart is based on (e.g., =Sheet1!$A$1:$B$12).
4. Reselect Your Data: You can either manually edit the range in this box or, more easily, click the small icon next to the field to temporarily collapse the window. Then, use your mouse to click and drag over the entire new data range, including your new rows or columns. Press Enter when you’re done, and then click OK.

Your chart will immediately update to include the new month's data, resizing itself to fit the expanded range.

Editing a PivotTable's Data Source

The process for PivotTables is very similar. Let's say you've added several new entries to your raw data tab and need to include them in your PivotTable analysis.

1. Select the PivotTable: Click any cell inside your PivotTable. This will bring up the PivotTable Analyze (or PivotTable Tools) and Design tabs in the ribbon.
2. Go to "Change Data Source": On the PivotTable Analyze tab, look for the Change Data Source button and click it.
3. Update the Range: The "Change PivotTable Data Source" dialog box will appear, showing the current table or range. Just like with charts, you can either edit the range here directly or collapse the window to re-select your entire data range.
4. Refresh the PivotTable: After you click OK, you still need to refresh your PivotTable for the new data to show up in your values, rows, or columns. Right-click anywhere in the PivotTable and select Refresh (or find the Refresh button on the PivotTable Analyze tab).

The Pro Move: Use Excel Tables for Automatic Updates

Manually changing the data source every time you add data works, but it's tedious and easy to forget. The single best way to avoid this is to format your source data as an Excel Table. Doing this makes your data dynamic, so any charts or PivotTables connected to it will automatically know when you add new information.

This is a true game-changer. Here’s how to set it up:

1. Convert to a Table: Click any cell inside your raw data range. Press the keyboard shortcut Ctrl + T (or go to the Insert tab and click Table). A small dialog box will appear, confirming the data range. Make sure the "My table has headers" box is checked if you have column titles, and click OK.
2. Style It (Optional): Your data will now have new formatting (like alternating row colors). You can change this on the Table Design tab that appears when you click on the table.
3. Build Your Chart or PivotTable: Now, create your chart or PivotTable based on this Table. Excel will reference the table by its name (e.g., Table1) instead of a fixed cell range.
4. Experience the Magic: From now on, whenever you type or paste a new row of data directly below the table, the table's blue border will automatically expand to include it. To update your PivotTable, just right-click and Refresh. Your chart will update automatically without any action needed! You’ll never have to use "Change Data Source" again.

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

How to Manage and Edit Data Linked from External Files

Sometimes, your Excel workbook pulls data from other files. For example, your main dashboard might summarize data from separate sales reports for North America, Europe, and Asia. If one of those file names changes or moves, your links will break.

Here’s how to manage these external links:

1. Navigate to the Data tab in the ribbon.
2. In the "Queries & Connections" group, click on Edit Links. (Note: If this button is grayed out, it means your current workbook has no active links to other files.)
3. In the "Edit Links" dialog box, you'll see a list of every external source file your workbook is connected to. From here you can:

Quick and Essential Data Cleaning Techniques

Editing your source data isn't just about changing numbers, it’s about preparing it for analysis. Raw data exports from other systems are often messy. Here are a few must-know cleaning techniques.

Remove Duplicate Rows

If you suspect your data has duplicate entries (like a customer being listed twice), you can quickly remove them.

Select your data range, go to the Data tab, and click Remove Duplicates. A window will appear allowing you to select which columns to check for duplicate values. If you want to remove rows where the entire entry is identical, just leave all columns checked and click OK.

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

Tackle Blank Cells and Unwanted Spaces

Blank cells can mess with calculations, and rogue spaces can ruin lookups.

• Fill Blanks Fast: Select a column with blank cells, press F5 to open the "Go To" box, click Special..., choose Blanks, and click OK. All blank cells in your selection will be highlighted. Now, without clicking anywhere else, just type 0 (or "N/A") and press Ctrl + Enter. Every blank cell will be filled at once.
• Zap Extra Spaces: Often, data copied from somewhere else has leading or trailing spaces that aren’t visible. This can prevent a VLOOKUP from finding a match. Use the TRIM function to fix this. In a new helper column, enter the formula for the first cell:

=TRIM(A2)

Then, drag this formula down for all your rows. Finally, copy your new, clean column and use "Paste Special" > "Values" to paste it over the original messy column.

Split Data with Text to Columns

Have a column with full names that you need to split into "First" and "Last"? The "Text to Columns" tool is your best friend.

1. Select the column you want to split.
2. Go to the Data tab and click Text to Columns.
3. The wizard will pop up. Choose Delimited if your data is separated by a consistent character (like a space, comma, or dash). Click Next.
4. Check the box for the delimiter your data uses (e.g., "Space"). You'll see a preview of how the data will be split. Click Next.
5. Choose a destination cell (make sure you have empty columns next to your original for the new data to go into) and click Finish.

Final Thoughts

Mastering how to edit and manage your source data is a fundamental Excel skill. By learning how to change data source ranges, leveraging the power of Excel Tables for automatic updates, and applying a few quick data cleaning techniques, you can ensure your analysis is always accurate, current, and reliable.

These Excel workflows save a massive amount of time, but they still require manual steps like updating, refreshing, and cleaning data from static files. We built Graphed because we believe your data should be live and analysis-ready without all the busywork. Instead of downloading CSVs and cleaning them in Excel, we let you connect directly to apps like Google Analytics, Shopify, and your CRM to build real-time dashboards that update themselves automatically, letting you spend your time on insights, not manual data prep.