When you want to know your company’s monthly, daily or weekly overview, it is a sound practice to have an income and expense report. It gives you a proper overview of how to adjust your business for the future. At the same time, you can create your personal income and expense report which will give you a benefit in the future. Excel gives you a platform where you can easily create an income and expense report. This article will show you the step-by-step procedure on how to create an income and expense report in Excel.
Watch Video – Create an Income and Expense Report in Excel
How to Create an Income and Expense Report in Excel (3 Examples)
To create a proper income and expense report in Excel, you need to do some basic procedures and also utilize Excel commands properly. In this article, we show some important procedures to create an income and expense report in Excel. Those procedures are fairly easy to digest and very fruitful for your purpose.
1. Daily Income and Expense Report in Excel
In this example, we need to focus on how to create a daily income and expense report. To do this, you need to follow the steps carefully.
Step 1: Import Dataset
At first, you need to import your dataset if you don’t have any. We can a dataset that includes an income and expense report of a company. The company has some products from which they earn money. They have some expenses also to maintain a big office and other necessary expenses. At last, they got a handsome amount of money as net income.
Step 2: Create Pivot Table
Next, you need to create a pivot table with your dataset, Pivot table helps to analyze your data more accurately and provides a summary of your data table.
- First, select your whole dataset by selecting any cell and then pressing Ctrl+A. It will select the entire dataset.
- Next, go to the Insert tab in the ribbon.
- Then, select PivotTable.
- PivotTable from table or range dialog box will pop up.
- As you select the entire dataset, that’s why the Table/Range option already appears in there.
- Next, choose the New Worksheet to place the PivotTable to a new worksheet.
- Finally, click on OK.
- Then, go to the worksheet where your PivotTable is supposed to appear.
- On the right side of it, PivotTable Fields will appear.
- Select Day, Total Income, and Expense.
- PivotTable will provide the following table with column headers you selected from PivotTable Fields.
Step 3: Insert Daily Income and Expense Report Chart
Now, if you want to insert the daily income and expense report chart, you need to follow these steps carefully. As we know, a chart is a graphical representation of your data. So, to have a better overview of your dataset, you must insert them into a chart.
- First, select any cell in the Pivot table.
- Next, go to the Insert tab in the ribbon.
- From the Chart group, select Recommended Charts.
- Insert Chart dialog box will appear.
- Next, select the Column chart from the All Chart section.
- Now, select any type of column chart. We take the first column chart.
- Finally, click on OK.
- There we have our desired chart.
- You can modify the column chart by using the Brush and Plus sign.
- The Brush sign can change the chart style.
- The Plus sign can change the chart elements.
- Now, if you think, you don’t want the column chart. You want to use a line chart to show your dataset.
- First, select any cell in the pivot table.
- From the Chart group, select Recommended Charts.
- Insert Chart dialog box will appear.
- Next, select the Line chart from the All Chart section.
- Now, select any type of column chart. We take the fourth Line chart.
- Finally, click on OK.
- There we have our desired line chart.
- Now, if you want to create a pie chart with your dataset, you need to select the process the same process.
- Go to the Insert tab in the ribbon.
- From the Charts group, select Recommended Charts.
- Insert Chart dialog box will appear.
- Next, select the Pie chart from the All Charts section.
- Now, select any type of column chart. We take the second Pie chart.
- Finally, click on OK.
- There we have our desired Pie chart.
- Finally, you can create a Combo chart in Excel.
- First, go to the Recommended Charts option like the previous charts.
- Insert Chart dialog box will appear.
- Next, select the Combo chart from the All Charts section.
- Now, select any type of column chart. We take the fourth Combo chart.
- Finally, click on OK.
- It will create a Combo chart like the following chart.
Step 4: Create Final Daily Income and Expense Report
When you finish plotting daily income and expenses in different types, you can copy them and paste them into a new worksheet. Then, present it in the following way.
Read More: How to Make Daily Sales Report in Excel
2. Weekly Income and Expense Report in Excel
Here, we want to create a weekly income and expense report. To create this report, you need to follow the following steps carefully.
Step 1: Import Dataset
Just like the previous example, you need to import your dataset if you don’t have any. We can a dataset that includes an income and expense report of a company.
Step 2: Create Pivot Table
Next, you need to create a pivot table with your dataset, Pivot table helps to analyze your data more accurately and provides a summary of your data table.
- First, select your whole dataset by selecting any cell and then pressing Ctrl+A. It will select the entire dataset.
- Next, go to the Insert tab in the ribbon.
- Then, select PivotTable.
- PivotTable from table or range dialog box will pop up.
- As you select the entire dataset, that’s why the Table/Range option already appears in there.
- Next, choose the New Worksheet to place the PivotTable to a new worksheet.
- Finally, click on OK.
- Then, go to the worksheet where your PivotTable is supposed to appear.
- On the right side of it, PivotTable Fields will appear.
- Select Date, Total Income, and Expense.
- PivotTable will provide the following table with column headers you selected from PivotTable Fields
- Now, right-click on any day cell.
- An options box will pop up.
- From there select Group.
- A Grouping box will appear.
- Select Days from the By
- Set the Number of days as 7.
- Finally, click on OK.
Step 3: Insert Weekly Income and Expense Report Chart
Now, if you want to insert the weekly income and expense report chart, you need to follow these steps carefully.
- First, select any cell in the Pivot table.
- Next, go to the Insert tab in the ribbon.
- From the Chart group, select Recommended Charts.
- Insert Chart dialog box will appear.
- Next, select the Column chart from the All Chart section.
- Now, select any type of column chart. We take the first Column chart.
- Finally, click on OK.
- There we have our desired chart.
- You can modify the column chart by using the Brush and Plus sign.
- The Brush sign can change the chart style.
- The Plus sign can change the chart elements.
- Now, if you think, you don’t want the column chart. You want to use a Line chart to show your dataset.
- First, select any cell in the pivot table.
- From the Charts group, select Recommended Charts.
- Insert Chart dialog box will appear.
- Next, select the Line chart from the All Charts section.
- Now, select any type of column chart. We take the fourth Line chart.
- Finally, click on OK.
- There we have our desired line chart.
- Now, if you want to create a pie chart with your dataset, you need to select the process the same process.
- Go to the Insert tab in the ribbon.
- From the Charts group, select Recommended Charts.
- Insert Chart dialog box will appear.
- Next, select the Pie chart from the All Charts section.
- Now, select any type of column chart. We take the second Pie chart.
- Finally, click on OK.
- There we have our desired Pie chart.
- Finally, you can create a Bar chart in Excel.
- First, go to the Recommended Charts option like the previous charts.
- Insert Chart dialog box will appear.
- Next, select the Bar chart from the All Charts section.
- Now, select any type of column chart. We take the first Bar chart.
- Finally, click on OK.
- It will create a Combo chart like the following chart.
Step 4: Create Final Weekly Income and Expense Report
When you plot weekly income and expenses in different types, you can copy them and paste them into a new worksheet. Then, present it in the following way.
3. Monthly Income and Expense Report in Excel
Lastly, we want to create a monthly income and expense report in a year. This report will help to predict future profit using forecasting. To create a monthly income and expense report, you need to follow the following steps carefully.
 Step 1: Import Dataset
At first, you need to import your dataset if you don’t have any.
Step 2: Create Pivot Table
Next, you need to create a pivot table with your dataset, Pivot table helps to analyze your data more accurately and provides a summary of your data table.
- First, select your whole dataset by selecting any cell and then pressing Ctrl+A. It will select the entire dataset.
- Next, go to the Insert tab in the ribbon.
- Then, select PivotTable.
- PivotTable from table or range dialog box will pop up.
- As you select the entire dataset, that’s why the Table/Range option already appears in there.
- Next, choose the New Worksheet to place the PivotTable to a new worksheet.
- Finally, click on OK.
- Then, go to the worksheet where your PivotTable is supposed to appear.
- On the right side of it, PivotTable Fields will appear.
- Select Month, Total Income, and Expense.
- PivotTable will provide the following table with column headers you selected from PivotTable Fields.
Step 3: Insert Monthly Income and Expense Report Chart
Now, if you want to insert the monthly income and expense report chart, you need to follow these steps carefully. First, select any cell in the Pivot table.
- Next, go to the Insert tab in the ribbon.
- From the Chart group, select Recommended Charts.
- Insert Chart dialog box will appear.
- Next, select the Column chart from the All Charts section.
- Now, select any type of column chart. We take the first column chart.
- Finally, click on OK.
- There we have our desired chart.
- You can modify the column chart by using the Brush and Plus sign.
- The Brush sign can change the chart style
- The Plus sign can change the chart elements.
- Now, if you think, you don’t want the column chart. You want to use a line chart to show your dataset.
- First, select any cell in the pivot table.
- From the Charts group, select Recommended Charts.
- Insert Chart dialog box will appear.
- Next, select the Line chart from the All Charts section.
- Now, select any type of column chart. We take the fourth Line chart.
- Finally, click on OK.
- There we have our desired line chart.
- Now, if you want to create a pie chart with your dataset, you need to select the process the same process.
- Go to the Insert tab in the ribbon.
- From the Chart group, select Recommended Charts.
- Insert Chart dialog box will appear.
- Next, select the Pie chart from the All Chart section.
- Now, select any type of column chart. We take the second Pie chart.
- Finally, click on OK.
- There we have our desired Pie chart.
- Finally, you can create a Bar chart in Excel.
- First, go to the Recommended Charts option like the previous charts.
- Insert Chart dialog box will appear.
- Next, select the Bar chart from the All Charts section.
- Now, select any type of column chart. We take the first Bar chart.
- Finally, click on OK.
- It will create a Bar chart like the following chart.
Step 4: Create Final Daily Income and Expense Report
When you finish plotting daily income and expenses in different types, you can copy them and paste them into a new worksheet. Then, present it in the following way.
Download this practice workbook.
Conclusion
To create an income and expense report in Excel, we have shown three different examples through which you can easily create an income and expense report in Excel. All the examples are really easy to understand. If you have any questions, feel free to ask in the comment box.