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.
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.
1. Maintaining Expense Accounts for a Shop 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.
- 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.
- 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. Format for Maintaining a 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.
- 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.
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.
- 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.
- 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 in Excel
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.
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
Download Practice Workbook
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.
