Data Import & Export

How to Update One Excel Spreadsheet from Another Without Overwriting Formulas

Guest Writer Sep 27, 2026 869 views

Question details

The user needs to retrieve and update specific values in a primary spreadsheet using data from a secondary spreadsheet, ensuring that existing calculated formulas and specific columns are not overwritten.

How to Update One Excel Spreadsheet from Another Without Overwriting Formulas

Product: Excel
Device & OS: not provided
Scenario: Merging updated information from a new dataset or external report into a master workbook that contains complex formulas.
Observed behavior: Updating the entire sheet normally overwrites calculation columns; the goal is to target only specific static data fields using a unique identifier.

Before you start

Ensure both spreadsheets share a common unique identifier (such as an ID number, SKU, or email address) to correctly match the rows. It is highly recommended to create a backup of your master file before performing bulk data updates.

Solution 1Recommended

Use VLOOKUP or XLOOKUP for Targeted Column Updates

Ideal for simple updates where you only need to pull specific static values while leaving your formula-driven columns completely untouched.

Lookup functions allow you to fetch data based on a matching unique ID. By writing the formula only in the columns that need updating, your existing calculation columns are preserved.

Open both spreadsheets

Open your master spreadsheet (the one to be updated) and the source spreadsheet containing the new data in Excel.

Insert the Lookup formula

In the master spreadsheet, click the first empty cell of the column you want to update. Enter your lookup formula using the unique ID as the lookup value. For example: =VLOOKUP(A2, [Source.xlsx]Sheet1!$A$1:$F$100, 3, FALSE).

Apply to the entire column

Press Enter, then double-click the fill handle at the bottom-right corner of the cell to drag the formula down the column.

Convert formulas to values

Once the data is populated, copy the updated column, right-click, and select 'Paste as Values' to remove the VLOOKUP formula and keep the static text, ensuring you skip any columns containing your permanent formulas.

Use VLOOKUP or XLOOKUP for Targeted Column Updates

Pro Tip: XLOOKUP is generally easier and safer to use than VLOOKUP because it defaults to an exact match and allows you to look up values to the left or right of your matching column without counting column indexes.

Solution 2

Use Power Query for Recurring Data Updates

Best for recurring reports where data needs to be refreshed frequently across multiple columns without manual copy-pasting.

Seamless Data Management with WPS

Update Spreadsheets Easily with WPS Office

WPS Spreadsheet provides powerful data handling tools, fully supporting VLOOKUP, XLOOKUP, and complex data references. You can seamlessly update specific columns across multiple workbooks without losing your calculations, all within a familiar, high-performance interface.

1. Launch WPS Spreadsheet: Open WPS Office and start a new WPS Spreadsheet session.
2. Open your workbooks: Open both your master file and your new data file. They will conveniently open in separate tabs within the same window.
3. Use lookup functions: Navigate to the Formulas tab, click 'Insert Function', and select VLOOKUP or XLOOKUP to match records based on your unique ID column.
4. Populate specific columns: Drag the lookup formula down the specific columns you need to update, leaving your existing formula columns completely intact.
5. Save as .xlsx: Save your updated master file in .xlsx format to guarantee perfect compatibility if you share it with Microsoft Excel users.

100% format compatibility with Microsoft Excel (.xlsx, .xls, .csv)Full native support for VLOOKUP, XLOOKUP, and advanced nested formulasIntuitive tabbed interface to easily switch between master and source workbooksFree to download and lightweight on system resources

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VLOOKUP formula return a #N/A error?

The #N/A error occurs when the formula cannot find the unique identifier in the source spreadsheet. Check for hidden spaces, formatting mismatches (such as numbers formatted as text), or misspellings in the ID columns of both sheets.

Can I update multiple columns at once using XLOOKUP?

Yes. Unlike VLOOKUP, XLOOKUP can return an array of values. By selecting multiple adjacent columns in the 'return_array' argument, XLOOKUP can fill several columns at once from a single matching ID.

Does Power Query delete existing formulas in my master sheet?

Power Query outputs data into a structured Excel Table. While formulas placed in columns directly adjacent to the Power Query table are preserved and auto-fill down when new rows are added, formulas written manually inside the query's output range will be overwritten upon refreshing.

How do I prevent lookup formulas from recalculating every time?

To prevent spreadsheet slowdowns and accidental overwrites, copy the column containing your successful VLOOKUP/XLOOKUP results, right-click, and choose 'Paste as Values'. This hardcodes the retrieved data and removes the active formula.

Excel & Data Problems