How to Use Excel for Accounting (and When to Level Up)

I own a small business with five full-time employees, several dozen freelance contractors and a half-dozen big-name clients, and I do all my “accounting” in Microsoft Excel. I put that word in quote marks, though, because I know that what I do is not real accounting — it’s transaction tracking. I’ve learned all about real accounting from professionals my company has worked with over the years to produce expert accounting content for multiple clients, from a Big 4 accounting firm to a consumer finance provider with a vested interest in its customers’ personal finance education.

So, when the request came to write a how-to article about doing accounting in Excel, I called our company’s certified public accountant (CPA), who advises us on financial matters and prepares our taxes. “Do any of your clients use Excel to do real accounting?” I asked. “Absolutely not,” he said. “That would be insane.”

What Is Excel Accounting?

Excel is an excellent accounting tool for a self-employed individual or a very small business with only basic accounting requirements because it is simple to use yet infinitely malleable. And for the many small businesses using the Microsoft 365 Office suite, it’s already paid for, while purpose-built accounting software would be an additional cost. Yes, Excel can also be set up to generate full-blown financial statements using true double-entry accounting methods. But the effort necessary to build such accounting tools in Excel and the manual workload necessary to do the actual accounting operations would be costly in labor hours and would bring significant risk of accounting errors.

It’s Excel’s flexibility and familiarity that make it attractive to individuals and very small businesses. If a business is so small that the only financial document it’s likely to produce for external consumption is a tax return, it need not comply with the rules of U.S. Generally Accepted Accounting Principles (GAAP). Instead, it can set up Excel spreadsheets in ways that precisely, and simply, mirror the nature of its business operations. The business can use Excel to record all its financial transactions — essentially, customer payments coming in and expenses going out.

That’s exactly what I did for my business. My first Excel accounting effort was a file containing five worksheet tabs:

• Receivables listed all invoices sent to clients, with columns for our invoice number, the client’s name, a description of the work, the amount invoiced, the date the invoice was sent and the date payment was received. (Note that “receivables” and “payables” are accrual-basis accounting terms that I misapplied for my cash-basis business out of my ignorance back then.)
• Income showed all client payments, with columns for date received, client name, amount and invoice number.
• People payables were for all payments made to contractors, with columns for their invoice number, name, amount paid, date paid and services performed. At the time, there were no employees, hence no payroll.
• Expenses were for other (non-contractor) business expenses, with columns for date, amount, type of expense and a description of the expense.
• Distributions were the payments the business made to me — essentially, my salary — with columns for date, amount and description (if needed).

Again, it’s important to note that these five worksheets do not constitute real accounting — for example, there is no balance sheet to account for the business’s assets, nor is there an income statement showing profit and loss. Nonetheless, our CPA was delighted with the excellent quality of those records because they enabled him to calculate everything he needed to file the business’s tax returns with the IRS and New York state.

Key Takeaways

• Microsoft Excel can be a great accounting tool for self-employed entrepreneurs or very small businesses that need not comply with standard accounting principles.
• It is possible to set up Excel for double-entry accounting, with a proper chart of accounts, general journal, general ledger, trial balance, income statement, balance sheet and statement of cash flows.
• But doing so would take significant effort and bring risk of accounting errors to the business, making purpose-built accounting software a far superior alternative.
• Using Excel for accounting requires strong bookkeeping knowledge to compensate for the lack of built-in accounting guidance and controls included in accounting software.

Excel Accounting Explained

Because Excel starts as a blank canvas, and all of the forms, formulas and transaction data for a business’s accounting must be developed and entered manually, accounting in Excel is best-suited for small and relatively simple businesses that don’t, for example, carry inventory. In accounting terms, this means cash-basis accounting with single-entry bookkeeping. Accrual-basis accounting, which the IRS requires for companies at or above $27 million (indexed to inflation) in annual revenue, as well as smaller companies that have inventory, requires double-entry bookkeeping. That makes it exponentially harder to do in Excel because double-entry bookkeeping demands that multiple manual data entries be made in different types of accounts for a single business transaction (discussed in more detail below in the “How to: Accrual-Basis Accounting in Excel” section). That volume of manual data entry is why my CPA said doing so “would be insane.” 

To help small businesses overcome the “blank canvas” challenge, Microsoft and many third parties provide free, downloadable Excel accounting templates. These include templates for the basic documents a business needs to get started doing “real” cash-basis accounting: a chart of accounts , which lists all of the business’s categories of revenue and expenses; a cash book — sometimes called a general journal or transaction sheet — that lists all financial transactions along with their categories from the chart of accounts; and an income statement that summarizes revenue and expenses for a specific period to show the business’s profit or loss.

Some businesses, like mine, separately track accounts payable and accounts receivable (respectively, money the business owes to suppliers and money owed to the business by customers) to make managing collections and payments easier. Free templates are available for accounts payable and accounts receivable worksheets, too.

Excel vs. Accounting Software

Ironically, small business entrepreneurs who lack accounting expertise often gravitate toward using Excel instead of buying accounting software because they think it’ll be simpler and easier. But Excel’s blank-canvas issue makes the opposite true. If you’re not already an expert bookkeeper or a CPA, it’s impossible to do real accounting in Excel because of myriad rules that are hard to understand for people who don’t have the formal accounting education and training. Accounting software, on the other hand, comes with accounting rules preprogrammed into it, so even inexpert users are prevented from making most basic mistakes. 

What Excel Can and Can’t Do

For me, Excel has been a great accounting tool in two ways: tracking cash transactions and performing ad-hoc analysis. Recording the business’s cash transactions in Excel enabled our CPA to quickly and easily generate the cash-basis forms needed for federal and state tax returns. But only 28 contractor payment transactions were made to eight contractors our first year; in 2022, that grew to 319 payments to 50 different contractors. Keeping up with all those transactions, manually, has become a time-consuming chore. Also in 2022 were material errors for the first time, like failing to pay an invoice until the contractor called asking what happened to her missing payment.

The ad-hoc analyses we do are things like checking the “Receivables” sheet to get a rough estimate of how much cash to expect from customers in the next month, or comparing year-to-date customer payments to the same period last year to track revenue growth. Of course, these are so simple that it’s a stretch to label them “analyses” — my estimate of incoming cash is nothing like a serious cash-flow analysis — but they are all our business has needed until now.

In 2023, it has become clear that, in order to manage our resources more efficiently so that we can continue our profitable growth, we need monthly, client-specific profit-and-loss (aka, income ) statements. By default, that means accrual-basis accounting. But we haven’t actually converted from cash basis to accrual basis ; instead, we’re doing monthly P&Ls as a sort of overlay. In addition to our cash-basis transaction tracking, we create a separate worksheet for each client listing all of the deliverables that earned revenue from that client that month, along with the amount earned and the expenses incurred to generate it — regardless of when we paid (or will pay) the expense or when we received (or will receive) the client’s payment. That, in essence, is accrual-basis accounting: the combination of the matching principle and the revenue recognition principle that are core to GAAP. What we do in Excel is a crude facsimile, and even that is painful to construct by hand.

Consequently, we’re working with a consulting CPA to take a hard look at how to convert to accrual-basis accounting and the software necessary to do so. Here’s the list she provided of the advantages accounting software can bring to the table versus Excel:

• Automation: Accounting software automates many bookkeeping and accounting tasks, such as generating invoices, tracking expenses and reconciling bank transactions. Compared to Excel, this significantly reduces manual data entry and the risk of errors.
• Double-entry accounting: Accounting software is usually designed for double-entry, accrual-basis accounting, which provides indispensable support for GAAP’s matching and revenue recognition principles . Those principles, in turn, enable accrual-basis accounting to produce more accurate and complete pictures of a business’s financial health compared to single-entry accounting in Excel.
• Security: Most accounting software can be configured with robust identity and access management controls that limit users’ access to sensitive information. Excel files can be opened by anyone who has them, so the business must develop independent security controls. This becomes a major concern as an organization grows.
• Backup: Especially in the cloud, accounting software provides a backup of records, rather than locally saved Excel spreadsheets that can become corrupted, deleted or stolen. Again, the business would have to develop independent processes for backup.
• Scalability: Accounting software is built to handle large numbers of accounts and transactions, but because prices are often tied to the number of users accessing the system, businesses can afford to buy in while still small and let the software scale as their organizations grow.
• Real-time reporting: Because of its inherent automation capabilities, accounting software can generate balance sheets, income statements, cash flow statements and custom reports in real time, making it easier to monitor a company’s financial performance .
• Integrations: Accounting software often integrates with other business applications, such as payroll, inventory management and customer relationship management (CRM) systems. By sharing data among those systems, a business can automate many business processes that would otherwise require human action and multiple data entries with Excel.
• Tax compliance: Many accounting software solutions include features for tax preparation that automate compliance with necessary tax rules.
• Multiuser access: Multiple users can access accounting software simultaneously, enabling collaboration and more efficient workflows. Access controls and permission levels can be set to allow each individual user access to only the level needed for their role, ensuring data security and accountability.
• Auditing: Accounting software maintains a detailed audit trail, making it easier to track changes to financial records and providing valuable information for internal or external audits .
• Industry-specific capabilities: Some accounting software solutions offer industry-specific features, such as specialized reporting or unique workflows, which are not available in Excel.

How To: Cash-Basis Accounting in Excel

The primary focus of cash-basis accounting is the flow of cash into and out of a business. Cash-basis companies account for revenue when they receive it and for expenses when they’re paid. In theory, they need only enter each transaction once. In practice, though, any serious growing business doing its accounting in Excel will end up entering transactions in a few different worksheets so that owners and managers get a clearer view of the business’s near-term health than they could by looking only at the inflow and outflow of cash.

So for the purpose of this how-to scenario for cash-basis Excel accounting, consider a hypothetical business — Wantagh Widgets, Inc. — that establishes the following five Excel worksheets: a chart of accounts, a transaction journal, a customer invoice log, a purchases log and an income statement.

The owner of Wantagh Widgets begins by setting up the chart of accounts, which will list all of the accounts it will use to record transactions. For cash-basis single-entry accounting, a business will need at least two main categories: revenue and expenses. Subcategories within each main category would better organize transactions for later analysis. For example, revenue might have subcategories for product and service revenue or for each customer, depending on the nature of the business. Expense subcategories might include payroll, supplier payments, rent, utilities and office supplies, to name a few possibilities. A more complete list, with good alignment among the categories and the way the business operates in the real world, will later produce financial statements that are clearer and more accurately reflect the health of the business. 

While obviously oversimplified, the chart of accounts for Wantagh Widgets might look like this:

The core bookkeeping activity for Wantagh Widgets’s Excel accounting will be to record all financial transactions in the transaction journal as they occur, using the account numbers from the chart of accounts. Each journal entry should include the date, description, account number and amount. Say Wantagh Widgets received four customer payments of $5,000 each during March 2023 and paid out $9,000 in four supplier payments, $6,500 in payroll costs and $1,000 in rent. Its transaction journal worksheet might look like this:

While cash-basis accounting focuses on when cash is received, it’s still important to monitor outstanding payments. The customer invoice log worksheet (equivalent to accounts receivable in accrual-basis accounting) tracks money owed to a business by its customers. It should be updated when the business invoices a customer and when it receives payment. Wantagh Widgets’s customer invoice log for the business activity discussed so far might look like this:

As with the customer invoice log, the purchases log is the cash-basis equivalent of the accrual basis’s accounts payable worksheet, helping the business keep track of bills and debts owed by the business. Invoices are recorded when they’re received, along with their due dates. Then the worksheet is updated when payments are sent. Wantagh Widgets’s purchases log for the business activity discussed so far might look like this:

An income statement summarizes the business’s financial performance over a specified period. In cash-basis accounting, only income and expenses that were actually received and paid during the period are included. The hypothetical March 2023 income statement below for Wantagh Widgets was created by transferring that month’s income and expense amounts from the transaction journal to the appropriate categories on the income statement worksheet. Then formulas in the spreadsheet calculate the total income, total expenses and net income (or loss), expressed in dollars and as a percentage of total revenue.

These five hypothetical statements are dramatically simpler than a real business would require and are meant only to illustrate the relevant cash-basis accounting concepts. A real business would likely flesh out its chart of accounts with several more sources of revenue and dozens, or even hundreds, of expense categories. But that’s just more of the same in terms of the five examples. The underlying formulas involved are simply addition, subtraction and division, and data can be relayed from worksheet to worksheet. For example, the income statement can be programmed with formulas that grab all of the needed information from the transaction journal, as guided by the unique account numbers in the chart of accounts.

Accounting becomes exponentially more complex when using the accrual-basis method required by GAAP and by the Securities and Exchange Commission for all U.S. public companies.

How To: Accrual-Basis Accounting in Excel

The primary focus of accrual-basis accounting is to paint the most accurate picture possible of a business’s financial health, for both the period being covered by the accounting data and, by extrapolation, for future periods. To do so, accrual-basis accounting attempts to reflect the true value of all of the business’s activities for the period, regardless of the timing of when revenue is received or expenses are paid. This is where the two previously mentioned core principles, revenue recognition and matching, come into play. But even though these principles are simple — recognize revenue when it is earned and expenses when they are incurred (so that expenses match, in the same period, to the revenue they generated) — applying them is what makes accrual-basis accounting more work than any sane individual would attempt to perform using Excel.

For example, the following two illustrations are accrual-basis versions of Wantagh Widgets’s chart of accounts and transaction journal. Note that the hypothetical data presented in these two charts reflects business activity that is identical to that of their cash-basis counterparts shown in the previous section. They’re expanded only enough to show the minimal additions necessary to perform accrual-basis accounting on the same activity.

The chart of accounts now has expanded to include categories for assets, liabilities and equity to address the fundamental accounting equation: assets = liabilities + equity. Before customers pay their bills, for example, the monies due from them are considered an asset in the accounts receivable account. And when an expense is incurred but not yet paid, it is considered a liability in the accounts payable and accrued expenses accounts. By accounting for all of a business’s activities in this way, even if no money has changed hands, accountants can construct financial statements that reflect the true health of the business — i.e., revenue growth, profitability and the sources of both.

Now, review the accrual-basis accounting journal pictured below.

The 11 entries seen in the cash-basis journal doubled to 22, because each requires a debit and a credit . (Debits increase the value of asset and expense accounts and decrease the value of liability, revenue and equity accounts, while credits have the opposite effect. Debits and credits must balance each other out.) Further, the entries are categorized under a larger set of accounts (the account numbers shown correspond to categories assigned in the chart of accounts). But the accrual-basis journal has additional entries because it must account for four customer invoices that Wantagh Widgets issued in March but have yet to be paid (journal entries 00002 through 00005) and two supplier bills that were received but also remain unpaid (entries 00015 and 00016).

In addition, accrual-basis accounting would require the following Excel worksheets:

• General ledger , which has separate worksheets for each account in the chart of accounts and repeats each entry in the journal above as a debit or a credit in its corresponding general-ledger account.
• Trial balance , which summarizes the debits and credits for all of the accounts in the general ledger and is used to test whether all of the debits equals all of the credits, identify any discrepancies and make appropriate adjustments before financial statements are issued.
• Accounts receivable , which reflects monies owed to the company by customers.
• Accounts payable , which reflects monies the company owes its suppliers.
• Balance sheet , which shows the company’s assets, liabilities and equity at a specific point in time.
• Income statement , which summarizes revenues and expenses for a specific period, resulting in the bottom line — the company’s net income or loss.
• Statement of cash flows , which provides an overview of the business’s cash inflows and outflows during a specific period. It exists to convert the accrual-basis income statement into cash terms so that business managers can properly manage the company’s cash flow.

The complexity of accrual-basis accounting and the sheer volume of manual data entry required for any but the smallest of businesses are why accounting software is a far better solution than Excel for accrual-basis companies.

Most Useful Excel Accounting Formulas and Formats

When putting together a company’s cash transaction journal, sales and purchases journals and financial statements, the most useful Excel formulas are basic addition (sum) and subtraction. Over time, it’s also useful to calculate averages and percentages, such as the average value of customer purchases or the business’s net income as a percentage of revenue.

To enter a formula in Excel, the first character you type must be an equal sign (“=”). To sum a vertical set of 10 cells starting from cell A2 and ending on cell A11, you would type “=SUM(A2:A11).” Similarly, to sum a horizontal set of 10 cells starting from A2 you would type “=SUM(A2:K2).” To calculate an average, simply substitute the word “AVERAGE” for “SUM” and then define the range of cells to include in the computation.

Subtraction and percentage formulas usually involve only two cells (because you’ve previously summed or averaged the values you want to use) and, therefore, a different calculation approach. For subtraction, you choose the two cells and use the minus operator (“-”). The image below on the left reiterates the Wantagh Widgets Income Statement but with the subtraction formula, “=F9-F17,” exposed. For the percentage, you choose cells representing the part whose percentage you wish to know and the whole of which it is part and use the division operator (“/”). The second image below shows the percentage formula exposed; net income is the part whose percentage is being calculated and total revenue is the whole of which it is a part.

It’s common practice to format dollar amounts to two decimals places, using Excel’s currency formatting with right justification. Positive amounts or cash inflows are typically in black, while negative amounts or disbursements are typically in red and have parenthesis. Subtotals are single-underlined while grand totals (like net income) are double-underlined. (The double underlines in the accompanying images are obscured when the formulas are visible.) Account numbers use a general format, without commas or other notations. Dates are typically mm/dd/yyyy.

Other helpful Excel features are the sort function and pivot tables. Sorting by account number can help with analysis and aggregating data into line items for financial statements. For example, a simple sort can group invoices that have been paid by customers from those that are still outstanding. For more advanced Excel users, pivot tables can be created to quickly summarize data in multiple dimensions, such as extracting the unpaid invoices from the sales journal, summarized by customer and listed in chronological order by date billed. In such cases, it’s helpful to copy and paste data into separate worksheets before performing further analysis so as to preserve the original data.

How to Know When You Need Accounting Software

Several excellent “rules of thumb” can make it obvious when a business using Excel for accounting should move up to accounting software. For example, does the business need to share financial statements with outside stakeholders, like lenders, investors or potential buyers? Those statements would need to be GAAP-compliant, which demands accrual-basis accounting. In the U.S., IRS rules require C corporations, any companies that carry inventory or any business with $27 million or more in annual revenue to use accrual-basis accounting. As this article has established, using Excel for accrual-basis accounting would be nonsensical. 

Other considerations include the complexity of the business, growth in the number of business transactions requiring manual data entry in Excel and the number of employees who need access to financial information. As any or all of these business attributes rise, the case for switching to accounting software becomes stronger. Similarly, if the business is concerned about the security of financial data or has questions about its accuracy (due to the inevitable errors that arise from higher volumes of manual data entry), it’s time to consider accounting software.

Graduate From Excel With NetSuite Accounting Software

For any business still doing accounting in Excel, adopting a cloud-based accounting solution like NetSuite will improve the efficiency, accuracy and decision-making of its financial management. NetSuite accounting software can automatically generate invoices based on customer activity, track expenses and reconcile banking transactions with the company’s books. It automates and enforces compliance with GAAP — or International Financial Reporting Standards if the business is subject to IFRS — and offers role-based access management for greater security. It automates the generation of financial reports, which can actually toggle between cash and accrual basis views and be updated in real time to extend business managers’ visibility into their financials right up to the minute. All of these attributes help to improve the speed of a business’s monthly close. Best yet for a small growing business, NetSuite cloud accounting is scalable — it’s affordable for small businesses with few users but can grow along with the business to support very large, publicly traded enterprises. And finally, it can grow “horizontally,” too, meaning it can support and automate a range of business processes, such as human resources, customer relationship management, inventory management and procurement, as the business expands into those areas.

Accounting in Excel has served my cash-basis business well for seven years but is now hitting the wall. As the business has grown in terms of the number of customers and transactions, the different products and services offered and the number of employees and contractors, the volume of manual data entry and analysis has become burdensome and errors have begun to pile up. At the same time, we’ve realized we have a need to better understand the profitability of our business activities each month and can foresee the day when we’ll need to switch to accrual-basis accounting. For all of these reasons, our next step will be to upgrade from Excel to accounting software. 

Excel Accounting FAQs

Can Excel be used for accounting?

Yes, Excel can be suited to the cash-basis accounting requirements of many self-employed individuals and very small businesses. But once a business grows beyond that, has a larger number of transactions or carries inventory, it’s likely to require accrual-basis accounting. Excel is not a good fit for accrual-basis accounting.

What are the accounting formulas in Excel?

Although accounting has many rules and guidelines to follow, the actual math required usually isn’t fancy. Basic addition, subtraction, averages and percentage calculations are the main formulas you’ll program into Excel worksheets to perform most accounting tasks.

How should businesses treat retained earnings?

A business’s retained earnings are its accumulated undistributed net income and are considered owner’s equity. Companies usually use retained earnings to reinvest in the business, as emergency funds or to pay off debts. For reporting purposes, they appear in the equity section of the balance sheet.

How do you use accounting in Excel?

Businesses with simple, basic accounting requirements can use Excel by establishing worksheets for a chart of accounts plus any additional records the organization needs to track, which will depend on the nature of the business. For example, most businesses will require a transaction journal to track cash payments made to the company and expenses paid out, a customer invoice log, a purchases log and an income statement, all of which can be established as separate worksheets within an Excel workbook. If using Excel, however, all of the bookkeeping data necessary to track business transactions must be entered manually into those worksheets.

How do you create an accounting account in Excel?

In accounting, accounts are discrete records used to document financial transactions that relate to a particular aspect of business activity, such as customer payments and supplier payments, or, in accrual-basis accounting, accounts receivable, accounts payable, assets, liabilities and equity. Creating any such account in Excel requires opening a new workbook or worksheet within a workbook and formatting it with the right headers and formulas for that account type. The account must also be listed in a chart of accounts, with an appropriate account number for tracking the account’s transactions across the multiple worksheets in which they may appear.