Barclaycard Statement to Excel: Credit Card Guide

TL;DR A Barclaycard statement is a credit card statement, so it works the opposite way to a current account: purchases, interest and fees increase what you owe, and payments and refunds reduce it. To get it into Excel, convert the PDF so each transaction is one row, choose one sign convention (we recommend purchases positive, payments negative, matching the balance owed), and check that the previous balance plus all transactions equals the new balance on the statement. Then tag your monthly payment as a transfer if you also track the current account it came from, categorise the spending, and fix any UK date issues. Keep the PDFs as your official record.

Barclaycard is one of the best-known credit card brands in the UK. Lots of people put most of their everyday spending on a card for convenience or rewards, which means the card statement, not the bank statement, is where the real detail of their spending lives. If you want a budget, a sole trader expense list, or just a clear picture of the year, you need those card transactions in a spreadsheet.

This guide covers how a credit card statement differs from a bank statement, which file to start with, how to convert a PDF, the balance check that proves your data is complete, how to avoid double counting with your current account, a worked example, and common problems. Barclaycard's online servicing and app change over time, so we describe options in general terms rather than listing exact menu steps.

How a credit card statement differs from a bank statement

If you've converted bank statements before, the main difference is direction.

On a current account, the balance is money you have. Deposits increase it and spending reduces it.

On a credit card, the balance is money you owe. Purchases, cash withdrawals, interest and fees increase it. Payments you make and refunds from merchants reduce it.

A Barclaycard statement typically shows:

• Previous balance, the amount owed at the end of the last statement.
• Transactions in the period: purchases, refunds, payments received, interest and any fees.
• New balance, the amount owed at the statement date.
• Payment information: the minimum payment and the date it's due.
• Credit limit and available credit.
• Interest rate information for different types of balance.

Only the transactions and the two balances matter for the spreadsheet. The payment information and interest rates are useful context but don't go into your transaction table.

PDF statement or transaction download?

Depending on your account and the current online servicing tools, you may be able to download PDF statements and possibly recent transactions in a spreadsheet-friendly format. Check what's available.

PDF statements are the formal monthly record, with the previous and new balances. Use them for anything you need to prove, and as the reference for your balance check.

Transaction downloads are easier to open, but they may cover an arbitrary date range, include pending items, or exclude interest and fees that only appear at the statement date. They're good raw data, but always reconcile them to a statement.

If you only have PDFs, or need statements from a while back, you'll need to convert them.

Converting a Barclaycard PDF into rows

Copy and paste from a PDF viewer is possible for a short statement, but card statements often have multiple sections (for example, separate lists for different cardholders or balance types), and pasting tends to mix them up or drop the "CR" markers that indicate credits.

Power Query in Excel for Microsoft 365 on Windows can import tables from PDFs. Results vary; see our Power Query bank statement guide.

A converter built for statements. SheetStatement's credit card statement converter reads the PDF into one row per transaction with a signed amount and checks the opening and closing balances, then exports to Excel or CSV. The free plan includes 3 pages a month; paid plans are $9, $19 and $39 a month.

Aim for this layout:

Add Cardholder if the account has additional cardholders and their transactions are listed separately.

Step 1: choose a sign convention

This is the decision that prevents most credit card spreadsheet confusion. Pick one and stick to it.

Option A: match the balance owed. Purchases, interest and fees are positive; payments and refunds are negative. The balance check is then Previous + SUM(Amount) = New. This is the natural choice for reconciling the card itself.

Option B: match your bank account. Purchases are negative (money out), payments and refunds positive. This makes it easier to combine card and bank data in one budget, but the balance check becomes Previous - SUM(Amount) = New.

We use Option A in this guide. If you later need Option B for a combined budget, multiply the column by −1 in a helper column.

Credits on card statements are often marked with "CR" after the amount rather than a minus sign. Convert those first. With the amount text in C2:

=IF(RIGHT(TRIM(C2),2)="CR", -VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"CR",""),"£",""),",","")), VALUE(SUBSTITUTE(SUBSTITUTE(C2,"£",""),",","")))

If credits appear in brackets instead, see our guide to converting parentheses to negative numbers.

Step 2: check the balance

With Option A signs:

=ROUND(Previous + SUM(Amount) - New, 2)

Zero means every transaction is present with the right sign. If it isn't zero:

• The difference equals one transaction. A row was missed, often at a page break or in a separate cardholder section.
• It's double a transaction. A credit lost its "CR" marker and was treated as a purchase, or the reverse. Payments are the usual suspect.
• It equals the interest or a fee. Interest and fees can appear in a separate part of the statement from purchases. Make sure they're included.

Our credit card reconciliation guide goes into more detail, including how to reconcile a card in bookkeeping software.

Step 3: fix dates and descriptions

Dates. UK statements use day/month/year, and card statements sometimes show only the day and month, such as 14 Mar, with the year implied by the statement period. If your statement period crosses a year end, transactions from late December and early January sit together, so add the year carefully. If dates have come through as text, rebuild them with DATE; our guide to fixing bank statement dates in Excel covers each format.

Card statements may show two dates per transaction: the date of the purchase and the date it was posted to the account. Use one consistently. The transaction date is usually best for budgeting, while the posting date determines which statement a transaction lands on.

Descriptions. Merchant descriptions on card statements often include a location, a reference, or a foreign currency note. Keep the original text and add a short Payee column if you want tidy reports. Our guide to splitting transaction descriptions in Excel has formulas for separating merchant names from the rest.

Foreign transactions. Purchases in another currency are usually shown in pounds with the original amount and exchange rate noted, and there may be a separate non-sterling transaction fee. Record the sterling amount as the transaction and keep any fee as its own row so it's categorised correctly.

Step 4: don't double count your card payment

If you also track the current account you pay the card from, your monthly card payment appears in two places:

• On the current account as money out (for example, "Barclaycard" or similar).
• On the card statement as a payment received, reducing the balance.

That payment isn't spending; it's settling spending that's already listed on the card. Tag it as Card payment or Transfer on both sides, and exclude it from your spending totals. Your real spending is the card purchases themselves, plus interest and fees.

The same applies to bookkeeping software. In Xero or QuickBooks, the card is its own account. The payment from the current account is a transfer between two accounts, not an expense. Our guide to importing credit card transactions into QuickBooks Online shows the setup.

A worked example

A Barclaycard statement for one month shows a previous balance of £412.60 and a new balance of £539.47. After conversion and applying Option A signs:

Balance check: the amounts sum to −412.60 + 58.20 + 46.00 + 89.99 − 24.99 + 42.75 + 12.00 + 71.32 + 38.40 + 205.80 = 126.87. £412.60 + £126.87 = £539.47. Matches the new balance. Complete.

Spending: excluding the card payment, the month's net spending was £539.47. Of that, £129.52 was groceries, £65.00 net was household (the refund offsets part of the online order), and £217.80 was business-related (software and hotel), which you might reclaim or record as a business expense, depending on your circumstances.

If the hotel and software were business costs on a personal card, keep the receipts and tag them clearly. Whether and how you can claim them depends on your situation; this is general information, so check HMRC guidance or ask an accountant. Our guide to categorising business expenses from bank statements shows a practical system.

Using card data for budgets and tax

Budgets. Card statements are often the richest source of spending detail. Combine them with your current account data, using the transfer tags to avoid double counting, then summarise by category and month. Our guide to creating a budget from bank statements shows the workbook structure, and our pivot table guide shows the summaries.

Interest and fees. Add them up for the year. It's a useful number to see in one place, and for some people it's the push to change how they use the card.

Sole traders. If business purchases go on a personal card, a categorised card spreadsheet makes your Self Assessment expense list much easier to prepare. The UK tax year runs from 6 April to 5 April, so filter by tax year rather than calendar year.

Bookkeeping catch-up. If you need to load months of card history into accounting software, convert each statement, check each balance, and import in order. Our guide to bookkeeping catch-up from months of statements explains how to do it without creating duplicates.

Balance transfers, cash and promotional balances

Card statements can include transactions that aren't ordinary purchases. Each needs a deliberate decision in your spreadsheet.

Balance transfers. If you moved debt from another card onto your Barclaycard, the statement shows the transferred amount as an increase in what you owe, and possibly a separate balance transfer fee. The transferred amount isn't new spending; it's the same debt moving from one card to another. Tag it Balance transfer and exclude it from spending totals. If you also track the old card, its statement will show a matching credit. The fee, on the other hand, is a real cost and belongs in a Fees category.

Cash withdrawals. Using a credit card at a cash machine usually counts as a cash transaction, which may carry its own fee and interest terms. In the spreadsheet, the withdrawal itself is a transfer of money to your wallet rather than spending on anything specific. If you track cash spending separately, tag it Cash; otherwise put it in a general Cash category. Keep any associated fee as its own row.

Promotional balances. Statements may show different balances under different interest rates, for example a promotional rate on a balance transfer and a standard rate on purchases. For the spreadsheet, these are just labels on the same total. Your balance check uses the overall previous and new balances, not each sub-balance.

Disputed transactions. If you dispute a charge, it may stay on the statement while the dispute is investigated, and a credit may appear on a later statement if it's resolved in your favour. Keep both rows and use a Notes column to link them, so you can see the net cost of that purchase at a glance.

Reviewing a whole year

Once you've converted twelve statements, stack them into one table with a Statement month column, and check that each new balance equals the next month's previous balance. A break in that chain tells you a statement is missing or a month was converted twice. Then a pivot table by category and month gives you a clear view of where the card was used. Many people find this annual review more useful than any monthly glance at the app, because patterns such as creeping subscription costs or seasonal spikes only become obvious over a full year. Our guide to finding recurring payments in a bank statement works just as well on card data.

Common problems

Credits treated as purchases. If the "CR" marker is lost, refunds and payments become charges and the balance check fails by double their value. Fix the sign formula.

Missing interest. Interest is often a single line at the end and easily missed.

Additional cardholder sections. Some statements list each cardholder's transactions separately. Make sure every section is converted.

Statement versus transaction date. A purchase made on the last day of a period may post on the next statement. Reconcile using the statement's own list, not your memory of when you bought something.

Duplicates when combining months. If you mix PDFs and downloads, the same transaction can appear twice. See our guide to removing duplicate bank transactions in Excel.

A short checklist

• Previous balance plus all transactions equals the new balance on each statement.
• Each new balance equals the next statement's previous balance.
• Credits are negative under your sign convention, purchases positive.
• Card payments are tagged and excluded from spending.
• Dates are real dates with the correct year.
• PDFs are kept with the workbook.

Convert your Barclaycard statements

Upload a Barclaycard PDF to SheetStatement, check the previous and new balances, and export clean rows to Excel or CSV. The free plan covers 3 pages a month; Starter, Pro and Business plans are $9, $19 and $39 a month.

FAQ

Can I export Barclaycard transactions to Excel?

Depending on your account, you may be able to download recent transactions as well as PDF statements. For full statement periods with balances, converting the PDF gives rows that reconcile to the official figures.

Should purchases be positive or negative?

Either works if you're consistent. Positive purchases match the balance owed on the card; negative purchases match a bank account and make combined budgets easier.

Why is my Barclaycard spreadsheet off by double a payment?

A credit marked "CR" was probably treated as a charge. Convert CR amounts to negatives before checking the balance.

Is my credit card payment an expense?

No. It settles purchases already on the card. Tag it as a transfer on both the card and current account so spending isn't counted twice.

How do I handle foreign currency purchases?

Record the sterling amount shown on the statement, and keep any non-sterling transaction fee as its own row.

How much does SheetStatement cost?

The free plan includes 3 pages a month. Paid plans are Starter at $9 a month, Pro at $19 and Business at $39.