How to Make Personal Expense Sheet in Excel (with Easy Steps)

If you are looking for some easy and effective tricks to make a personal expense sheet in Excel then you are in the right place. Every person must trackΒ expenses so he/she can identify what to do to keep the expenses under control. In this article, I will show you every step with clear illustrations to make a personal Expense sheet. You can download the free monthly expense template and modify it for your personal use.


Download Sample Workbook

You can download the following free template from here:


Steps to Make Personal Expense Sheet in Excel

Making a personal expense sheet is necessary for everyone. But it may be felt clumsy or daunting work to create an expense tracker in Excel. So, in this article, I will show you the necessary steps to create a personal expense sheet.

There are some important things you should include in the expense sheet. These are:

  • List of expenses with forecast and actual values.
  • You can categorize the expenses so you will get a summary of data that will show where you have spent and how much amount.
  • You should include the types of transaction medium that you use. So you can see how much amount you have in each medium.

How to Make Personal Expense Sheet in Excel


πŸ“Œ Step 1: Create Expense Sheet Layout

At first, create a blank template in a new worksheet. There you will create 4 tables.

  • Expense Table: In this table, you will add columns for date, description of the expense, category of the expense, medium of transactions and forecast amount, and the actual amount. You can add a column to add notes for expenses.
  • Net Balance: Here, you will insert the list of transaction mediums, starting balance of each medium, and the present balance.
  • Transfer Amount: Here, you will add the transferred amount from one medium to another.
  • Expense Category: Here, you will list the expense category items. It will help you to make a summary of the month’s expenses.

Blank Lay out template to Make Personal Expense Sheet in Excel


πŸ“Œ Step 2: Insert Transaction Medium and Expense Category Lists

  • Now, add the list of transaction mediums in the Medium Column that you use. I have inserted some items here for example. You will add your bank account names, credit cards, or mobile financial services here.
  • Then, add the list of probable expense categories in the Expense Category column. Also, I have added a list of categories for example. You can edit these to add or remove categories as per your use.

Insert Transaction Medium and Expense Category Lists


πŸ“Œ Step 3: Create Drop-Down List in Medium and Category Columns

Now, you will create drop-down list options in the medium and category column in the Expense table. So, while entering the expense you can easily select options from the drop-down list. And you can make a summary of the expense per category and mediums.

  • For this, to implement a drop-down list in the Medium column cells, select all the cells of the medium column.
  • Then, go to the Data tab and select the Data Validation tool.

Create Drop-Down List in Medium and Category Columns

  • Then, in the Data Validation window, select List in the Allow option.
  • Then, select the Medium column cells in the source option.

Create Data Validation

  • Similarly, apply the same steps to apply drop-down options in the Category cells.

Create Data Validation for Expense Category

  • Before entering data, you should convert the cells of the Date columns into the Short Date format.

Changing the Date cells format

Read More: How to Track Income and Expenses in Excel (4 Suitable Examples)


πŸ“Œ Step 4: Insert Expense Data

Now, you are ready to take entry of the expenses. Here I am showing an example of how you can easily enter a date in the expense table.

Insert Expense Data

  • Then enter the starting balance of each transaction medium that you use. You have to edit this data as per your income and the remaining balance from the previous month.

Net Balance of Personal Expense Sheet


πŸ“Œ Step 5: Calculate Net Balance of Transaction Mediums

  • Now, you can calculate the present balance using a formula that will automate the Expense Sheet. Insert this formula into cell L7.
=K7-SUMIF($E$8:$E$100,J7,$G$8:$G$98)+SUMIF($K$16:$K$21,J7,$L$16:$L$21) - SUMIF($J$16:$J$21,J7,$L$16:$L$21)

πŸ”Ž Formula Breakdown:

  • SUMIF($E$8:$E$100,J7,$G$8:$G$98) : Here, the SUMIF function will calculate the sum of the cells in the actual column if the medium cell contains the value of J7 which is β€œX Bank”. So, it will give the total expense done from the X bank medium. So. this amount will be subtracted from the start balance.
  • SUMIF($K$16:$K$21,J7,$L$16:$L$21) : It will give the sum of the transferred amount in this medium. So, it will be added with the start balance.
  • SUMIF($J$16:$J$21,J7,$L$16:$L$21) : It will give the sum of the transferred amount from this medium. So, it will be subtracted from the start balance.

Inserting formula to Calculate Net Balance of Transaction Mediums

Drag Fill handle icon


πŸ“Œ Step 6: Make a Dashboard of Personal Expenses

Now, make a dashboard that will show the present balance and total expense for this month.

  • Calculate the sum of values in the Actual column in the Expense table using the SUM function. For this, paste this formula into the cell G3:
=SUM(G7:G20)

Using SUM function to calculate the total

  • Then calculate the present balance from the Net Balance Table. Paste this formula into the cell mentioning the Present Balance:
=SUM(L7:L12)

Calculating present balance to Make Personal Expense Sheet in Excel

  • Β Now, your monthly expenses sheet is ready to use. Start entering data of daily expenses and you will see the total expense and the present balance is showing results automatically.

How to Make Personal Expense Sheet in Excel

Read More: Daily Income and Expense Sheet in Excel (Create with Detailed Steps)


Things to Remember

  • Edit the transaction Medium as of your usage. And enter the income and remaining balance from the previous month in the Start column of Net Balance.
  • Edit expense category list as of your daily living expenses.

Conclusion

In this article, I have shown you how to make a personal expense sheet in Excel. Download the free template and modify it for your use. I hope you found this article helpful. Besides, you can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Osman Goni Ridwan

Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo