How to Automate Data Entry in Excel

For a comparison of products, see the best data entry automation software in 2026.

Refresh a file with Power Query

Use this in Excel for Microsoft 365 when the rows already exist in a CSV, a text file, or another workbook.

1. Open the workbook that should receive the rows.
2. On the Data tab, choose Get Data, From File, then From Text/CSV or From Workbook.
3. Pick the file. In the Power Query editor, keep the columns you need and set the types.
4. Choose Close & Load. Excel writes the result to a sheet.
5. When the source file changes, choose Data, Refresh All. The same steps run again.

Check the loaded rows before you use them in a report.

Repeat a pattern with Flash Fill

Use this when the values are already on the sheet and you want to split or combine them, such as a full name into a first name.

1. In the column next to the source, type the result you want for the first row.
2. Select the cell below that example.
3. On the Data tab, choose Flash Fill. On Windows the shortcut is Ctrl+E.
4. Check the filled cells. Flash Fill follows the example you typed. If the pattern is wrong, undo and type a second example.

Record the same clicks

Use a macro in desktop Excel when the entry is the same sequence of clicks.

1. Go to File, Options, Customize Ribbon, and check Developer.
2. On the Developer tab, choose Record Macro, name it, and start.
3. Perform the entry once, then choose Stop Recording.
4. Next time, choose Developer, Macros, then Run.

Excel for the web does not run VBA macros. A macro follows the layout you recorded. Record it again when the columns move.

One option: Elyx in the open workbook

Elyx is one option when the rows are already in the file and you want to describe the entry instead of building a query or a macro. You describe the column or the rows. Elyx writes them in the workbook.

The trial lasts 7 days and does not ask for a card. Then Plus is €12/month and Pro is €29/month. There is no permanent free plan. Elyx is not a form product and it is not an OCR product. It does not run in Google Sheets.

Start the 7-day free trial, no credit card.

Direct answers

How do you automate data entry in Excel?

Load a CSV or another workbook with Power Query, then use Data, Refresh All when the file changes. For values already on the sheet, type one example and use Flash Fill. In desktop Excel, record a macro for the same clicks.

How do you bring new rows in without retyping the file?

After the first Power Query load, choose Data, Refresh All. Excel runs the same steps on the updated source file.

Where is the software comparison?

The comparison of data entry automation software, with prices when they are published, is on the best data entry automation software page. This page stays on the Excel steps.