In order to keep a record of our Expenses we often feel the necessity of having an Expense Spreadsheet. We can easily make an Expense Spreadsheet in Excel to keep track of our expenses. In this way, we can have a precise idea about our spending. In this article, we will see how to make an Expense Spreadsheet in Excel.
Download Practice Workbook
You can download the Excel workbook from here.
2 Suitable Ways to Make an Expense Spreadsheet in Excel
We can make an Expense Spreadsheet in Excel in two suitable ways. One is without Categorizing Expenses and the another is by categorizing Expenses. We will see both ways to make an Expense Spreadsheet in Excel in this article.
1. Make an Expense Spreadsheet in Excel without Categorizing Expenses
This is the easiest way to make an Expense Spreadsheet in Excel. For this method, we don’t need to categorize our expenses. All we need to do is to make a single spreadsheet in Excel and manually input every expense in the spreadsheet.
Steps:
- At first, open a blank worksheet.
- Then, Create a Table and type the table headings in row 4 like below.
- After that, type all the sources of Expenses in column B, and to sum the expenses type Total at the end of column B like the picture below.
- Next, input all your expenses in the table for different months for all the sources of your expenses like below.
- Now to calculate the total expenses of month January, click the C15 cell and type the following formula:
=SUM(C5:C14)
- After that, press Enter and it will calculate your total expense for the month of January.
- Next, drag the Fill Handle from cell C15 to N15 and it will calculate your total expenses for the rest of the months like the picture below.
- Subsequently, to calculate the total expense for a particular source of expense in a year, click the O5 cell and type the following formula:
=SUM(C5:C14)
- Then press Enter and it will calculate your total expense for that particular source in that year.
- Next, drag the Fill Handle from cell O5 to O14 and it will calculate your total expenses for the rest of the sources of expense like the picture below.
- Finally, to sum your total expense in that particular year, click the O15 cell and type the following formula:
=IF(SUM(C15:N15)=SUM(O5:O14),SUM(O5:O14))
- Then press Enter and it will calculate your total expense for that particular year like the picture below.
🔎 How Does the Formula Work?
- Here, if SUM(C15:N15) is equal to SUM(O5:O14), it’ll give SUM(O5:O14) output.
Read More: How to Track Income and Expenses in Excel (4 Suitable Examples)
2. Categorize Expenses to Make an Expense Spreadsheet in Excel
If we want to keep track of our expenses in an organized way, we can make an Expense Spreadsheet by categorizing our expenses. In this way, we can easily keep an eye on our expenses in an organized manner.
Steps:
- At first, open a blank worksheet.
- Secondly, create a table mentioning the categories and subcategories of your expenses like the picture below.
- After that, open a new worksheet and create a table like the picture below to add your categories and subcategories of expenses.
- Now, select the cells (C6:C12). Then, go to the Data tab >> Data Validation tool >> Data Validation option.
- As a result, the Data Validation window will appear.
- Now, in the Settings tab, choose the List option from the Allow dropdown list.
- Subsequently, at the Source: text box, refer to the (B5:B15) cells from the ‘Categories and Sub Categories’ worksheet.
- Following, click on the OK.
- Now, after clicking on the cells of the category column, you will be able to choose your categories of expenses like below.
- Furthermore, select the (D6:D12) cell. Then, go to the Data tab >> Data Validation tool >> Data Validation option.
- As a result, the Data Validation window will appear.
- Now, at the Settings tab, choose the List option from the Allow dropdown list.
- Subsequently, at the Source: text box, refer to the (C5:C15) cells from the Categories and Sub Categories worksheet.
- Following, click on the OK.
- Now, after clicking on the cells of the Sub Category column, you will be able to choose your Sub categories of expenses like below.
- After that, you can input all your Expense data according to Categories and Sub Categories for a particular month like below.
- Finally, to calculate the total expense of a particular month click on the cell E13 and type the following formula:
=SUM(E6:E12)
- Then press Enter and it will calculate your total Expense for that particular month.
Read More: Daily Income and Expense Sheet in Excel (Create with Detailed Steps)
Things to Remember
- If you want to make only one spreadsheet and keep track of your expenses without categorizing them, then the first method will be the best option for you.
- If you want to keep track of your expenses in an organized way by categorizing and sub-categorizing them, then the second method will be the best option for you. In this way, you can also make your Daily Expense Spreadsheet.
Conclusion
Hence, follow the above-described methods. Thus, you can easily learn how to make an Expense Spreadsheet in Excel. Hope this will be helpful. Follow the ExcelDemy website for more articles like this. Don’t forget to drop your comments, suggestions, or queries in the comment section below.
Related Articles
- How to Calculate Business Income and Expense in Excel Worksheet
- How to Make Monthly Expenses Sheet in Excel (With Easy Steps)
- How to Keep Track of Small Business Expenses in Excel (2 Easy Ways)
- How to Make Office Expense Sheet in Excel (with Easy Steps)
- How to Make Personal Expense Sheet in Excel (with Easy Steps)