Is managing your finances starting to feel overwhelming?
Creating a bookkeeping template in Google Sheets can help you stay organized and on top of your expenses. With a template, you can track your income, expenses, and other financial details without the hassle of complicated software.
What Is Bookkeeping?
Bookkeeping is like keeping a diary, but instead of writing about your day, you jot down all the financial details for your business—when money comes in when it goes out, and what it’s all about.
It’s ensuring you’re tracking every penny so you know exactly how much cash you’ve got, where it’s coming from, and where it’s being spent. This is a really important task because it helps you see if your business is doing well, like if you’re making a profit, and it keeps everything for tax time or when you need to make big business decisions.
Step 1: Open a New Google Sheet
First, go to Google Sheets and click “+ Blank” to start a new spreadsheet.
Step 2: Set Up the Header Row
Your header row will define the type of information you’ll track. Enter the following column titles:
- Transaction Date
- Category (Income or Expense)
- Transaction Number
- Description
- Mode of Payment
- Paid To
- Amount
- Starting Amount
- Balance
You can adjust these columns based on your specific needs, but these are the basics for bookkeeping.
Step 3: Format the Columns
Next, it’s time to format your columns so they function properly. Highlight the “Amount” “Starting Balance,” and “Balance” columns, and select “$.” It will make your numbers appear as dollar amounts.
Step 4: Input Your Transactions
You can start inputting your transactions now that your template is set up. In each row, fill in the date, transaction description, amount, etc.
Step 5: Add a Dropdown Menu
You can add a dropdown menu for recurring information, such as Category and Mode of Payment columns. To do this, select the column where you want to add it and click Insert > Dropdown from the toolbar. Then, you can now customize your dropdown list options.
Step 6: Add a Running Balance Formula
You’ll need to add a formula to keep track of your total balance. In cell H7 (the first cell under “Balance”), enter the formula:
=IFS(A7=”INCOME”,H4+G7,A7=”EXPENSE”,H4-G7)
(assuming that your A7 is where the Category is, H4 is where the Starting Balance is, and G7 is the first transaction amount)
It will automatically update your initial balance depending on whether the first transaction is income or an expense.
Now, for the second cell under Balance, use:
=IFS(A8=”INCOME”,H7+G8,A8=”EXPENSE”,H7-G8)
(assuming that your A8 is where the Category is, H7 is where the first transaction amount is, and G8 is the following transaction amount)
Step 7: Auto-Calculate Totals
You can use the “SUM” function to see your total income and expenses quickly. Below your last transaction, in the “Amount” column, enter:
=SUM(G7:G29)
This will add up all the amounts in that column, giving you your combined income and expenses.
To split this into separate totals for income and expenses, you can use the following formulas:
For Total Income:
=SUMIF(A7:A29, “Income”, G7:G29)
For Total Expenses:
=SUMIF(A7:A29, “Expense”, G7:G29)
This will calculate the total for each category.
Once you’ve entered your transactions and set up your formulas, save your template. You can reuse this template every month or year by simply making a copy in Google Sheets. Feel free to customize the template by adding more categories, adjusting the layout, or adding charts to visualize your finances. You can also share it by clicking the “Share” icon.
Get the Free Bookkeeping Template
Get a copy of the free Bookkeeping Template. I’ve populated some cells as examples, but you can customize them as needed.
Final Thoughts
This bookkeeping template in Google Sheets makes managing your finances much easier and more organized. You’ll have all your records in one place, ready for tax time or when you need to make important business decisions.
Frequently Asked Questions
What formulas should I use to calculate my running balance?
The formula to track your balance depends on whether a transaction is an income or expense. Use this formula in the “Balance” column “=IF(C2=”Income”, E1+D2, E1-D2)” This formula checks if the transaction is categorized as income or expense and then adjusts your balance accordingly.
How do I calculate the total income and expenses automatically?
Google Sheets has a SUMIF function that helps you calculate totals based on categories. To calculate Total Income “=SUMIF(C2:C100, “Income”, D2:D100)” To calculate Total Expenses “=SUMIF(C2:C100, “Expense”, D2:D100)” These formulas automatically total all amounts marked as income or expense.
