Bookkeeping is a must for any small business owner, helping track income, expenses, and overall financial health. While there are plenty of accounting software options available, many entrepreneurs still rely on Excel bookkeeping because it’s accessible, customizable, and—most importantly—free.

But while Excel can be a useful tool, it’s not always the most efficient. Manually entering transactions, setting up formulas, and generating reports can be time-consuming and prone to human error. That’s why many businesses eventually turn to automated solutions like North One, which integrate with bookkeeping tools to simplify financial management.

This guide walks you through how to do bookkeeping in Excel step by step. Whether you’re just starting or looking to refine your process, these steps will help you keep your finances organized. Plus, we’ll explore when it makes sense to switch to an automated system that saves time and reduces errors.

Step 1: Set Up Your Excel Workbook

Before entering transactions, start with a well-structured Excel workbook. This makes it easier to track income, expenses, and balances without confusion. Here are a few simple instructions for setting one up:

Create a New Workbook and Tabs

Open Microsoft Excel and create a new spreadsheet. Rename the first sheet to Bookkeeping 2025 and add separate tabs for Income, Expenses, and Summary to keep records organized.

Set Up Your Transaction Table

Formatting a table in Excel allows for easy filtering and sorting, helping you quickly find specific transactions. On your main sheet, create a table with the following columns:

  • Date: When the transaction occurred
  • Description: A brief note on what the transaction was for
  • Category: Income or expense type (e.g., Services, Office Expenses)
  • Income: Amount received
  • Expense: Amount spent
  • Balance: Running total after each transaction
  • Payment Method: How the transaction was made (e.g., Credit Card, Bank Transfer)

Define Categories for Better Tracking

Categorizing transactions ensures better financial organization. Common income categories include sales, service fees, and rental income, while expense categories might include office supplies, marketing, and software subscriptions. Keeping categories consistent simplifies tax preparation and reporting.

Step 2: Create Income and Expense Categories

Organizing transactions into clear categories makes bookkeeping easier and ensures accurate financial tracking. Categorization helps you understand where your money is coming from and where it’s going—crucial for budgeting and tax preparation. Without structured categories, bookkeeping can become messy and difficult to analyze.

Common Income Categories

Grouping income sources helps track revenue streams effectively. These categories allow you to see which areas of your business are generating the most money:

  • Sales Revenue: Money earned from selling products or services
  • Freelance/Contract Work: Payments for client work
  • Rental Income: If you lease equipment or property
  • Investments & Interest: Dividends or bank interest earned

Common Expense Categories

Tracking expenses properly helps identify where money is being spent and can highlight areas where you can cut costs. Use these categories to keep spending organized:

  • Office Supplies: Paper, pens, and other materials
  • Marketing & Advertising: Social media ads, print materials
  • Software Subscriptions: Tools like accounting or design software
  • Travel & Transportation: Business trips, mileage, or commuting costs
  • Taxes & Fees: Business taxes, credit card processing fees

Keeping categories consistent makes it easier to generate reports and identify spending patterns. You can also color-code categories in Excel for quick reference.

Step 3: Input and Organize Transactions

Once your categories are set up, the next step is entering your transactions accurately. Keeping records updated ensures you always have a clear picture of your business finances. Whether you’re tracking income from clients or logging expenses like software subscriptions, maintaining an organized system prevents errors and makes tax time easier.

How to Enter Transactions in Excel

Each time you receive income or make a purchase, add a new entry to your bookkeeping table. Be sure to include:

  • Date: The transaction date
  • Description: A short note on what the transaction was for
  • Category: The assigned income or expense type
  • Amount: The exact value of the transaction
  • Payment Method: How it was paid (e.g., credit card, bank transfer, cash)

Manually entering transactions is time-consuming, but it ensures accuracy if done regularly. Setting aside a few minutes each day or week to update your records helps avoid last-minute scrambling.

Using Excel Features to Stay Organized

Excel has built-in tools that make bookkeeping more efficient. For example, the Sort & Filter function makes it easy to search for specific transactions by date, amount, or category. Likewise, the Conditional Formatting function can be used to highlight overdue invoices or large expenses automatically. You can even create Drop-Down Lists of predefined categories to speed up data entry and reduce mistakes.

A structured, organized spreadsheet helps you track finances with clarity. However, as your business grows, manually entering transactions may become overwhelming. At that point, automated bookkeeping integrations like those offered by North One can save time by syncing transactions directly with your bank account—eliminating the need for manual updates.

Step 4: Use Formulas to Automate Calculations

Manually calculating totals for income, expenses, and balances can be time-consuming and prone to errors. Fortunately, Excel formulas can automate these calculations, making bookkeeping more efficient and accurate. With just a few simple functions, you can save time and reduce the risk of mistakes. Here are some essential formulas to help streamline your bookkeeping:

  • SUM(): Adds up values in a column, such as total income or expenses. Example: =SUM(B2:B50) calculates the total of all values in column B.
  • SUBTOTAL(): Sums only visible values in a filtered column, useful for reports. Example: =SUBTOTAL(9, C2:C50) calculates the total while ignoring hidden rows.
  • IF(): Applies conditions, such as labeling transactions automatically. Example: =IF(D2=”Office Supplies”, “Business Expense”, “Other”) assigns a category.
  • ROUND(): Rounds numbers to a specific decimal place to keep values clean. Example: =ROUND(E2, 2) rounds the value in E2 to two decimal places.
  • TEXT(): Formats numbers or dates for better readability. Example: =TEXT(A2, “MM/DD/YYYY”) ensures all dates appear in the same format.
  • LEFT() / RIGHT(): Extracts part of a text string, useful for transaction IDs or codes. Example: =LEFT(A2, 4) pulls the first four characters from a cell.
  • TODAY(): Inserts the current date automatically, useful for tracking transactions. Example: =TODAY() updates the cell with today’s date.
  • Balance Calculation: Keeps a running total of cash flow. Example: =F2+D3-E3 (where F2 is the previous balance, D3 is income, and E3 is expenses).

Step 5: Generate Basic Financial Reports

Once your transactions are recorded and categorized, the next step is to create basic financial reports to get a clear view of your business’s financial health. Reports help track cash flow, identify spending trends, and prepare for tax season. In Excel, you can quickly generate these reports using built-in tools and formulas.

  • Income Statement: Summarizes total income and expenses over a period to show profit or loss. Example: Use =SUM(D2:D50) – SUM(E2:E50) to calculate net income.
  • Expense Report: Breaks down spending by category to identify where money is going. Example: Use a Pivot Table to group and total expenses by category.
  • Cash Flow Statement: Tracks money moving in and out of your business to monitor liquidity. Example: Use =SUM(Income Column) – SUM(Expense Column) for cash flow.
  • Profit and Loss Overview: Provides a snapshot of revenue versus expenses. Example: Use conditional formatting to highlight months with a negative balance.
  • Aging Accounts Report: Identifies overdue payments from clients or unpaid invoices. Example: Use =TODAY()-A2 to calculate how many days a payment is overdue.
  • Break-Even Analysis: Helps determine when revenue will cover expenses. Example: Use =Fixed Costs/(Sales Price – Variable Costs) to calculate the break-even point.

Step 6: Reconcile with Bank Statements

Reconciling your Excel bookkeeping with your bank statements ensures accuracy and helps catch errors, missing transactions, or fraudulent activity. This process involves comparing your recorded transactions with actual bank records to confirm that everything matches. Regular reconciliation—whether weekly or monthly—prevents bookkeeping errors from piling up and keeps your financial records reliable.

Start by downloading your bank statements in CSV or PDF format, then cross-check each transaction in Excel, verifying dates, amounts, and descriptions. If discrepancies appear, highlight missing or incorrect entries and adjust them accordingly. Be sure to account for bank fees, interest earnings, and service charges, as these may not be automatically reflected in your records. Once reconciled, save and store your records for tax preparation and financial reporting.

Step 7: Save and Backup Your Data

Keeping your bookkeeping records safe is just as important as maintaining accurate entries. Losing financial data due to accidental deletion, file corruption, or computer failure can create major setbacks, especially during tax season or financial audits. Regularly saving and backing up your Excel bookkeeping ensures that your records remain secure and accessible.

Start by saving your file frequently and using a clear naming convention, such as Bookkeeping_2024_Q1.xlsx. Consider enabling auto-save in Excel to prevent data loss. For added protection, store backups in multiple locations, such as an external hard drive, a cloud storage service (Google Drive, OneDrive, Dropbox), or an encrypted USB device. If you make frequent updates, setting up automatic cloud syncing can ensure that the latest version of your records is always available.

Finally, keep historical copies of your financial records in case you need to reference past data. A good practice is to save a new version at the end of each month or quarter, allowing you to track changes over time without overwriting previous information.

Is Excel Enough for Your Bookkeeping Needs?

Excel is a powerful tool for bookkeeping, offering flexibility and control over your financial records. However, as your business grows, manually managing bookkeeping in Excel can become time-consuming. In particular, automating financial tracking with a banking solution that integrates with bookkeeping tools can help reduce errors and free up valuable time. 

North One makes it easy to streamline financial management by syncing transactions and automating record-keeping, so you can focus on growing your business. Sign up today and take the hassle out of financial tracking.