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 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.
- Write down all the month’s names in the range of cells B12:B23.
- 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.
- 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.
- 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.
- 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.
- 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.
- Finally, in cell E7, write down the following formula to get the manager’s name.
=": " & IF(Summary!C9<>0,Summary!C9," ")
- Then, press Enter.
- 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.
- 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.
- 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.
- 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.
- Our monthly expense data table for January is ready to use.
- Follow the procedure to create the monthly expense sheet for the rest of the month.
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
- How to Generate Reports Using Macros in Excel (with Easy Steps)
- Make Daily Sales Report in Excel (with Quick Steps)
- How to Make Monthly Report in Excel (with Quick Steps)
- Create a Report That Displays Quarterly Sales in Excel (with Easy Steps)
- How to Make MIS Report in Excel for Sales (with Easy Steps)
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.
- Now, in the Summary sheet, select cell C12 and write down the following formula into the cell.
=January[[#Totals],[Total]]
- Press Enter.
- Similarly, write down a similar type of formula to extract the total expense of each month in the range of cells C13:C23.
- 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.
- 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.
- 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.
- 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.
- 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.
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
- How to Make Monthly Sales Report in Excel (with Simple Steps)
- Generate Report in PDF Format Using Excel VBA (3 Quick Tricks)
- How to Make Production Report in Excel (2 Common Variants)
- Make Daily Activity Report in Excel (5 Easy Examples)
- How to Make Daily Production Report in Excel (Download Free Template)
- Make Report Card in Excel (Download Free Template)
- How to Automate Excel Reports Using Macros (3 Easy Ways)