How to Maintain Accounts in an Excel Sheet – 4 Templates

The sample dataset showcases the transaction history of a shop.

how to maintain accounts in excel sheet format


Template 1- Maintaining Expense Accounts for a Shop in Excel Sheet

Steps:

  • Create a chart as shown below.

Maintaining Accounts for a Shop Expense in Excel Sheet

  • Enter account data.

  • Use the following formula to calculate the total debit amount.

=SUM(D7:D13)

 

  • Calculate the credit amount as shown below.

Maintaining Accounts for a Shop Expense in Excel Sheet

  • Enter the formula to determine the remaining balance.

=G14-D14

 


Template 2 – Maintaining Daily Life Accounts in Excel

Steps:

  • Create a chart as shown below.

Maintaining Simple Daily Life Accounts

  • Enter the transaction data and store the value of E6 in F6 by using the following formula.

=E6

  • Press ENTER.
  • Use this formula in F7.

=F6+E7-D7

The formula will show you the remaining balance.

  • Drag down the Fill Handle to see the result in the rest of the cells.

Maintaining Simple Daily Life Accounts

 


Template 3 – Managing Multiple Accounts in an Excel Sheet

Steps:

  • Two Excel sheets will be used. One for the selection of a shop (here, Home) and the other to store the account history .
  • Insert a new sheet and go to Insert >> Text >> WordArt.
  • Use Buttons or Shapes.

Managing Multiple Accounts in Excel Sheet

  • Create WordArts. Here, two.

  • Create your Excel chart for account maintenance in a new sheet (here, Shop A).
  • Use the SUM function as described in the previous method to calculate the total amounts.
  • Create another WordArt (HOME) which will be linked to the Home sheet.

  • Go back to the Home sheet and right-click Shop A WordArt.
  • Select Link.

  • In Insert Hyperlink, select Place in This Document >> Cell Reference >> Shop A.
  • Click OK.

Managing Multiple Accounts in Excel Sheet

  • Click the Shop A WordArt to go to Shop A.

  • Link the HOME WordArt to Home.

 


Template 4 – Maintaining Accounts by Keeping an Invoice History in Excel

 

Maintaining Accounts by Keeping Invoice History

Download the template and use it.

The Due Period refers to the time 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, the Due Status will be Recent. Otherwise, it will be Past Due. The blue area shows a summary of the Invoice History.


Download Practice Workbook


 

How to Maintain Accounts in Excel: Knowledge Hub


<< Go Back to Excel for Accounting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo