Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Maintain Accounts in Excel Sheet Format (4 Templates)

The article will show you how to maintain accounts in Excel sheet format. It’s important to know how your expenses are going in a month so that you can mitigate your expenses when it’s needed. There are many sectors where we require this application. Here I’m going to show you four templates for maintaining account statements in Excel sheet format. Even though these templates do not match your criteria, you can make a new one on your own just by following the procedure and changing the transaction description. Because the formula we will be using here won’t change a bit for other templates.


Download Practice Workbook


4 Free Templates to Maintain Accounts in Excel Sheet Format

Here, I’m going to show you a sample of what an account maintaining template will look like. You see the transaction history of a shop in the following screenshot.

how to maintain accounts in excel sheet format


1. Maintaining Accounts for a Shop Expense in Excel Sheet

Suppose, you work in a shop and you have a duty to monitor the expenses and credits of that shop. Let’s help you on this matter and see the process below.

Steps:

  • First, simply just make a chart like the following picture. If you need, add some more criteria into it.

Maintaining Accounts for a Shop Expense in Excel Sheet

  • After that, enter the necessary account data in the chart.

  • Thereafter, use the following formula to calculate the total debit

=SUM(D7:D13)

The formula uses the SUM function to calculate the total debit amount of the shop.

  • Similarly, you can calculate the credit amount using the exact function.

Maintaining Accounts for a Shop Expense in Excel Sheet

  • After that, write down the formula below to determine the remaining balance.

=G14-D14

Thus you can easily maintain accounts in Excel sheet format.

Read More: How to Create Account Statement in Excel (with Easy Steps)


2. Maintaining Simple Daily Life Accounts

You can also use Excel sheets to maintain your daily life accounts. Let’s go through the procedure below for a better understanding.

Steps:

  • First, create a chart similar to the following image.

Maintaining Simple Daily Life Accounts

  • Next, insert the transaction data and store the value of E6 in F6 by writing the following formula.

=E6

  • Next, press the ENTER button and type another formula in F7.

=F6+E7-D7

The formula will show you the remaining balance.

Maintaining Simple Daily Life Accounts

Thus you can also maintain your daily life accounts in Excel sheet format.

Read More: How to Edit Bank Statement in Excel (with Easy Steps)


3. Managing Multiple Accounts in Excel Sheet

The above templates are for single individual account maintenance. But if you want to maintain multiple accounts history, you should follow this method carefully.

Steps:

  • Here, we will need at least two Excel sheets. One is for the selection of a shop and the others are for storing accounts history.
  • So first, insert a new sheet and then go to Insert >> Text >> WordArt. Here the sheet name is Home and we will maintain the account data of Shop A.
  • You may use Button or Shapes in this case.

Managing Multiple Accounts in Excel Sheet

  • Create as many WordArts as you need. Here I made two and I’ll link one of them to show how to use WordArt in this case.

  • Next, create your Excel chart for accounts maintenance in a new sheet. In my case, the sheet name is Shop A. Use the SUM function as we did in the previous method to calculate the total amounts.
  • Also, create another WordArt named HOME which we will link with the Home sheet.

  • Thereafter, go back to the Home sheet and right-click on the Shop A WordArt.
  • After that, select Link.

  • Next, in the Insert Hyperlink window, select Place in This Document >> Cell Reference >> Shop A.
  • Thereafter, click OK.

Managing Multiple Accounts in Excel Sheet

  • After that, click on the Shop A WordArt and this operation will take you to the Shop A

  • Similarly, link the HOME WordArt to the Home

Thus you can maintain multiple accounts in Excel sheet format.

Read More: Bank Reconciliation Statement in Excel Format


4. Maintaining Accounts by Keeping Invoice History

The following template could be a fine tool for you to maintain the detailed accounts history of your shop if the shop offers due payment for the customers. Just look at the template below.

Maintaining Accounts by Keeping Invoice History

To know the details about this template, please follow the link. I’ll suggest you just download the template file of this article and start using it. However, I’ll give you a brief description of this template below.

The Due Period refers to the time duration between the Due Date and the Present Date. For example, Jim’s Due Date was 15th June, which is more than 90 days from the present date. So the Due Period will be +90 Days. If the Due Date is posted to the present date then the Due Status will be Recent, otherwise, it will be Past Due. The blue area shows a summary of the Invoice History.

Read More: Monthly Bank Reconciliation Statement Format in Excel


Conclusion

In the end, we can conclude that you will get some valuable template to maintain accounts in Excel sheet format from this article. If you have any better suggestions or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy.


Related Articles

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo