How to Make a Monthly Expense Report in Excel (with Quick Steps)

A monthly expense report is a common staff in our professional life. Almost every organization has to deal with this report to pay back their employee’s self-funded bills. In this article, we will demonstrate to you how to make a monthly expense report in Excel. If you are also interested to create your monthly expense report, download our free template and follow us.


Download Practice Workbook

Download this free workbook for practice while you are reading this article.


Step-by-Step Procedure to Make a Monthly Expense Report in Excel

We consider an employee David who works for our company in the sales department to demonstrate this procedure. His employee ID is 2022007. The name of his department manager is Luca.


Step 1: Design Preliminary Summary Layout

In this step, we will make a primary summary layout for showing our expenses month-wise.

  • First of all, in the range of cells B6:B9, write down the following entities as shown in the image.
  • Then, format the range of cells C6:C9 according to your desire to input the employee’s data.

  • After that, entitle cells B11 and C11 as Month and Expense.

Design Preliminary Summary Layout to Make a Monthly Expense Report

  • Write down all the month’s names in the range of cells B12:B23.

Design Preliminary Summary Layout to Make a Monthly Expense Report

  • Our preliminary summary layout is ready.

Thus, we can say we completed the first step to make a monthly expense report in Excel.

Read More: How to Create an Income and Expense Report in Excel (3 Examples)


Step 2: Create Monthly Expense Report for All Months

Now, we are going to generate the monthly expense report. We will demonstrate the process for January. The procedure for the rest of the month will be similar.

  • At first, select cell D1 to insert your company logo.
  • In the Insert tab, click the drop-down arrow of Illustrations > Pictures.
  • After that, select the This device option.

Generate Monthly Expense Report for All Months in Excel

  • As a result, the Insert Picture dialog box will appear. Choose your organization’s logo. We are inserting our webpage logo for your convenience.
  • At last, click the Insert button.

  • The logo will be inserted and placed the logo at your desired location.

  • Now, select the range of cells B4:F4.
  • In the Home tab, select the Merge & Center option from the Alignment group.

Generate Monthly Expense Report for All Months in Excel

  • Then, in the merged cell set a suitable title for the sheet. We denote the sheet as Expense Report of January.

  • After that, in the range of cells B6:B7 and D6:D7, write down the following entities.

  • Then, select cell C6 and write down the following formula to extract the employee’s ID from the Summary

=": " & IF(Summary!C6<>0,Summary!C6," ")

  • Press Enter.

Generate Monthly Expense Report for All Months in Excel

  • Here, we have used the IF function to get a good cell formatting.
  • Next, in cell C7, write down the following formula to get the name of that employee.

=": " & IF(Summary!C7<>0,Summary!C7," ")

  • Press Enter.

Generate Monthly Expense Report for All Months in Excel

  • Similarly, in cell E6, write down the formula to get the value for the department name.

=": " & IF(Summary!C8<>0,Summary!C8," ")

  • Press the Enter key.

Generate Monthly Expense Report for All Months in Excel

  • Finally, in cell E7, write down the following formula to get the manager’s name.

=": " &  IF(Summary!C9<>0,Summary!C9," ")

  • Then, press Enter.

Generate Monthly Expense Report for All Months in Excel

  • Now, write down the following titles as shown in the image, in the range of cells B9:F9 for the monthly expense table.

  • To ensure that the proper data will be input in columns Payment Type and Category, we have to use the data validation feature.
  • For that, create a separate sheet and rename it as Others.
  • Now, in the range of cells B3:B6, write down four types of payment systems.

Generate Monthly Expense Report for All Months in Excel

  • After that, select cell D10 and in the Data tab, select the drop-down arrow of Data Validation > Data validation from the Data Tools group.

Generate Monthly Expense Report for All Months in Excel

  • A small dialog box, called Data Validation will appear.
  • Then, set the Allow option as List, and in the Source option, select the range of cells Others!$B$3:$B$6.
  • Click OK.

  • Similarly, in cell E10, create the same data validation drop-down for the categories shown in the Others sheet in the range of B9:B14.

Generate Monthly Expense Report for All Months in Excel

  • Now, we will convert the dataset into a table, because it will help you to copy the data validation drop-down arrow in every new row of this table.
  • For that, select the range of cells B9:F10 and press ‘Ctrl+T’ to convert the dataset into a table.

  • As a result, the Create Table dialog box will appear.
  • Check My table has headers option and click OK.

  • Then, in the Table Design tab, set the table name as January from the Properties group.
  • Besides it, modify the Table Style Options according to your desire. We checked the following items for our table.

Generate Monthly Expense Report for All Months in Excel

  • Our monthly expense data table for January is ready to use.

Generate Monthly Expense Report for All Months in Excel

  • Follow the procedure to create the monthly expense sheet for the rest of the month.

Generate Monthly Expense Report for All Months in Excel

At last, we can say that we completed the second step to make a monthly expense report in Excel.

Read More: How to Create an Expense Report in Excel (With Easy Steps)


Similar Readings


Step 3: Verify Summary Report with Data

In this step, we will complete our summary report with some sample data and check the report’s workability.

  • First, input the following data according to your institution profile in the range of cells C6:C9.

  • You will see the IF function will show these data in our monthly expense sheet.
  • Input some sample data into the table called January as shown in the image below.

Verify Summary Report with Sample Data of Monthly Expense Report

  • Now, in the Summary sheet, select cell C12 and write down the following formula into the cell.

=January[[#Totals],[Total]]

  • Press Enter.

Verify Summary Report with Sample Data of Monthly Expense Report

  • Similarly, write down a similar type of formula to extract the total expense of each month in the range of cells C13:C23.

Verify Summary Report with Sample Data of Monthly Expense Report

  • Our summary report is completed.

Finally, we can say that we completed the final step to make a monthly expense report in Excel.

Read More: Create a Report in Excel as a Table (With Easy Steps)


Step 4: Generate Dynamic Monthly Expense Report

We are going to add two charts to our summary report to get a better visualization of our monthly expenses. It is not mandatory, but it will provide your dataset with a better visualization. We will add a pie chart and a bar chart.

  • In the Summary sheet, select the range of cells B12:C23.
  • Then, in the Insert tab, select the drop-down arrow of the Insert Pie or Doughnut Chart option and select the 3-D Pie option.

Insert Charts in Summary of Monthly Expense Report

  • The chart will appear in front.
  • You can modify the chart style from the Chart Design and Format tabs, For our pie chart, we choose Style 9 from the Chart Styles group.
  • Besides it, click on the Chart Elements icon and checked the Data Labels, and placed the Legends on the right side.

Insert Charts in Summary of Monthly Expense Report

  • Again, select the range of cells B12:C23.
  • Now, to insert the Bar chart, follow the same process, and in the drop-down arrow of Columns or Bar Chart, select the Clustered Bar option from the 2-D Bar section.

Insert Charts in Summary of Monthly Expense Report

  • Then, modify the number of elements and the chart style according to your desire. We choose Style 7 and the Axes and Data Labels elements in this chart.
  • To show the Data Labels, choose the Outside End option.

Insert Charts in Summary of Monthly Expense Report

  • After that, placed both charts in a suitable position in the Summary sheet.
  • Finally, select the range of cells B4:J4 and in the Home tab, click on the Merge & Center option from the Alignment group.

  • Write down a suitable title into the merged cell according to your desire. For our sheet, we wrote down the Summary of Monthly Expense Report as the title.

  • At last, follow the same process as step 2 to insert your organization’s logo in cell G1.
  • Our monthly expense report is completed and ready to use.

Insert Charts in Summary of Monthly Expense Report

In the end, we can say that we completed all the steps to make a monthly expense report in Excel. Besides it, you can see, that our datasheet gets a better outlook from the end of the last step.

Read More: How to Generate Reports from Excel Data (2 Easy Methods)


Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to make a monthly expense report in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.

Don’t forget to check our website ExcelDemy for several Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Soumik Dutta
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo