Maintain Accounts in Excel – A Quick Guide

Maintain Accounts in Excel

Want to maintain accounts in Excel? Running a business involves a lot of work, and one of the most important points in a business is the maintenance of accounts. Making calculations and maintaining them manually takes a lot of time, patience and there are chances of errors while maintaining them manually. In what comes as an easier way, Microsoft Excel features a simple way of maintaining accounts.

An Excel spreadsheet can easily help record and analyze data and make quick calculations. An Excel spreadsheet can perform any sort of calculation easily, for the various features and functions provided with Excel. Once you have created an Accounts spreadsheet, it’ll automatically update all the calculations and data records to make predictions, and help visualize data. Even though they can vary sometimes, most spreadsheets contain the same general elements.

Steps to maintain accounts in Excel

Let’s quickly take a look at how you can create and maintain accounts in Excel.

Step 1: Create The Spreasheet

Open the Excel app, and create a new workbook by choosing File and then New. In the templates section, open the blank workbook option to open a worksheet to maintain accounts in it.

Step 2: Choose the number of worksheets

  • After the creation of the file, choose the number of worksheets you want for maintaining accounts.
  • A default workbook has three worksheets, with each one being accessible from tabs at the bottom of the window.
  • Depending on how complex the accounts are, one file for Expenses, one for Income and the third for Tax should be sufficient for maintaining accounts, also you can always add more sheets as per need.
New workbook to maintain accounts in excel
New workbook in Excel

Step 3: Create The Transaction Labels

  • Create separate headings for each data type you want to record in Excel.
  • In the first row of each column, add a heading under which all data types are listed. The headings used should be relevant and self-explanatory. Example: for expenses there will be headings as telephone expense, food expenses, electricity, etcetera.
Column or Table Headings in Excel
Column or Table Headings in Excel

Step 4: Entering Data

  • The next step in maintaining accounts is to simply enter the required data under the respective headings or columns.
  • The best way to make sure that you have covered all column headings is by entering a piece of data under it.
  • You can apply formatting, and the data in the column will be formatted accordingly, and so will the data that is entered later.

Step 5: Create a calculation system

  • Within the spreadsheet itself, you can now make calculations as well and find out totals of your expenses and incomes without much of an effort.
  • We can setup calculation systems to calculate the sum of two values in all cells above it by clicking and dragging all the cells containing the values and click the auto sum button in the editing group under home tab. Enter the sum formula and a group of cells will be added to the parenthesis.

Recommended Read for Maintaining Accounts: Guide to the Sum function in Excel

Step 6: Make calculations

  • The final step involves calculating the accounting balances.
  • In the balance sheet, we can calculate the total value and balance of the business, which is based on the data under income and expenses table.
  • For example, if the cell under balance sheet is selected, and we have entered the equal to sign (=), and then select income sheet and put ‘-‘ and then eventually click enter under the expenses sheet. There will be a new spreadsheet created with the total balance.
Making calculations in Accounts in Excel
Making calculations in Accounts

The following method allows you to maintain accounts in a much easier way without any manual calculations. When life gives you accounts, use spreadsheets to maintain them.


That’s it for the article. There is much more to Maintaining Accounts in Excel. In this tutorial, we have covered the basic and introductory lessons to maintain accounts. Stay tuned to QuickExcel for more tutorials on Excel and maintaining accounts.