If you have bank accounts with Wells Fargo, here are the steps to get your bank transactions into the Excel Checkbook. It’s best if you already have some comfort with Microsoft Excel including the entry of basic formulas. You can follow these steps every time you want to get your bank transactions. Or maybe you want to get the previous year’s transactions? In either case, we’ll start with a summary.
Summary
- Export your transactions from the Wells Fargo website to a Comma delimited (CSV) file format
- Open that CSV file using Microsoft Excel, and reformat the data so that it has a similar column layout as the Excel checkbook template. This step includes the need to put deposits and withdrawals into a separate column since Wells Fargo puts all amounts in one column.
- Open the Excel Checkbook template and set the opening balance in cell G6 (if this is your first time using the Excel Checkbook)
- Copy transactions from the Wells Fargo CSV file and paste them into the checkbook template, starting in cell A7 (if this is your first time). If you have existing transactions, paste them after the last entries that you already have.
- (Optional) Review each transaction and specify a sub-category for each so that the Dashboard can properly show your income and expenses by category.
Exporting Transactions from Wells Fargo
After logging into the Wells Fargo website, click on your account to see current transactions. There’s a link for Download your account activity to get your transactions.
On the screen that appears next (see below), you can specify a date range.
Be sure to choose the Comma delimited option for the file format. Click Download.
After download, you can open the file in Microsoft Excel. Your result should be similar to the sample screenshot below. Note: there are no column headings provided by Wells Fargo. While not required, it can be helpful to add a row at the top to establish column labels.
Here’s a sample of the above with column headings added for clarity.
Reformatting Steps to Take
- [Optional] Insert a blank row in row #1 and add in some column headings. To do so, right-click on the “1” row and choose Insert. Then type column headings similar to the sample shown above.
- Give column “F” a label — call it Withdrawal
- Give column “G” a label — call it Deposit
- Right-click on the “B” column (Amount) and choose Cut.
- Paste it before the “G” column by right-clicking on the “G” column and choose Insert Cut Cells.
- Insert a new column before Check #, and call it Trx Type. To do so, right-click on the “C” column and choose Insert.
- Delete the “B” column which has asterisks as we don’t want to bring that over to the checkbook spreadsheet. To do so, right-click on the “B” column and choose Delete.
When you’re done, your spreadsheet should look something like this:
If your spreadsheet looks like the sample above with the Withdrawal column in “E”, enter the following formula into the E2 cell:
=IF(G2<0, G2 * -1, "")After doing so, copy that formula all the way down the sheet. The quickest way to do so is by pointing your mouse on cell E2 but at the bottom-right corner until you see a black plus symbol. Then double-click.
Enter this formula into the F2 cell:
=IF(G2>0, G2, "")Copy that formula down the sheet just like you did for the E2 formula.
At this point, your spreadsheet should look something like this:
You are now ready to copy & paste your transactions from this CSV file into your Excel Checkbook register. Be sure to use “Paste as Values” when you paste into the Excel Checkbook register. See this video for a helpful guide on copying & pasting transactions. You can skip ahead to the 2:55 minute mark. The following video was made to show how to copy data between an older spreadsheet and a newer checkbook spreadsheet, but the copy/paste process that you’ll need to follow is identical.