Guide: Downloading, organizing, and sorting bank statement data

Sarah Covell
|
46 steps
|
3 minutes

Google Drive

Google Docs

Wellsfargo

Navigate to the bank account you use for your business. Select the desired dates and download to a spreadsheet, .csv, or comma delimited format.

Click "Download Account Activity"

Select your desired date range.

Click "Download"

Open Google Drive

Tip: My Preference is to have a main Tax folder, then subfolders for each tax year where you can save this information and anything else that applies to the year. Then you don't have to search as much when reviewing your business, or in preparing for tax time. If you have multiple businesses, have separate bank accounts and tracking sheets for each.

Click "New, then "File Upload" to upload the file to Google Drive.

Double-click the file to open it.

Click here to open with Google Sheets.

Select Row 1 and right click or Control click on it to bring up the menu.

Select "Insert 1 row above". You will use this to create a header row.

In this example, select Column C and hit delete to remove the unnecessary characters.

Add column headers according to the order of the data (it's different for each bank). Here, I used "DATE, AMOUNT, CATEGORY, and TRANSACTION"

Select Row 1, then click "View"

Select Freeze then 1 row so column headings never move

Select the top left (empty) cell to highlight the entire sheet.

Click "Data", then "Sort Range"

Click "Advanced range sorting options"

Click here to use header row titles.

Select "TRANSACTION".

Sorting by Transaction/Description places all similar transactions together, so categorizing is much less tedious or time consuming.

Click "Sort"

Begin entering business expense categories in the appropriate column.

Remember to be allowable, expenses must be "necessary" and "reasonable"for your business. While bank statements are sufficient as proof for many expenses, some things should also be backed up with receipts saved somewhere safe in the event you need them. Receipts from restaurants for meals with notes about who you met and what was discussed, or notes on a Costco receipt regarding the business purpose of the items purchased, are necessary because on their face, it's not clearly business related.

A reminder, for several cells with identical entries, you can drag the dot on the bottom right corner of a cell to fill in all applicable cells quickly.

Click here.

Continue Categorizing transactions.

To total all expenses in a category for tax purposes, my preference is to use advanced sorting and sort by Category first, then add another column and select Transaction.

Click "Sort"

You know your expenses better than anyone else does. Whether you're preparing your own taxes or handing them off to someone else, you'll be your preparer's favorite client if you bring in Schedule C business information totaled and pretty. This also gives them more time to look for additional savings or potential issues in your return, rather than drowning in a tornado of receipts that may or may not fully reflect your business expenses. *The only things I want handed to me in a shoebox are **actual shoes**, size 8M/9.5W.*

You can create a section with your category names on the right side of the sheet and create formulas to total (SUM) each one

To create your formula, go to the cell where you want it to appear and type "=SUM(StartingCell#:EndingCell#)

OR type " =SUM( " and drag to select each cell in that category.

When you've selected the last cell, type ") and [[enter]]

Continue totaling categories in this manner.

If you'd prefer to see the Amounts as dollar entries, highlight the column and click "$"

As an alternative to totaling your expenses by category in the bank statement Sheet, you can copy and paste data into one of my Tracking Templates (free on my website, just make a copy and save to your own Drive. Easily customizable categories, or I can customize the sheet for you for a fee) If you plan to do this, drag the columns from your bank sheet into the following order: Date, Transaction, Category, Amount. It is **not necessary** to begin to categorize, but I do recommend sorting by Transaction before copying into the Tracking Template.

To use the tracking template, navigate to www.CovellTax.com

Click "File"

Select "Make a copy"

Decide where you want the copy to be saved (it should go in the same tax folder, year subfolder as your bank transactions) and what it will be named and click "Make a copy"

I recommend you include your name or the business name and tax year in the title- especially if you'll be sharing it with a tax preparer.

Once you've made your copy, return to your bank transaction sheet, highlight Columns A, B, C and D and copy them.

Do NOT highlight/copy the entire sheet, it will mess up some of the formulas in the Tracking Template.

Open the Current Expenses tab of the Tracking Template and paste in the copied data.

NOTE: The three entries in the Template are there as an example only; please DELETE THEM before making your own entries.

Delete the column headers that came over with the copied information.

The Category column has a pulldown menu with potential expense types. Select the correct category from the options.

You can also select the dot in the bottom right corner of the Category cell and drag it to cover all applicable cells.

Tip: If you don't see an appropriate category option in the pulldown menu, look to the right in Column F and add it instead of an option that is not applicable to your business.

In this example, enter Gas instead of Other/Training.

A reminder: it is rare for many businesses to use actual vehicle expenses where a % of gas expenses could be written off. This example is the exception rather than the rule, but if you're in that situation, your Gas expenses would be filled in with the totals on the Mileage/Vehicle tab.

These totals for Current Expenses will translate directly into coordinating sections of Schedule C, or can be specified on Schedule C, Other.

*and* be included in a pie chart for the % of your expenses each category makes up.

I find it best to separate out Income and/or Transfers between accounts belonging to you.

In order to further separate expense and income/transfer totals, I prefer to select those entire rows, Cut (using command-X or ctrl-X), and paste into another tab for Transfers or Income respectively

Add a new tab using the + bottom left, then click on the arrow to the right of the tab to select Rename and enter an appropriate new title.

Then press Command-V or Control-V to paste the copied rows into the new tab