Data Import & Export

How to Insert or Update a Record in a Local Excel File

Phi Hung Vo Sep 25, 2026 869 views

Question details

The user needs to know how to insert a new item or update an existing record in a local Excel workbook based on specific matching criteria.

How to Insert or Update a Record in a Local Excel File

Product: Excel
Device & OS: not provided
Scenario: Managing data entry where records must be updated if they already exist in the dataset, or inserted as new rows if they do not.
Observed behavior: The goal is to define a record structure, establish a duplicate-checking field, and execute an automated or semi-automated insert or update action accordingly.

Before you start

Before proceeding, clearly define the structure of your data and identify a unique column (such as an ID number or email address) that will be used to accurately check for existing records without false matches.

Solution 1Recommended

Automate Insert and Update Actions Using VBA Macros

Create a VBA script to automatically search for a unique identifier, update the row if found, or insert a new row if missing.

Visual Basic for Applications (VBA) is the most efficient method for automating the 'upsert' (update or insert) process in local Excel files. It allows you to programmatically define which column identifies a duplicate and how the data should be written.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications editor. Go to the Insert menu and select Module to create a new script space.

Write the search logic

Write a script utilizing the Range.Find method to search your target worksheet's unique ID column for the incoming record's identifier.

Define the update condition

Add an 'If Not rng Is Nothing Then' statement. Inside this block, write code to overwrite the specific cells in the matched row with your new data.

Define the insert condition

Add an 'Else' statement that finds the last empty row using 'Cells(Rows.Count, 1).End(xlUp).Row + 1' and writes the new record data into that blank row.

Automate Insert and Update Actions Using VBA Macros

Macro Security: Ensure that your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) to retain your automation scripts after closing the file.

Solution 2

Use Lookup Formulas to Identify and Update Records Manually

Identify if a record already exists using lookup functions, then manually update or append the data based on the formula results.

Efficient Data Management with WPS

Manage and Automate Excel Records with WPS Spreadsheet

WPS Spreadsheet provides robust support for data processing, including advanced lookup formulas, data validation, and VBA/Macro support, making it easy to automate inserting or updating records in local files.

1. Open your file in WPS: Launch WPS Office and open your local Excel workbook containing the dataset.
2. Use advanced formulas: Navigate to the Formulas tab to utilize lookup functions for identifying existing records quickly.
3. Run macros for automation: Go to the Developer tab to write or execute macros that automate your insert and update workflows.

Seamless compatibility with Microsoft Excel (.xlsx, .xlsm, .csv) formats.Built-in support for advanced functions like XLOOKUP and VLOOKUP.Full VBA/Macro compatibility for automating data insert and update tasks.Lightweight, fast, and free to use for everyday data management.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use Power Query to insert or update records in Excel?

Power Query is excellent for importing and merging data to highlight updates or new records, but it outputs a new merged table. It does not directly write back (insert/update) into the original source table dynamically without the help of additional VBA scripts.

What is the best way to prevent duplicate records when inserting data manually?

You can use the Data Validation feature to prevent duplicate entries. Select your unique ID column, go to Data > Data Validation, choose 'Custom', and input a formula like =COUNTIF($A$1:$A$1000, A1)<=1.

How do I handle partial matches when updating records?

If an exact match is not guaranteed, you can use wildcard characters (such as * or ?) within functions like VLOOKUP or MATCH. If using VBA, you can utilize the Range.Find method and set the parameter to LookAt:=xlPart.

Excel & Data Problems