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

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.

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 Practice Workbook

Download this practice workbook.

Income and Expense Report.xlsx

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.

<< Go Back to Report in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

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

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

1. Daily Income and Expense Report in Excel

2. Weekly Income and Expense Report in Excel

3. Monthly Income and Expense Report in Excel

Conclusion

Related Articles

Leave a reply Cancel reply