How to export data from your bank, ERP or online store to Excel and get it ready to use

Almost every system has an export button: online banking, your invoicing software, the ERP or your online store’s dashboard. The trouble comes afterwards. You open the file and find useless title rows, amounts Excel will not add up, dates you cannot filter and repeated orders.

This guide shows you how to choose the right format when exporting and how to turn what you download into a clean table you can sum, filter and build pivot tables from. The steps work for any system, because the problems are always the same.

Which format to choose when exporting

Before you click the button, look at the options the system offers:

• XLSX. If available, it is usually the best choice: numbers and dates more often arrive with the correct type.
• CSV. The most common and most universal format. It arrives without formatting but contains all the data. It must be imported properly so you do not lose zeros or accented characters.
• XLS. Some older systems still produce it. It works, but it is best to convert it to XLSX before you start.
• PDF. Avoid it if there is another option. It is designed for printing, not for analysis.

Also, export only the period you need, include all useful columns (reference, date, description, amount, status) and always use the same settings. That way every export will have the same structure and you can reuse your work.

Typical problems in exported data

These are the flaws that show up again and again, wherever the file comes from:

Step 1: import without damaging the data

If the file is a CSV, do not open it with a double-click. Use Data › Get Data › From File › From Text/CSV:

1. Choose the file and, under File Origin, select 65001: Unicode (UTF-8) if you see strange characters.
2. Check that the delimiter is correct (comma or semicolon).
3. Click Transform Data and set columns with references, order numbers or account numbers to Text, so leading zeros are kept.
4. If there are title rows above the headers, use Home › Remove Rows › Remove Top Rows and then Use First Row as Headers.
5. Click Home › Close & Load.

If the file is an XLSX, open it directly and delete the title and total rows by hand. Leave a single header row and no blank rows in the middle of the data.

Step 2: clean up amounts, dates and text

With the data in Excel, add helper columns with these formulas and, once they are correct, copy and paste them as values over the originals.

Amounts with currency and separators

If cell A2 contains 1.250,75 EUR (a format many European systems use), this formula removes the currency code and turns the text into a number, whatever your computer’s regional settings (Excel 2013 or later):

=NUMBERVALUE(SUBSTITUTE(A2,"EUR",""),",",".")

The second argument says the comma is the decimal separator of the original text and the third that the period separates thousands. If your system exports 1,250.75, swap the two.

Trailing minus sign

Some accounting programs export debits as 45,00-. This formula turns them into negative numbers and leaves everything else as it is:

=IF(RIGHT(A2,1)="-",-NUMBERVALUE(LEFT(A2,LEN(A2)-1),",","."),NUMBERVALUE(A2,",","."))

If the statement has two separate columns, Debit in B and Credit in C, calculate the net movement with =C2-B2. Empty cells count as zero.

Dates in YYYYMMDD format

Many ERPs export dates as 20230315. To turn them into real dates:

=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))

Then apply a date format to the column. Another option without formulas is Data › Text to Columns: in the last step choose Date with the YMD order.

Text with extra spaces

Customer names and descriptions often come with spaces at the beginning or end, and sometimes invisible characters. That makes “Customer A” and “Customer A ” look different. Clean them with:

=TRIM(CLEAN(A2))

Step 3: turn it into a table and remove duplicates

With the data clean, select any cell in the range and press Ctrl+T (or Insert › Table). An Excel table expands automatically when you add rows, keeps its filters and makes formulas and pivot tables update with the new data.

If you have combined several exports, check for duplicates. Use Data › Remove Duplicates and tick only the column that identifies each record, such as the order number or the transaction reference. Do not tick every column if a field, such as the order status, may have changed between one export and the next.

Finally, make sure there are no blank rows or subtotal rows left, as they break filters and pivot tables.

Automate it if you export every month

If you repeat the process every week or every month, let Power Query do it for you. Every step you take in the Power Query Editor (removing top rows, changing types, replacing values, splitting columns) is saved as a recipe.

1. Always save the export with the same name in the same folder.
2. Import the file with Data › From Text/CSV and do the cleanup in the editor.
3. Load the result into a table.
4. The following month, replace the file and click Data › Refresh All.

If you export one file per month and want to combine them all, use Data › Get Data › From File › From Folder: Power Query combines every file in the folder into a single table.

If you just need to turn an exported CSV into an Excel file quickly, without installing anything, you can use our free tool to convert CSV to Excel. And if combining several exports leaves you with repeated records, our tool to remove duplicates deletes them without touching the rest of the data.

Conclusion

Exporting data is easy; getting it ready takes a little method. Choose XLSX or CSV when exporting, import without letting Excel guess, convert amounts and dates with formulas, clean up the text and finish with a table free of duplicates. If the process repeats, Power Query turns it into a single click.

Frequently asked questions