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.


Download Practice Workbook

Download this practice workbook.


3 Suitable Examples to Create an Income and Expense Report in Excel

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.

Daily Income and Expense Report in Excel

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.

Daily Income and Expense Report in Excel

  • PivotTable will provide the following table with column headers you selected from PivotTable Fields.

Daily Income and Expense Report in Excel

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.

Daily Income and Expense Report in Excel

  • There we have our desired chart.

Daily Income and Expense Report in Excel

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

Create Daily Income and Expense Report

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

Create Daily Income and Expense Report

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

Create an Income and Expense Report in Excel

Read More: How to Make Daily Sales Report in Excel (with Quick Steps)


Similar Readings


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.

Daily Income and Expense Report in Excel

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.

Create Weekly Income and Expense Report in Excel

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

Create Weekly Income and Expense Report

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.

Create Weekly Income and Expense Report

  • There we have our desired chart.

Create Weekly Income and Expense Report

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

Create Weekly Income and Expense Report

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

Create Weekly Income and Expense Report

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

Create Weekly Income and Expense Report

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

Create an Income and Expense Report

Read More: Create a Report That Displays Quarterly Sales in Excel (with Easy Steps)


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.

Create Monthly Income and Expense Report

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.

Create Weekly Income and Expense Report

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

Create Monthly Income and Expense Report

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

Create Monthly Income and Expense Report

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

Create Monthly Income and Expense Report

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

Create Income and Expense Report

Read More: Create a report that displays the quarterly sales by territory


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, and don’t forget to visit our Exceldemy page.


Related Articles

Durjoy Paul

Durjoy Paul

Hi there! I'm Durjoy. I have completed my graduation from the Bangladesh University of Engineering and Technology. I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo