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

A monthly report is used very commonly to track the sequence of events, data collected, achieved results, etc. It is one of the most essential tools to track progress or decline. This article will show you how to make a monthly report in Excel.

How to Make Monthly Report in Excel (With Easy Steps)

This section demonstrates how you can make a monthly report on a daily or weekly basis in Excel. But first, you need a dataset where data is tracked daily throughout the month. Once you have that, follow these steps to make the monthly report.

Step 1: Import Your Dataset

First of all, you need to import the dataset in Excel if it isn’t in it already. We are using the following dataset for example.

Here, you can see the sales of different products listed daily in May.

Step 2: Create Pivot Tables for the Dataset

The next thing you need to do is make a pivot table out of the dataset.

To do that, select the dataset first by selecting a cell in it and pressing Ctrl+A on your keyboard.

Now go to the Insert tab on your ribbon.
Then select PivotTable from the Tables group and select From Table/Range from the drop-down menu.

As you have already selected the table, you will notice the range is selected in the pop-up box. Now, select New Worksheet for the pivot table. And then click on OK.

After that, go to the new sheet where the pivot table is supposed to be and on the right side of it, select Day and Total Sales (the one we intend to make a report on) from the PivotTable Fields.

As a result, you can see the pivot table emerging with the column headers you have selected.

Step 3: Insert Daily Report Chart

Now is the time to insert charts on the basis you would prefer. This sub-section will focus on making a chart based on the daily intervals.

First, select a cell on the pivot table.
Second, go to the Insert tab from your ribbon.
Now, select Recommended Charts from the Charts group.

Then in the Insert Chart box select Column from the left and select the type of column chart you want. We have selected Stacked Column After that selection, click on OK.

As a result, a column chart will be created.

You can now modify the chart style from the plus icon and brush icon that appears on the right once you have selected the chart.

After selecting style 8 and removing the legend, it will look something like this.

In case, you don’t want a bar plot, you can have your preferred one from the Insert Chart box. Follow the below procedure where a method of plotting line plot is described.

First of all, select a cell in the pivot table.
Then go to the Insert tab from your ribbon and select Recommended Charts from the Charts group just like above.

Now, in the Insert Chart box that popped up, select Line under all charts on the left. And then select the type of line chart you prefer.

After that, click OK. You will have the line chart on your spreadsheet.

You can change chart elements and chart styles from the plus icon and brush icons that appear once you select the chart in your spreadsheet.

After changing the chart styles and removing the legend, it will look something like this.

Step 4: Insert Weekly Report Chart of the Month

If you want a weekly analysis, instead of or after the daily activity report, follow these points to make a report on one.

First of all, you need the pivot charge described in step 2. If you don’t want to overwrite the previous graphs in step 3, make a new pivot table.
Then right-click on one of the cells on the days’ column. From the context menu, select Group option.

After that, in the Grouping box select Days and unselect Months in the By In the Number of Days, select 7.

Next, click on OK.
Now, the pivot table will look something like this, sums labeled on weekly basis.

Next, select one of the cells in the pivot table and go to the Insert Then select Recommended Charts from the Charts group.

Now in the Insert Chart box select Pie chart from the left and select the type of pie chart you prefer.

After that, click on OK.
At this point, you will have a pie chart on your spreadsheet.

You can change the chart elements and styles from the plus icon and the brush icon that appears on the right of the chart once you have selected it.

After changing the color palette, the chart will look something like this.

In case you want a line plot, follow this procedure.

First of all, select a cell on the pivot table.
Then go to the Insert tab and select Recommended Charts from the Charts group just like above.

After that, in the Insert Chart box, select Line at the left, under the All Charts Then select the one you prefer from the right.

Now click on OK.

As a result, you will have your line plot.

After changing the style and removing the legend it will look something like this.

Step 5: Generate Final Report

Once you have all of the reports plotted, daily or weekly, you can copy those to a different spreadsheet to present the monthly report in Excel.

Read More: How to Make Sales Report in Excel

How to Make Report for Consecutive Months in a Year in Excel

If you want a report on the consecutive months in a year in Excel, you can use the same approach to make the graph you prefer to present. Follow these steps for a detailed method on how to make a report on consecutive months in a year in Excel.

Step 1: Import Your Dataset

First, import the dataset in Excel if it isn’t in it already. With this in mind, have a dataset where data is tracked and recorded by months for this process. We are using the following dataset for the demonstration.

Step 2: Create Pivot Table

At this point, you need to convert your dataset into an Excel pivot table.

For that, select the dataset first.

Then go to the Insert tab from the ribbon and select PivotTable from the Tables
After that, from the drop-down menu, select From Table/Range.

Now, select New Worksheet in the next box and click on OK.

As a result, a new worksheet will be created. Now, go to the sheet, and in the PivotTable Fields select Month and Profit.

Consecutively, a pivot table will emerge like this.

Step 3: Insert Chart for Dataset

Now is the time to insert the desired chart from the dataset.

To do that, select a cell in the pivot table first.
Then, go to the Insert tab and select Recommended Charts from the Charts group.

Now, in the Insert Chart box select Column on the left, under the All Charts From the right, select the type of column chart you prefer.

Finally, click on OK.

As a result of the previous steps, a column chart will be created.

Step 4: Generate Final Report

You can change the chart elements and chart styles from the plus icon and the brush icon that appears once you have selected the chart.

Now you can modify the chart to your liking. For example, selecting the style and removing the legend, will look something like this.

Download Practice Workbook

Download the practice workbook used for demonstrating the article with datasets and the charts below.

Make Monthly Report.xlsx

Conclusion

This is how you make a monthly report on both a daily and weekly basis and a report for consecutive months in a year in Excel. Hope you have found this guide helpful and informative. If you have any questions or suggestions let us know below.

<< Go Back to Report in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!