5 Approaches to Use Excel for Small Company Accounting

Accountants are known for sporting pocket protectors and expending their times operating in Microsoft Excel. I can vouch for the latter stereotype.

a person sitting at a desk in front of a laptop computer: Businesswoman analyzing spreadsheets on computer

© Getty Pictures
Businesswoman analyzing spreadsheets on laptop

Quite a few compact organizations swear by Excel for all of their bookkeeping demands. Even though accounting application has its position in each organization, you can benefit from introducing Excel into your life.

3 rewards of working with Excel for accounting

Excel is a powerful device that can assist any person doing work with figures.

1. Streamlined facts entry

I’m not getting something away from the relieve of use that’s turn out to be normal in small business accounting program, but you’d be tough-pressed to uncover a method far better suited to mass knowledge entry than Excel.

Excel electricity customers know that macros can churn out several hours of a human’s operate in moments. Business enterprise homeowners can produce macros to automate responsibilities, these types of as formatting, filtering, and functioning primary assessment.

You really do not want to know how to set up macros to reward from Excel’s operation. As we’ll discover down below, Excel remains a single of the most effective techniques to import large transaction datasets into your accounting software.

2. Visible financial investigation

Money investigation does not have to be a list of accounting ratios that assess your company’s profitability. If you are a visual learner, use Excel to build pie charts that show wherever your business’s money is going.

Nonprofit annual reports frequently use visible aids to converse in which they spend their income. For-financial gain providers might prepare similar studies when pitching investors.

3. Quick sharing

Excel is the universal currency of accounting. When you have to have to share fiscal info with a loan provider or trader, you’ll want to mail them possibly a PDF or Excel file.

3 most handy capabilities for Excel little business enterprise accounting

I could go on for eons about the Excel features that small enterprises can leverage for accounting. Here are my 3 favorites.

1. Templates

Microsoft has 1000’s of free Excel templates on its web-site. Rather than having to layout your own invoices and price stories from scratch, you can download and tweak another person else’s generation.

Templates are useful for all those who aren’t snug navigating Excel or haven’t however aced Accounting 101. Creating your individual accounting templates needs 100{f13b67734a7459ff15bce07f17c500e58f5449212eae0f7769c5b6fbcf4cc0c4} precision if a person mobile isn’t operating accurately, you may wind up with errors in your accounting records. Templates are plug-and-perform with pre-stuffed formulation.

2. Formulation

Excel formulation considerably velocity up company expenditure monitoring and assessment.

Say you are looking at the financial institution statement under and want to know how considerably the organization used on stock purchases. Alternatively than including up a checklist of the relevant costs utilizing a calculator, you can use the “SUMIF” method, instructing it only to add bills marked as stock purchases.

table: An Excel spreadsheet shows a SUMIF formula.

© Delivered by The Blueprint
An Excel spreadsheet reveals a SUMIF formulation.

You can obtain the considerable library of Excel functions by clicking on the components button at the major of any Excel site. It will provide up a aspect panel exactly where you can research and learn how to use the perform.

graphical user interface, table: A Microsoft Excel window with the formula button boxed in red.

© Supplied by The Blueprint
A Microsoft Excel window with the system button boxed in red.

3. PivotTables

I have saved the ideal for final. PivotTables, the most effective Excel function, reorganizes data to existing it additional logically. Accountants use the resource to system financial institution statements and other economic knowledge.

For example, lender statements ordinarily checklist transactions chronologically, which does not aid you have an understanding of in which your money went that thirty day period.

I produced a PivotTable beneath to categorize profits and fees on the adjacent lender assertion. It collapsed the purchase and stock transactions into 1 line to delineate how significantly the organization acquired in income and put in on stock purchases. The full row demonstrates the company’s net money for the thirty day period.

graphical user interface, application, table, Excel: A PivotTable lists the categories in a bank statement.

© Furnished by The Blueprint
A PivotTable lists the classes in a financial institution statement.

The instance shows just a month’s well worth of transactions, but envision your spreadsheet lists of transactions for an total yr. You could use a PivotTable to individual your transactions by month to much better have an understanding of your company’s funds movement. That would acquire hours by hand, but it’s only moments with PivotTables.

How to use Excel for your little company accounting requires

Excel can assistance you with plenty of facets of your compact organization accounting. Here’s how I’ve made use of Excel as an accountant.


Although a lot of accounting program platforms have budgeting capabilities, I’d select Excel each time.

Start out your business spending plan by exporting an money statement from your accounting software to Excel. Very clear out the quantities and incorporate columns for just about every month of the period of time for which you’re budgeting. Then begin moving into your budgeted quantities and voila! You have a budget.

As your business’s finances get additional involved, you can build granular sub-budgets that roll up into your business’s grasp spending budget. Excel can cope with it.


If your accounting software package does not have an invoicing function, you can conveniently make professional expenditures by means of Excel.

Microsoft Phrase also has bill templates, but I would not propose making use of them since Word templates just cannot do any arithmetic for you. Excel bill templates instantly sum invoice line items, lessening the possibility of human mistake. You wouldn’t want to be liable for underbilling a consumer for the reason that of an arithmetic miscalculation.

Price reports

Each time you reimburse an employee for mileage or a different company expenditure, you should check with for receipts and an price report that tallies all expenses in one particular document. There are hundreds of price report templates to select from online.

Accounts receivable and accounts payable

A lot of modest firms track who owes them and whom they owe with an Excel spreadsheet.

Companies on the income accounting process only document income and costs when income arrives in or out. Excel is the excellent repository for a checklist of superb consumer invoices and your business’s unpaid expenses since they have no place in your accounting data right until funds adjustments fingers.

Accrual-basis firms record receivables and payables in their accounting software program when they make earnings and incur expenses, respectively. They should not need to have to keep track of their receivables and payables any where else.

Financial institution reconciliations

Lender reconciliations, a person of the bookkeeping basic principles, look at your company’s accounting records with its lender statements. You can place each facet by side in Excel and immediately scrutinize any discrepancies.

Numerous accounting program solutions have financial institution reconciliation resources, but I favor to use Excel to perform month to month reconciliations for the reason that of its no-frills interface. Start out the financial institution reconciliation by downloading Excel versions of your company’s general ledger and bank statement. Then, duplicate and paste the info into just one spreadsheet and get started reconciling.

3 most effective tactics when applying Excel accounting software

Stay clear of generating extra function for on your own by adhering to these most effective techniques.

1. Permit formulation do the function

The best suggestions I’ve received about utilizing Excel for accounting is to stay away from “hardcoding” numbers every time possible.

Imagine you’re constructing your company’s price range for future yr. You program to pay all personnel the point out least wage of $12 for every hour. You later on study that the minimal wage is heading up to $15 subsequent calendar year. If you produced the funds utilizing formulas the to start with time, this correct ought to just take no extra than 30 seconds.

Hardcoding a range indicates coming into an real number, not a formulation, into a mobile. The only quantities I hardcoded below are the $12 least wage and the month-to-month hrs for just about every employee. Formulation compute the employees’ monthly gross pay back.

graphical user interface, application, table, Excel: A payroll budget shows an employee’s monthly hours multiplied by the minimum wage.

© Provided by The Blueprint
A payroll spending budget reveals an employee’s month to month several hours multiplied by the bare minimum wage.

Bridget’s monthly pay is calculated by multiplying her monthly hours by the condition minimal wage. Experienced I independently multiplied 12 and 30 in every single cell, I would have to redo the total payroll funds. Many thanks to the formula, I can simply modify the 12 to a 15, and all the monthly payroll amounts will immediately update.

A bonus tip: The dollar symptoms in the components “lock” the row and column you are referencing, so if you copy and paste the method from one mobile to one more, it will go on to reference the similar spot instead of adjusting for the new cell’s spot.

2. Save Excel accounting documents securely

Anyplace you retail outlet economic information and facts ought to be secured by a password that only a privileged couple know. Password-guard Excel files with delicate information and facts, these as employee Social Stability quantities.

Never forget about to again up information that are not stored in a cloud storage technique.

3. Make template documents

Save your bill and cost report templates as template data files. When you open up a template file, it makes a new document that will not overwrite the initial template.

After you obtain and tweak your cost report template, click on “Save As Template” and help you save it to an obtainable position for you and your employees. Any time somebody clicks on the template file, you can be absolutely sure they won’t inadvertently spoil all your difficult get the job done in making it.

Excel with Excel

Excel can be intimidating at 1st, but the experience fades speedily. It is like conference your in-regulations for the initially time (hopefully).

Start out exploring Excel’s capabilities by downloading an price report or invoice template and tweaking it to match your enterprise. Soon more than enough, you’ll gain the know-how to employ it much more broadly in your firm.

Continue on Reading