Accounting: When It’s Time to Move On from Excel (2024)

I own a small business with five full-time employees, several dozen freelance contractors anda 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 realaccounting — it’s transaction tracking. I’ve learned all about realaccounting from professionals my company has worked with over the years to produce expertaccounting content for multiple clients, from a Big 4 accounting firm to a consumer financeprovider 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 calledour company’s certified public accountant (CPA), who advises us on financial mattersand prepares our taxes. “Do any of your clients use Excel to do realaccounting?” I asked. “Absolutely not,” he said. “That would beinsane.”

What Is Excel Accounting?

Excel is an excellent accounting tool for a self-employed individual or a very small businesswith 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 alreadypaid for, while purpose-built accounting software would be an additional cost. Yes, Excelcan also be set up to generate full-blown financial statements using true double-entryaccounting methods. But the effort necessary to build such accounting tools in Excel and themanual workload necessary to do the actual accounting operations would be costly in labor hours and would bringsignificant risk of accounting errors.

It’s Excel’s flexibility and familiarity that make it attractive to individualsand very small businesses. If a business is so small that the only financial documentit’s likely to produce for external consumption is a tax return, it need not complywith the rules of U.S. Generally Accepted Accounting Principles (GAAP). Instead, it can set up Excelspreadsheets in ways that precisely, and simply, mirror the nature of its businessoperations. 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 filecontaining five worksheet tabs:

• Receivables listed all invoices sent to clients, with columns for ourinvoice 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 termsthat 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, withcolumns 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, withcolumns 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 (ifneeded).

Again, it’s important to note that these five worksheets do not constitute realaccounting — for example, there is no balance sheet to account for the business’s assets, nor is there an incomestatement showing profit and loss. Nonetheless, our CPA was delighted with the excellentquality of those records because they enabled him to calculate everything he needed to filethe 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 verysmall businesses that need not comply with standard accounting principles.
• It is possible to set up Excel for double-entry accounting, with a proper chart ofaccounts, general journal, general ledger, trial balance, income statement, balancesheet and statement of cash flows.
• But doing so would take significant effort and bring risk of accounting errors to thebusiness, making purpose-built accounting software a far superior alternative.
• Using Excel for accounting requires strong bookkeeping knowledge to compensate for thelack 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 datafor a business’s accounting must be developed and entered manually, accounting inExcel is best-suited for small and relatively simple businesses that don’t, forexample, carry inventory. In accounting terms, this means cash-basis accounting withsingle-entry bookkeeping. Accrual-basis accounting, which the IRS requires for companies ator above $27 million (indexed to inflation) in annual revenue, as well as smaller companiesthat have inventory, requires double-entry bookkeeping. That makes it exponentially harderto do in Excel because double-entry bookkeeping demands that multiple manual data entries bemade in different types of accounts for a single business transaction (discussed in moredetail below in the “How to: Accrual-Basis Accounting in Excel” section). Thatvolume of manual data entry is why my CPA said doing so “would be insane.”

To help small businesses overcome the “blank canvas” challenge, Microsoft andmany third parties provide free, downloadable Excel accounting templates. These includetemplates 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 revenueand expenses; a cash book — sometimes called a general journal or transaction sheet— that lists all financial transactions along with their categories from the chart ofaccounts; and an income statement that summarizes revenue and expenses for a specific periodto show the business’s profit or loss.

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

Excel vs. Accounting Software

Ironically, small business entrepreneurs who lack accounting expertise often gravitate towardusing Excel instead of buying accounting software because they think it’ll be simplerand easier. But Excel’s blank-canvas issue makes the opposite true. If you’renot already an expert bookkeeper or a CPA, it’s impossible to do real accounting inExcel because of myriad rules that are hard to understand for people who don’t havethe formal accounting education and training. Accounting software, on the other hand, comeswith accounting rules preprogrammed into it, so even inexpert users are prevented frommaking 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 andperforming ad-hoc analysis. Recording the business’s cash transactions in Excelenabled our CPA to quickly and easily generate the cash-basis forms needed for federal andstate tax returns. But only 28 contractor payment transactions were made to eightcontractors 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 in2022 were material errors for the first time, like failing to pay an invoice until thecontractor called asking what happened to her missing payment.

The ad-hoc analyses we do are things like checking the “Receivables” sheet to geta rough estimate of how much cash to expect from customers in the next month, or comparingyear-to-date customer payments to the same period last year to track revenue growth. Ofcourse, 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 thatwe can continue our profitable growth, we need monthly, client-specific profit-and-loss(aka, income ) statements. By default, that means accrual-basis accounting. But wehaven’t actually converted from cash basis to accrual basis ; instead, we’re doing monthly P&Ls as a sortof overlay. In addition to our cash-basis transaction tracking, we create a separateworksheet for each client listing all of the deliverables that earned revenue from thatclient 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 willreceive) the client’s payment. That, in essence, is accrual-basis accounting: thecombination of the matching principle and the revenue recognition principle that are core toGAAP. What we do in Excel is a crude facsimile, and even that is painful to construct byhand.

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

• Automation: Accounting software automates many bookkeeping and accounting tasks, such asgenerating invoices, tracking expenses and reconciling bank transactions. Compared toExcel, this significantly reduces manual data entry and the risk of errors.
• Double-entry accounting: Accounting software is usually designed fordouble-entry, accrual-basis accounting, which provides indispensable support for GAAP’s matching and revenue recognition principles . Those principles, inturn, enable accrual-basis accounting to produce more accurate and complete pictures ofa business’s financial health compared to single-entry accounting in Excel.
• Security: Most accounting software can be configured with robustidentity and access management controls that limit users’ access to sensitiveinformation. Excel files can be opened by anyone who has them, so the business mustdevelop independent security controls. This becomes a major concern as an organizationgrows.
• Backup: Especially in the cloud, accounting software provides abackup of records, rather than locally saved Excel spreadsheets that can becomecorrupted, deleted or stolen. Again, the business would have to develop independentprocesses for backup.
• Scalability: Accounting software is built to handle large numbers ofaccounts and transactions, but because prices are often tied to the number of usersaccessing the system, businesses can afford to buy in while still small and let thesoftware scale as their organizations grow.
• Real-time reporting: Because of its inherent automation capabilities,accounting software can generate balance sheets, income statements, cash flow statementsand custom reports in real time, making it easier to monitor a company’s financial performance .
• Integrations: Accounting software often integrates with otherbusiness applications, such as payroll, inventory management and customer relationshipmanagement (CRM) systems. By sharing data among those systems, a business can automatemany business processes that would otherwise require human action and multiple dataentries with Excel.
• Tax compliance: Many accounting software solutions include features for tax preparation that automate compliance with necessarytax rules.
• Multiuser access: Multiple users can access accounting softwaresimultaneously, enabling collaboration and more efficient workflows. Access controls andpermission levels can be set to allow each individual user access to only the levelneeded 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 valuableinformation for internal or external audits .
• Industry-specific capabilities: Some accounting software solutionsoffer 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-basiscompanies 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 seriousgrowing business doing its accounting in Excel will end up entering transactions in a fewdifferent worksheets so that owners and managers get a clearer view of the business’snear-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 ahypothetical business — Wantagh Widgets, Inc. — that establishes the followingfive Excel worksheets: a chart of accounts, a transaction journal, a customer invoice log, apurchases log and an income statement.

The owner of Wantagh Widgets begins by setting up the chart of accounts, which will list allof 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. Subcategorieswithin each main category would better organize transactions for later analysis. Forexample, revenue might have subcategories for product and service revenue or for eachcustomer, depending on the nature of the business. Expense subcategories might includepayroll, supplier payments, rent, utilities and office supplies, to name a fewpossibilities. A more complete list, with good alignment among the categories and the waythe business operates in the real world, will later produce financial statements that areclearer and more accurately reflect the health of the business.

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

The core bookkeeping activity for Wantagh Widgets’s Excel accounting will be to recordall financial transactions in the transaction journal as they occur, using the accountnumbers 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,000each during March 2023 and paid out $9,000 in four supplier payments, $6,500 in payrollcosts 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 tomonitor outstanding payments. The customer invoice log worksheet (equivalent to accountsreceivable in accrual-basis accounting) tracks money owed to a business by its customers. Itshould 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 farmight look like this:

As with the customer invoice log, the purchases log is the cash-basis equivalent of theaccrual basis’s accounts payable worksheet, helping the business keep track of billsand debts owed by the business. Invoices are recorded when they’re received, alongwith their due dates. Then the worksheet is updated when payments are sent. WantaghWidgets’s purchases log for the business activity discussed so far might look likethis:

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

These five hypothetical statements are dramatically simpler than a real business wouldrequire and are meant only to illustrate the relevant cash-basis accounting concepts. A realbusiness would likely flesh out its chart of accounts with several more sources of revenueand dozens, or even hundreds, of expense categories. But that’s just more of the samein 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 informationfrom the transaction journal, as guided by the unique account numbers in the chart ofaccounts.

Accounting becomes exponentially more complex when using the accrual-basis method required byGAAP 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 abusiness’s financial health, for both the period being covered by the accounting dataand, by extrapolation, for future periods. To do so, accrual-basis accounting attempts toreflect the true value of all of the business’s activities for the period, regardlessof the timing of when revenue is received or expenses are paid. This is where the twopreviously mentioned core principles, revenue recognition and matching, come into play. Buteven though these principles are simple — recognize revenue when it is earned andexpenses when they are incurred (so that expenses match, in the same period, to the revenuethey generated) — applying them is what makes accrual-basis accounting more work thanany sane individual would attempt to perform using Excel.

For example, the following two illustrations are accrual-basis versions of WantaghWidgets’s chart of accounts and transaction journal. Note that the hypothetical datapresented in these two charts reflects business activity that is identical to that of theircash-basis counterparts shown in the previous section. They’re expanded only enough toshow the minimal additions necessary to perform accrual-basis accounting on the sameactivity.

The chart of accounts now has expanded to include categories for assets, liabilities andequity to address the fundamental accounting equation: assets = liabilities + equity. Beforecustomers pay their bills, for example, the monies due from them are considered an asset inthe accounts receivable account. And when an expense is incurred but not yet paid, it isconsidered a liability in the accounts payable and accrued expenses accounts. By accountingfor 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 anddecrease the value of liability, revenue and equity accounts, while credits have theopposite effect. Debits and credits must balance each other out.) Further, the entries arecategorized under a larger set of accounts (the account numbers shown correspond tocategories assigned in the chart of accounts). But the accrual-basis journal has additionalentries because it must account for four customer invoices that Wantagh Widgets issued inMarch but have yet to be paid (journal entries 00002 through 00005) and two supplier billsthat 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 inthe chart of accounts and repeats each entry in the journal above as a debit or a creditin its corresponding general-ledger account.
• Trial balance , which summarizes the debits and credits for all ofthe accounts in the general ledger and is used to test whether all of the debits equalsall of the credits, identify any discrepancies and make appropriate adjustments beforefinancial statements are issued.
• Accounts receivable , which reflects monies owed to the company bycustomers.
• Accounts payable , which reflects monies the company owes itssuppliers.
• Balance sheet , which shows the company’s assets, liabilitiesand equity at a specific point in time.
• Income statement , which summarizes revenues and expenses for aspecific period, resulting in the bottom line — the company’s net income orloss.
• Statement of cash flows , which provides an overview of thebusiness’s cash inflows and outflows during a specific period. It exists toconvert the accrual-basis income statement into cash terms so that business managers canproperly manage the company’s cash flow.

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

Most Useful Excel Accounting Formulas and Formats

When putting together a company’s cash transaction journal, sales and purchasesjournals 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 apercentage 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 oncell A11, you would type “=SUM(A2:A11).” Similarly, to sum a horizontal set of10 cells starting from A2 you would type “=SUM(A2:K2).” To calculate an average,simply substitute the word “AVERAGE” for “SUM” and then define therange of cells to include in the computation.

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

It’s common practice to format dollar amounts to two decimals places, usingExcel’s currency formatting with right justification. Positive amounts or cash inflowsare typically in black, while negative amounts or disbursem*nts are typically in red andhave parenthesis. Subtotals are single-underlined while grand totals (like net income) aredouble-underlined. (The double underlines in the accompanying images are obscured when theformulas are visible.) Account numbers use a general format, without commas or othernotations. Dates are typically mm/dd/yyyy.

Other helpful Excel features are the sort function and pivot tables. Sorting by accountnumber 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 thosethat are still outstanding. For more advanced Excel users, pivottables can be created to quickly summarize data in multiple dimensions, such asextracting the unpaid invoices from the sales journal, summarized by customer and listed inchronological order by date billed. In such cases, it’s helpful to copy and paste datainto separate worksheets before performing further analysis so as to preserve the originaldata.

How to Know When You Need Accounting Software

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

Other considerations include the complexity of the business, growth in the number of businesstransactions requiring manual data entry in Excel and the number of employees who needaccess to financial information. As any or all of these business attributes rise, the casefor switching to accounting software becomes stronger. Similarly, if the business isconcerned about the security of financial data or has questions about its accuracy (due to the inevitable errors that arise from highervolumes 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 anddecision-making of its financial management. NetSuite accounting software can automaticallygenerate invoices based on customer activity, track expenses and reconcile bankingtransactions with the company’s books. It automates and enforces compliance with GAAP—or International Financial Reporting Standards if the business is subject toIFRS — and offers role-based access management for greater security. It automates thegeneration of financial reports, which can actually toggle between cash and accrual basisviews and be updated in real time to extend business managers’ visibility into theirfinancials right up to the minute. All of these attributes help to improve the speed of abusiness’s monthly close. Best yet for a small growing business, NetSuite cloudaccounting is scalable — it’s affordable for small businesses with few users butcan grow along with the business to support very large, publicly traded enterprises. Andfinally, it can grow “horizontally,” too, meaning it can support and automate arange 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 hittingthe 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, thevolume of manual data entry and analysis has become burdensome and errors have begun to pileup. At the same time, we’ve realized we have a need to better understand theprofitability of our business activities each month and can foresee the day when we’llneed to switch to accrual-basis accounting. For all of these reasons, our next step will beto 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-employedindividuals and very small businesses. But once a business grows beyond that, has a largernumber of transactions or carries inventory, it’s likely to require accrual-basisaccounting. 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 usuallyisn’t fancy. Basic addition, subtraction, averages and percentage calculations are themain 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 areconsidered owner’s equity. Companies usually use retained earnings to reinvest in thebusiness, as emergency funds or to pay off debts. For reporting purposes, they appear in theequity section of the balance sheet.

How do you use accounting in Excel?

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

How do you create an accounting account in Excel?

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