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

### Step 1: Design a Preliminary Summary Layout

• In the range of cells B6:B9, enter the following entities, as shown in the image.
• Format the range of cells C6:C9 according to your desire to input the employeeâ€™s data.

• Name cells B11 and C11 as Month and Expense.

• Enter all the months in the range of cells B12:B23.

• Our preliminary summary layout is ready.

### Step 2: Create a Monthly Expense Report for All Months

• Select cell D1 to insert your company logo.
• In the Insert tab, click the drop-down arrow of Illustrations > Pictures.
• Select the This Device option.

• The Insert Picture dialog box will appear. Choose your organizationâ€™s logo. We are inserting our webpage logo for your convenience.
• Click the Insert button.

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

• Select the range of cells B4:F4.
• Select the Merge & Center option from the Alignment group in the Home tab.

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

• In the range of cells B6:B7 and D6:D7, enter the following entities.

• Select cell C6 and enter 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.
• In cell C7, enter the following formula to get the name of that employee:

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

• Press Enter.

• In cell E6, enter the formula to get the value for the department name:

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

• Press Enter.

• In cell E7, enter the following formula to get the managerâ€™s name:

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

• Then, press Enter.

• For the monthly expense table, enter the following titles in the range of cells B9:F9, as shown in the image.

• Use the data validation feature to ensure the proper data is input in columns Payment Type and Category.
• Create a separate sheet and rename it as Others.
• In the range of cells B3:B6, enter the four types of payment systems.

• 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.
• Set the Allow option as List, and in the Source option, select the range of cells Others!\$B\$3:\$B\$6.
• Click OK.

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

• Convert the dataset into a table. It will help you copy the data validation drop-down arrow in every new row of this table.
• Select the range of cells B9:F10 and press â€˜Ctrl+Tâ€™ to convert the dataset into a table.

• The Create Table dialog box will appear.
• Check the My table has headers option and click OK.

• In the Table Design tab, set the table name as January fromÂ the Properties group.
• 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.

### Step 3: Verify the Summary Report with Data

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

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

• In the Summary sheet, select cell C12 and enter the following formula into the cell:

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

• Press Enter.

• Enter is a similar formula to extract each month’s total expense in the range of cells C13:C23.

• Our summary report is completed.

### Step 4: Generate a Dynamic Monthly Expense Report

• In the Summary sheet, select the range of cells B12:C23.
• 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.
• You can modify the chart style from the Chart Design and Format tabs. We chose Style 9 from the Chart Styles group for our pie chart.
• Click on the Chart Elements icon and check the Data Labels.
• Place the Legends on the right side.

• Select the range of cells B12:C23.
• 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.

• You can modify the number of elements and the chart style according to your preference. We chose Style 7 and this chart’s Axes and Data Labels elements.
• To show the Data Labels, choose the Outside End option.

• Place both charts in a suitable position on the summaryÂ sheet.
• Select the range of cells B4:J4, and in the Home tab, click on the Merge & Center option from the AlignmentÂ group.

• Enter a suitable title into the merged cell according to your desire. We wrote down the Summary of Monthly Expense Report as the title for our sheet.

• 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.

## Related Articles

<< Go Back to Report in Excel |Â Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Soumik Dutta

Soumik Dutta, having earned a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a key role as an Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Soumik not only skillfully addresses complex challenges but also demonstrates enthusiasm and expertise in gracefully navigating tough situations, underscoring his unwavering commitment to consistently deliver exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF