Convert a credit card statement to Excel without flipping the signs

Convert a credit card statement into a spreadsheet and the signs often come out backwards. Your purchases show as positive, as if they were money coming in, and your payment shows as negative. Totals, budgets and imports then come out wrong. Here's why it happens and how to fix it.

Why the signs flip

A card statement is written from the bank's side. Its balance is what you owe, so a purchase increases it and prints as a positive charge, while a payment reduces it and prints as a minus or "CR". A bank account statement is the other way round: the balance is what you have, so spending reduces it.

Spreadsheets and accounting software expect the bank account convention, with money out negative. Copy a card statement across as printed, and every purchase lands as money in.

Fix it in Excel

To flip every amount at once:

1. Type -1 in any empty cell and copy it.
2. Select the amount column.
3. Choose Paste Special, select Multiply under Operation, and click OK.
4. Delete the -1.

Purchases are now negative and payments and refunds positive.

Fix it in Google Sheets

Add a helper column with =-C2 (pointing at the amount), fill it down, then copy the helper column and paste it over the original as values only (Edit → Paste special → Values only).

When there are separate charge and credit columns

Some card statements, and some card CSV exports, put charges and credits in two columns. For a spreadsheet with Money out and Money in columns, charges go in Money out and payments and refunds in Money in. That's usually just renaming the headings, as long as both columns hold positive numbers.

Check it's the right way round

A card statement has its own version of the balance check:

previous balance + purchases + fees + interest − payments − credits = new balance

With your purchases now negative, the quick test is the payment row: it should be positive. If the totals are off by exactly twice your payments, the payments are on the wrong side. There's more in why your bank statement spreadsheet doesn't balance.

Importing into accounting software

QuickBooks Online notes that credit card CSVs can show amounts the other way round, so check the preview before you finish an import. If a whole file imports with the signs reversed, it's quicker to undo the import, flip the file and import again than to fix each transaction.

Skip the flipping

The statement converter recognises credit card statements and turns them the way a spreadsheet expects, with purchases as money out and payments and refunds as money in. It checks the result against the statement's own previous and new balance. There's more on the credit card page.